2009-07-31

バッチ・マクロのTOPへ

通常，Excel VBAのコードは， .xls ファイル（＝ブック）の内部に
保存されてしまう。

これだと

複数のブックで共通のコードを利用できないし，

マクロの修正があると，全ブックを修正しなければならないし，

ソースコードをテキスト形式で管理できない（SVNやGitで差分を読みづらい）

という不便さがある。

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

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

テキスト形式で，VBAのモジュールをブックの外部に保管して共有し，

どのモジュールをブックに読み込ませるか？という情報（モジュールの依存関係）も外部の設定ファイルで管理し，

ブックを開いたら自動的に必要なモジュールが外部から読み込まれる

というふうにする。

ここでは

（１）簡易な方法（非テキスト形式）

（２）便利な方法（テキスト形式）

の２つの方法を掲載するので，お好みで好きなほうを選んでください。

（１）原始的な方法：自動的に他ブックを開く

（２）より良い方法：モジュールをテキストファイルからロードする方法

補足

（１）原始的な方法：自動的に他ブックを開く

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

まず，共通コードを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を呼び出せる。

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

しかし欠点として，

開いたら閉じなければならない（開くだけで済ませられない）

開いてから閉じるまでに間がある（利用中はずっと開いていなければならない）

呼び出し方が冗長（あくまで，外部にあるものを外部にあるまま呼び出しているに過ぎない。自身のオブジェクトとして取り込んではいない。includeではない。）

コードはExcelの内部で管理されてしまう（コードをテキスト形式で管理できない）

などがある。

かわりに，もっと良い方法がある。　以下で紹介しよう。

↑目次へ戻る

（２）より良い方法：モジュールをテキストファイルからロードする方法

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

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

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

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

下記のように。

libdef.txt

.\HogeModule.bas
.\FugaModule.bas

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

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

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

(Excel2007の場合)
リボンの「開発」タブ＞マクロのセキュリティ＞VBAプロジェクトオブジェクトモデルへのアクセスを信頼する
http://support.microsoft.com/kb/28283...
※「プログラミングによる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)
        ' １行ずつ
        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

コード概説：

ワークブックを開くときのイベント http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_130_04.html

標準モジュールのインポートをVBAで自動化する方法 http://www.seiji-tsubosaki.net/ExcelTech/ExcelProfessionalEngineerTechnic/Contents_08.htm

標準モジュールを削除する方法 http://oshiete1.goo.ne.jp/qa1799764.html

これで完了だ。

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