VBA 標準モジュールのマクロを読み取って起動時にVBEのメニューに自動登録するアドインを自作する

今回は、指定の標準モジュールにマクロを書いておくとブックの起動時にVBエディタのメニューに自動登録してくれるアドインを作る。
VBエディタのメニューから実行するマクロなので、主に開発支援のためのコードを登録すると便利。
アドイン化することで常にVBエディタ上で実行できるようになる。

目次

マクロの動作解説

1) 「MenuMacros」という名称で標準モジュールを作り、任意のコードを記述する。

※たとえば以下のコードを挿入する。

Sub hoge() 'H
    MsgBox "hoge"
End Sub
Sub fuga() 'F
    MsgBox "fuga"
End Sub
Sub piyo() 'P
    MsgBox "piyo"
End Sub

2) メニュー登録用のマクロは「MenuMacros」モジュールの内容を読み取り、自動的にVBエディタに以下のようなコードを登録する。

このときプロシージャ名の隣に英字1文字のコメントをつけると、メニューのショートカット文字列になる。

3) メニューからクリックしたマクロが実行される。

活用できる場面

今回はExcelのメニューではなくVBエディタ側のメニューへの登録なので、主に開発・コーディング支援のマクロを登録すると便利かと思う。

たとえばこういうのとか、
thom.hateblo.jp
こういうのとか、
thom.hateblo.jp
こういうのとか。
thom.hateblo.jp

作り方

準備

参照設定

まず新規ブックを作成し、以下の2つの参照設定を済ませておく。

• Microsoft Visual Basic for Applications Extensibility 5.3
• Microsoft Scripting Runtime

セキュリティ設定

また、以下の手順でセキュリティ設定を変更しておく。
ファイル
→オプション
→セキュリティセンター
→セキュリティセンターの設定
→マクロの設定
→VBAプロジェクトオブジェクトモデルへのアクセスを信頼するにチェック

ただしこの設定はマクロウイルスに感染しやすくなるリスクもあるので自己責任で。
いまどきマクロウイルスもないと思うけれど、作ろうと思ったら作れるものなので最低限の警戒は必要。
基本的にネットに落ちてる信頼性の低いファイルをマクロ有効で開いたりしなければ大丈夫なはず。

コーディング

クラスモジュール

クラスモジュールを挿入し、プロパティウインドウからオブジェクト名を「Instruction」に変更する。
コードは以下の2行のみ。

Public name As String
Public shortcut As String

このクラスは、標準モジュール「MenuMacros」から読み取ったプロシージャとそのショートカット文字を保管するための入れ物である。

次にもうひとつクラスモジュールを挿入し、オブジェクト名を「EventHandler」とする。
コードは次のとおり。

Public WithEvents MenuEvent As VBIDE.CommandBarEvents

Public Property Get Self() As Object
    Set Self = Me
End Property

Private Sub MenuEvent_Click(ByVal CommandBarControl As Object, handled As Boolean, CancelDefault As Boolean)
    Application.Run CommandBarControl.OnAction
    handled = True
    CancelDefault = True
End Sub

このクラスは、VBエディタのメニュー項目がクリックされたイベントを検知して実際に該当のコードを実行する役割を持つ。

次にもうひとつクラスモジュールを挿入し、オブジェクト名を「MenuCreator」とする。
コードは以下のとおり。

Private MenuTag As String
Private RootMenu As CommandBarPopup
Private EventHandlers As Collection
Private MenuMacroComponentFullName As String

Public Sub Init(tag As String, rootCaption As String, vbc As VBComponent)
    MenuTag = tag
    Call Me.RemoveMenu
    Set EventHandlers = New Collection
    Dim VBEMenuBar As CommandBar: Set VBEMenuBar _
        = Application.VBE.CommandBars(1)
    
    With New FileSystemObject
        MenuMacroComponentFullName _
            = "'" & .GetFileName(vbc.Collection.Parent.Filename) & "'!" & vbc.name
    End With
    
    Set RootMenu = VBEMenuBar.Controls.Add(Type:=msoControlPopup)
    RootMenu.Caption = rootCaption
    RootMenu.tag = MenuTag
End Sub

Public Sub AddSubMenu(procname As String, shortcut As String)
    Dim SubMenu As CommandBarControl: Set SubMenu _
        = RootMenu.Controls.Add
    
    With SubMenu
        .Caption = procname & "(&" & shortcut & ")"
        .BeginGroup = False
        .OnAction = MenuMacroComponentFullName & "." & procname
    End With
    
    With New EventHandler
        Set .MenuEvent = Application.VBE.Events.CommandBarEvents(SubMenu)
        EventHandlers.Add .Self
    End With
End Sub

Public Sub RemoveMenu()
    'RootMenu.Deleteとする代わりに、わざわざMenuTagで検索して消すのは、
    '前回の異常終了で残ってしまったメニューも片づけるため。
    Dim MyMenu As CommandBarControl: Set MyMenu _
        = Application.VBE.CommandBars.FindControl(tag:=MenuTag)
    Do Until MyMenu Is Nothing
        MyMenu.Delete
        Set MyMenu = Application.VBE.CommandBars.FindControl(tag:=MenuTag)
    Loop
    Set EventHandlers = Nothing
End Sub

これはメインコードから呼び出され、VBエディタに実際にメニューを構築する役割を担うクラス。
最初にInitプロシージャを呼び出した時点でルートメニューを作成してしまい、以降はメインコードからAddSubMenuを呼び出すたびに一つサブメニューを追加する形でメニューを作っていく。
また、MenuCreatorはメニューを作るだけではなくイベントハンドラを保持するためのEventHandlersコレクションを持っている。

標準モジュール

次に、標準モジュールを挿入し、オブジェクト名を「Main」とする。
コードはつぎのとおり。

Private Menu As MenuCreator
Public Sub Auto_Open()
    Dim vbc As VBComponent: Set vbc _
        = ThisWorkbook.VBProject.VBComponents("MenuMacros")
    
    Set Menu = New MenuCreator
    Menu.Init "MyTools", "MyTools(&M)", vbc
    
    Dim arr() As Instruction: arr = GetInstructions(vbc.CodeModule)
    
    Dim i As Long
    For i = 0 To UBound(arr)
        Menu.AddSubMenu arr(i).name, arr(i).shortcut
    Next
End Sub

Public Sub Auto_Close()
    On Error Resume Next
    Menu.RemoveMenu
    On Error GoTo 0
    Set Menu = Nothing
End Sub

Private Function GetInstructions(cmod As CodeModule) As Instruction()
    Dim psl As Long, pbl As String
    Dim ret() As Instruction: ReDim ret(0)
    Dim i As Long
    For i = 1 To cmod.CountOfLines
        Dim pname As String
        pname = cmod.ProcOfLine(i, vbext_pk_Proc)
        If pname <> "" Then
            psl = cmod.ProcBodyLine(pname, vbext_pk_Proc)
            If i = psl Then
                pbl = cmod.Lines(psl, 1)
                Set ret(UBound(ret)) = New Instruction
                
                On Error Resume Next
                    ret(UBound(ret)).shortcut = Split(pbl, "'")(1)
                On Error GoTo 0
                
                ret(UBound(ret)).name = pname
                ReDim Preserve ret(UBound(ret) + 1)
            End If
        End If
    Next
    ReDim Preserve ret(UBound(ret) - 1)
    GetInstructions = ret
End Function

プロシージャ名をAuto_Openとしておくことで、ファイルが開かれると自動的に実行されるマクロになる。
そしてAuto_Openのコード内では、MenuMacrosモジュールからマクロを読み取るための関数「GetInstructions」が呼び出されている。※GetInstructionsもMain内に作成したコードである。

GetInstructionsを呼び出すとInstruction型の配列が返るので、MenuCreatorにそれぞれマクロ名とショートカット文字列を渡してメニューを作っている。

最後にMenuMacrosという名前で標準モジュールを作成し、以下のコードを挿入する。

Sub hoge() 'H
    MsgBox "hoge"
End Sub
Sub fuga() 'F
    MsgBox "fuga"
End Sub
Sub piyo() 'P
    MsgBox "piyo"
End Sub

あとは、Excelブックを保存して開きなおすか、Auto_Openマクロを手動実行すれば冒頭で紹介したようにMenuMacrosに書かれたマクロがひととおりメニューに登録される。

アドイン化

ブックをアドイン化するのはすごく簡単で、単にファイルを名前を付けて保存するときに種類をExcel アドインとすればアドイン保存用のフォルダに移動するので、そのまま保存すればよい。

Windows 7の場合、場所はここ↓
C:\Users\[ユーザー名]\AppData\Roaming\Microsoft\AddIns

ファイル→オプション→アドイン→設定と進み、今保存したアドイン名にチェックを入れるとアドインとして開かれる。

アドイン化すると他のブックの編集中もプロジェクトエクスプローラーに表示される。

Excelの場合、アドイン化した後もコードの編集ができる。編集して保存する際はプロジェクトエクスプローラー上でアドインのモジュールなどをどれか選択したうえでCtrl+Sで保存する。

※VBEでの保存は、現在選択されているモジュールのあるブックに対して保存されるので注意。

なお、Word VBAの場合はアドインとして読み込み中のドキュメントのコードは編集できず、別途開きなおす必要がある。また、PowerPointはアドイン化するとそれ以上編集できなくなるので、アドイン化前のpptmファイルを保管しておく必要がある。

実際に活用してみる

先ほど紹介したこちらのマクロを実際にMenuMacrosモジュールに組み込んでみる。
thom.hateblo.jp

以下をMenuMacrosモジュールに追記。

Sub 非アクティブなコードペインを閉じる() 'C
    Dim C As VBIDE.CodePane
    With ThisWorkbook.VBProject.VBE
        For Each C In .CodePanes
            If Not .ActiveCodePane Is C Then C.Window.Close
        Next
    End With
End Sub

そしてメニューを更新するため、MainモジュールのAuto_Openマクロを手動で実行する。

すると以下のようにメニューに登録される。

VBエディタ使用中にこんな風にごちゃごちゃしてきたら、

Alt+M、Cと入力すればアクティブなコードを残して他をすべて閉じることができる。

いちいちマクロを追加するたびにAuto_Openを実行するのが面倒ならそれもメニューに登録してしまえばよい。

MenuMacrosモジュールに以下を追記し、一度だけ手動で実行しておく。

Sub メニュー更新() 'U
    Main.Auto_Open
End Sub

次回からAlt+M、Uと入力することでメニューが更新される。
ツールの改修中は何度かメニューをいじると思うので便利。

注意事項

• このマクロのあるプロジェクトで実行時エラーを発生させてしまうとメニュー操作が利かなくなる。その場合はAuto_Openマクロを再実行すると復活する。他のプロジェクトでのエラーは問題ない。

• Privateプロシージャでもメニューに登録される。また、他のプロジェクト編集中であってもメニューからはPrivateプロシージャが実行できるので、この実装を逆手にとってExcel側から見せたくない場合はPrivateにしておいてもよい。

• Functionもメニューに登録されてしまうが引数を渡すことができず、戻り値も使うことができないのであまり意味はない。他から呼び出す用のFunctionを作りたい場合は別のモジュールに作ると良い。

• Propertyプロシージャを書くとエラーが発生する。

今後やりたいこと

以下の、AutoIndenterはクリップボードから読み込んでイミディエイトウインドウに出力するけど、せっかくメニュー実行できるのだから選択中のプロシージャを直接インデントできるように改良したい。
thom.hateblo.jp

一応、機能的にはできることが分かったけれど、いろいろ悪戦苦闘中。

参考書籍

VBエディタのメニューに登録したりといったテクニックは以下の書籍に詳しく掲載されている。

Excelプロフェッショナルエンジニアテクニック

サブメニューや右クリックメニューに任意のマクロを登録する方法も掲載されているのでVBAでVBEを自在に操作できるようになりたい方にはおススメの一冊。