Excelマクロを外部ファイルにして複数ブックから利用できるようにする

※2021/10/4 更新

Excelマクロは便利なのですが、ブックファイルの中にプログラムを仕込むので、基本使うマクロはそのファイル内に記述します。ここで問題になるのが、プログラムの最新管理。同じ内容のプログラムでもファイルごとに保存しているので、プログラムを修正する場合にすべてのファイルに対して修正しなければなりません。この工数がマクロの最大の欠点となります。

会社で、同じマクロを入れたファイルがコピーにコピーを重ねて増殖し、今度はそれを修正することになって、えらい苦労したことがあります。そうならないためにも、サクッと最新プログラムに更新できるよう、あらかじめ仕掛けておく必要があります。

ソースコード

そのプログラムがこちらです。但し、これだけでは動きません。後述する設定が必要なので最後までお読みください。

Sub replaceModule()
   ModuleNames = "Module1, Module2"
   PathName = "C:\Users\user1\Documents"
   Dim FileNames As Variant
   FileNames = Split(ModuleNames, ",")
   For Each cmp In ThisWorkbook.VBProject.VBComponents
      If InStr(ModuleNames, cmp.Name) > 0 Then
         ThisWorkbook.VBProject.VBComponents.Remove cmp
      End If
   Next cmp
   For i = 0 To UBound(FileNames)
      file_path = PathName & "\" & Trim(FileNames(i)) & ".bas"
      If Dir(file_path) = "" Then
         MsgBox (file_path & " は存在しません。スキップします。")
      Else
         ThisWorkbook.VBProject.VBComponents.Import file_path
      End If
   Next i
End Sub
 

 

最初の2行で、

• ModuleNames ：更新するモジュール名。カンマ区切りで指定
• PathName ：最新のプログラムが置かれる共有フォルダのパス

を指定します。

あとは、指定した共有フォルダに、Module1.bas, Module2.bas というマクロファイルを置いてください。

 

参照設定

次に参照設定を行います。Visual Basic Editorのメニューから「ツール」－「参照設定」を選びます。

 

以下のようなダイアログが現れるので、リストから「Microsft Visual Basic for Application Extensibility」にチェックを入れて「OK」ボタンを押します。

リボンに「開発」タブを追加する

次にマクロのセキュリティ設定をするのですが、その前にリボンに「開発」タブを表示させる必要があります。もしすでに表示させているのであれば読み飛ばしてください。

 

リボンの適当な場所で右クリックしてプルダウンメニューから「リボンのユーザー設定」をクリックします。

 

以下のダイアログが現れるので赤枠の「開発」にチェックを入れてOKボタンを押します。これでリボンに開発タブが現れたはずｄす。

マクロのセキュリティ設定

「開発」タブの「マクロのセキュリティ」をクリックします。

以下のダイアログが現れるので、「VBAプロジェクト オブジェクト モデルのアクセスを信頼する」にチェックを入れて「OK」ボタンを押します。これでセキュリティ設定は完了です。

実行

それでは先ほど示したマクロを実行してみましょう。

下記の通り指定フォルダ内のモジュールをインポートできました。

 

管理者はモジュールファイルを最新にした後、各ユーザー側で、replaceModule()を実行してもらうことで、最新ファイルにリプレイスされるわけです。

 

 - VBA