主に言語とシステム開発に関して RSSフィード



2009-07-31

Excel VBAのマクロを,複数のブックから利用する方法 (標準モジュールをブックの外部で管理して,共通ライブラリとして読み込み)

| Excel VBAのマクロを,複数のブックから利用する方法 (標準モジュールをブックの外部で管理して,共通ライブラリとして読み込み)のブックマークコメント



通常,Excel VBAのコードは .xls ファイル(=ブック)の内部に保存されてしまう。

これだと

  • 複数のブックで共通のコードを利用できないし,
  • マクロの修正があると,全ブックを修正しなければならないし,

という不便さがある。

これでは,VBAを使ってスムーズな開発プロジェクトを行なうのは難しい。



下記は,それを解決する方法。


  • テキスト形式で,VBAのモジュールをブックの外部に保管して共有し,
  • どのモジュールをブックに読み込ませるか?という情報(モジュールの依存関係)も外部の設定ファイルで管理し,
  • ブックを開いたら自動的に必要なモジュールが外部から読み込まれる

というふうにする。






ここでは

  • (1)簡易な方法(非テキスト形式)
  • (2)便利な方法(テキスト形式)

の2つの方法を掲載する。




(1)原始的な方法:自動的に他ブックを開く

コードの存在するブックを開いて,そのブックのマクロを呼び出して,終わったら閉じる。


まず,共通コードをsrc.xlsに書く。

ふつうにVisual Basic Editor上でマクロを書く必要がある。


src.xls の標準モジュールとして「HogeModule」内に:


Sub fuga()
    MsgBox "fuga"
End Sub

Function hello(x)
    hello = "Hello, " & x & "!"
End Function

※VBEの画面左側に「標準モジュール」が無い場合は,右クリック>挿入 で作っておけばよい。



そして,それを呼び出す側のブック。


caller.xls のSheet1


Sub call_fuga()
    Workbooks.Open Filename:="D:\temp\src.xls", ReadOnly:=True
    Application.Run "'src.xls'!HogeModule.fuga"
    Windows("src.xls").Close
End Sub

Sub call_hello()
    Workbooks.Open Filename:="D:\temp\src.xls", ReadOnly:=True
    s = Application.Run("'src.xls'!HogeModule.hello", "World")
    MsgBox s
    Windows("src.xls").Close
End Sub

' 注:ブック名はシングルクオートで囲っている。
' ブック名に丸括弧等の特殊文字が含まれる時のために必要。



これでsrc.xlsに書いてあるマクロのロジックを,外部ファイルcaller.xlsから呼び出せる。


caller.xlsを開くと,Alt + F8でcall_fugaとかcall_helloを呼び出せる。

呼び出し側のブックの数がいくら増えたとしても,マクロの変更箇所は1つで済む。



しかし欠点として,

  • 開いたら閉じなければならない(開くだけで済ませられない)
  • 開いてから閉じるまでに間がある(利用中はずっと開いていなければならない)
  • 呼び出し方が冗長(あくまで,外部にあるものを外部にあるまま呼び出しているに過ぎない。自身のオブジェクトとして取り込んではいない。includeではない。)
  • コードはExcelの内部で管理されてしまう(コードをテキスト形式で管理できない)

などがある。


かわりに,もっと良い方法がある。



(2)より良い方法:モジュールをテキストファイルからロードする


呼び出したいモジュールとして,下記のような2つがあるとしよう。

まずは,これらをそれぞれ自由にエディタ(メモ帳とか)で書いて,ファイルとして保存。


HogeModule.bas


' hogeのためのモジュール。
Attribute VB_Name = "HogeModule"


Sub hoge()
    MsgBox "hoge"
End Sub

Function hello(x)
    hello = "Hello, " & x & "!"
End Function



FugaModule.bas


' fugaのためのモジュール。
Attribute VB_Name = "FugaModule"


Sub fuga()
    MsgBox "fuga"
End Sub


そして,これらの2つを呼び出したいという旨,設定ファイルに記述する。

下記のように。


libdef.txt


.\HogeModule.bas
.\FugaModule.bas




次に,呼び出したい側のエクセルファイルに,モジュールを自動的に読み込むという設定を書く。


事前に下記の設定を行なっておくこと。

(Excel2003の場合)

ツール>マクロ>セキュリティ>信頼できる発行元>Visual Basic プロジェクトへのアクセスを信頼する をオン


(Excel2007の場合)

リボンの「開発」タブ>マクロのセキュリティ>VBAプロジェクトオブジェクトモデルへのアクセスを信頼する

http://support.microsoft.com/kb/282830/ja

※「プログラミングによるVisual Basicプロジェクトへのアクセスは信頼性に欠けます」というエラーメッセージが出ないようになる。


ここでは,呼び出し側のファイルをcaller.xlsとする。

「ブックを開いたときに自動的に〜〜」という処理は ThisWorkbook の中に書く。


下記の内容をコピペ。


caller.xls のThisWorkbook



' ワークブックを開く時のイベント
Private Sub Workbook_Open()
    
    ' txtに書いてある外部ライブラリを読み込み
    load_from_conf ".\libdef.txt"
       
End Sub



' -------------------- モジュール読み込みに関する関数 --------------------



' 設定ファイルに書いてある外部ライブラリを読み込みます。
Sub load_from_conf(conf_path)
    
    ' 全モジュールを削除
    clear_modules
    
    ' 絶対パスに変換
    conf_path = abs_path(conf_path)
    If Dir(conf_path) = "" Then
        MsgBox "外部ライブラリ定義" & conf_path & "が存在しません。"
        Exit Sub
    End If
    
    ' 読み取り
    fp = FreeFile
    Open conf_path For Input As #fp
    Do Until EOF(fp)
        ' 1行ずつ
        Line Input #fp, temp_str
        If Len(temp_str) > 0 Then
            module_path = abs_path(temp_str)
            If Dir(module_path) = "" Then
                ' エラー
                MsgBox "モジュール" & module_path & "は存在しません。"
                Exit Do
            Else
                ' モジュールとして取り込み
                include module_path
            End If
        End If
    Loop
    Close #fp

    ThisWorkbook.Save
    
End Sub


' あるモジュールを外部から読み込みます。
' パスが.で始まる場合は,相対パスと解釈されます。
Sub include(file_path)
    ' 絶対パスに変換
    file_path = abs_path(file_path)
    
    ' 標準モジュールとして登録
    ThisWorkbook.VBProject.VBComponents.Import file_path
End Sub


' 全モジュールを初期化します。
Private Sub clear_modules()
    For Each component In ThisWorkbook.VBProject.VBComponents
        If component.Type = 1 Then
            ' この標準モジュールを削除
            ThisWorkbook.VBProject.VBComponents.Remove component
        End If
    Next component
End Sub


' ファイルパスを絶対パスに変換します。
Function abs_path(file_path)

    ' 絶対パスに変換
    If Left(file_path, 1) = "." Then
        file_path = ThisWorkbook.Path & Mid(file_path, 2, Len(file_path) - 1)
    End If
    
    abs_path = file_path

End Function



コード概説:



これで完了。

caller.xlsを開くと,HogeModule.hoge() も FugaModule.fuga() も実行可能な状態になっている。





こうすることのメリットはたくさんある。


  • 複数のブックで,常に共通のマクロを利用できる。マクロに変更があっても,たった1箇所の修正ですむ。
  • 外部の設定ファイルを書き換えるだけで,全モジュールの依存関係を制御できる(一種のDI*1)。
  • モジュールの内容がテキスト形式で管理されるので,Subversionなどのバージョン管理ツールで差分が出しやすい

もちろん,外部にテキスト形式として保存されてはいても,VBEを利用できる。

ブックを開いた際に自動的に読み込まれたマクロをVBE上で閲覧+編集したら,編集結果をbasファイルとして再度エクスポートすればよいのだ。



また,ブックごとに利用モジュールを変えたい場合は,ブックごとに,読み込む設定ファイル( libdef.txt )の名前を変えればよい。



補足

(2)の方法だと,caller.xls内でマクロを編集しても,それだけだと次にブックを開きなおした時に全部クリアされてしまう。

あくまでコードは外部に存在するので,その点を注意。


また,外部のコードが更新されても,ブックを開きなおすまではコードの更新が反映されない。(リアルタイムでない)

それを煩わしく感じる場合もあるかもしれない。

その場合は,全モジュールをその場でリロードするために,マクロで load_from_conf ".\libdef.txt" を実行すればよいだろう。



関連する記事:

Excelのマクロを外部に吸い出して保存するバッチ (WSH/JScriptで,VBAのソースコードだけを抜き取る) - 主に言語とシステム開発に関して

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


パスワード付きのExcelの保護を,バッチで自動的に解除しよう (VBAマクロが見れないブックに対して,パスワードを自動入力) - 主に言語とシステム開発に関して

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


ドキュメント作成を楽にするための,Excel VBA 頻出8パターン - 主に言語とシステム開発に関して

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


画面のスクリーンショットを,Excelブック内に自動的に保存するバッチ - 主に言語とシステム開発に関して

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


JScript/WSH で,Excelファイルを読み書きしよう - 主に言語とシステム開発に関して

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


バッチで,Word文書の内容を読み取ろう (WSH/JScriptでWordファイルを操作する方法) - 主に言語とシステム開発に関して

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


Excelブックの「シート目次」を,自動的に作成するバッチ (WSH/JScriptで,各シートへのリンク付きの目次を自動生成) - 主に言語とシステム開発に関して

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

 

*1DI・・・Dependency Injection, 依存性注入。ただし,ここではC言語のヘッダぐらいの意味でしかない。

リンク元