今回は、指定の標準モジュールにマクロを書いておくとブックの起動時に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エディタのメニューに登録したりといったテクニックは以下の書籍に詳しく掲載されている。

- 作者: 坪崎誠司
- 出版社/メーカー: 株式会社プレスティージ
- 発売日: 2008/11/17
- メディア: 単行本(ソフトカバー)
- クリック: 11回
- この商品を含むブログを見る
サブメニューや右クリックメニューに任意のマクロを登録する方法も掲載されているのでVBAでVBEを自在に操作できるようになりたい方にはおススメの一冊。