Excelのメニューに自作したマクロを追加するには：VBA／マクロ便利Tips

業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、CommandBarオブジェクトのAddメソッドを使い、作成したマクロをExcelメニューに追加して、呼び出す方法について。

» 2014年05月19日 18時00分公開

※本Tipsの環境：Windows 8.1 Enterprize（64ビット）＋Excel 2013

　今回は、作成したマクロをExcelメニューから呼び出す方法を紹介する。呼び出すマクロはTips「Excel方眼紙をきめ細かい設定で簡単に作り印刷するには」で紹介した「Excel方眼紙作成」マクロを例として活用するが、もちろん他のマクロでも応用は可能だ。

　なお本Tips連載は、ある程度VBA／マクロが使える方を対象にしている。Excelマクロ／VBAの初心者の方は、「Excelマクロ／VBAリファレンス用途別・キーワード別一覧超まとめ」を参照しながら本稿を読んでいただきたい。

Excelのメニューバーを扱う「CommandBar」オブジェクト

　Excelのメニューバーは「Worksheet Menu Bar」という名前のCommandBarオブジェクトである。Addメソッドでコントロールを追加できる。

　書式は下記のようになる

CommandBarオブジェクトのAddメソッドメソッドの書式

Set CommandBarControl=ThisWorkbook.Application.CommandBars(“Worksheet Menu Bar”).Controls.Add(Type:=｛下記表｝)

　引数Typeには下記表の値を指定すると、Excelメニューにマクロが追加される。

表　引数Typeに指定する値
コントロール名	説明
msoControlButton	ボタン
msoControlEdit	テキストボックス
msoControlDropdown	ドロップダウンリストボックス
msoControlComboBox	コンボボックス
msoControlPopup	ポップアップメニュー

　今回は、Typeに「msoControlPopup」を指定している。

「フォームを開く」コードの作成

　Excelメニューにマクロを登録する前に、VBEのメニューから［挿入］を選択して「標準モジュール」を選択する。プロジェクト内にModule1が追加されるので、その中にリスト1のコードを追加する。

Option Explicit
Sub フォームを開く()
  UserForm1.Show vbModeless
End Sub

リスト1　「フォームを開く」コード

　今回は、フォームを「モードレス」で開いている。Showメソッドの後に、vbModelessを追加する。

　通常フォームを表示させる場合は、Showメソッドで表示させる。このShowメソッドを使用すると、フォームが表示されている間は、Excelの操作ができなくなる。そこで、フォームを「モードレス」という表示方法で表示させると、フォームが開いたままの状態で、Excelの操作が可能になる。

　「フォームの表示方法」については、また別のTipsで詳説する予定だ。

Excelメニューの作成

　次に、Excelのメニューに「Excel方眼紙作成」というメニューを追加して、フォームを表示させるコードを記述する。このコードはVBEのプロジェクト内のThisWorkbookの中に記述する必要がある（リスト2）。

Option Explicit
Private Sub Workbook_Open()
  Dim フォームメニュー As CommandBarControl
  Dim メニュー As CommandBar
  Set メニュー = Application.CommandBars("Worksheet Menu Bar")
  For Each フォームメニュー In メニュー.Controls
    フォームメニュー.Delete
  Next
  Set フォームメニュー = ThisWorkbook.Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
  フォームメニュー.Caption = "Excel方眼紙作成"
  フォームメニュー.OnAction = "フォームを開く"
  Set フォームメニュー = Nothing
End Sub

リスト2　マクロをExcelメニューに追加するコード

　コードはWorkbook_Openイベント内に記述する。

　まず、CommandBarControl型の変数「フォームメニュー」を宣言し、CommandBar型の変数「メニュー」を宣言する。

　CommandBarオブジェクトには、CommandBarControlsコレクションがあり、コマンドバーの全てのコントロールが含まれる。コマンドバーのコントロールを参照するには、CommandBarオブジェクトのControlsプロパティを使用する。

　Excelのメニューバーは「Worksheet Menu Bar」という名前のCommandBarオブジェクトであるため、変数「メニュー」に「Worksheet Menu Bar」という名前のCommandBarオブジェクトをセットする。

　CommandBarオブジェクトのControlsプロパティを使用して、CommandBarControlsコレクション内のコマンドを、変数「フォームメニュー」に格納しながら、以下の処理を繰り返す。

　CommandBarControl型の変数「フォームメニュー」の「Delete」メソッドで一度登録されているコマンドを削除する。この処理を行っていないと、Workbookが開かれるたびに、同じコマンドが重複して追加されるので、注意が必要だ。

　Excelのメニューバーは「Worksheet Menu Bar」という名前のCommandBarオブジェクトで表されるので、Addメソッドでフォームメニューに、コントロールを追加して、セットしておく。

　コマンドの「Caption」プロパティに「Excel方眼紙作成」と指定する。これがExcelメニューに表示される名前となる。

　次に「OnAction」プロパティに、リスト1で作成した「フォームを開く」イベントを指定する。これで、「Excel方眼紙作成」メニューをクリックすると、フォームが「モードレス」で開くようになる。

　最後に「Set *** = Nothing」で「フォームメニュー」変数を全ての関連付けから解放しておく。