[VBA] プロシージャ(関数)や変数を外部ファイル管理にして、共通モジュール化したい

Webディレクターとしての、VBA活用記1

()

なぜかここ1年、仕事でVBAを使う機会がやたらと多くなり、知見も少し溜まってきたので、備忘録としてメモ。

サイト制作なんかの場合、ヘッダーやフッターなんかはいろんなページで共通だったりするので、PHPなりJSなりで共通インクルードさせたりしますね。

そうすることで、1つのファイルを修正すれば、多くのページに修正内容が反映されて、メンテナンス性が上がるという次第。

それを同じように、多くのExcelファイルで関数や変数を使い回したいときのやり方をメモっときます。(Windows版Excel2007を使用した場合です)

Excel vba外部ファイル化

いろいろネットで調べたところ、どうやら以下の2つの方法がよさそうです。

 

1. モジュールをテキストファイルからロードする

出来るならこのやり方が一番よいです。

やり方は以下に詳細が書いてあります。(2)の方です。

http://d.hatena.ne.jp/language_and_engineering/20090731/p1

ただし、このやり方で1点困ったことがあり、エクセル側のセキュリティ設定を変更しないといけません。

クライアントに使ってもらう場合、セキュリティ設定を変えることで、何か思わぬ事態が起きてしまった場合、責任問題になってくるのであまり推奨できないですね。

 

2. Application Runの実行

こちらの方を私は使用しています。上記ページの(1)の方ですね。

http://officetanaka.net/excel/vba/tips/tips09.htm

Application Runメソッドを使えば、他ブックの関数や変数などを呼び出すことが出来ます。

マクロを実行したい方のファイル(他ブックのマクロを参照するファイル)のファイル名などを関数に渡したいときは、引数を設定できます。

Application.Run("test.xls!プロシージャ名", 引数1, 引数2, ・・・)

この引数1に、ThisWorkbook.Nameなどを設定したり。

で、共有サーバーなどの¥¥から始まる環境で使用するときは、このままだと動かないことがありました。

その際は、

Debug.Print Application.Run Application.Run("¥¥192.168.0.1¥test.xls!Macro1", 引数1, 引数2, ・・・)

と頭にDebug.Printを付けるとなぜかうまくいきました。

注意したいのは、外部ファイルの関数を使用するにあたり、読み込む外部ファイルが開きっぱなしになるので、一連の処理が済んだら、Closeメソッドでファイルを閉じる処理を入れなくてはなりません。

あと、不思議なことに、外部ファイルの関数を実行しているはずが、関数で使用している定数やグローバル変数まで読み込んでくれます。

ある意味便利ですが、ファイルごとに違う定数などを用意していた場合、どんなんだろうなという感じ。

 

もっと気軽にモジュール化出来るといいんですけどね。

 

戦意

© 2012 戦意