Excelのメニューに自作したマクロを追加するには：VBA／マクロ便利Tips
業務効率化に役立つVBA／マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、CommandBarオブジェクトのAddメソッドを使い、作成したマクロをExcelメニューに追加して、呼び出す方法について。
今回は、作成したマクロを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メニューにマクロが追加される。
| コントロール名 | 説明 |
|---|---|
| msoControlButton | ボタン |
| msoControlEdit | テキストボックス |
| msoControlDropdown | ドロップダウンリストボックス |
| msoControlComboBox | コンボボックス |
| msoControlPopup | ポップアップメニュー |
今回は、Typeに「msoControlPopup」を指定している。
「フォームを開く」コードの作成
Excelメニューにマクロを登録する前に、VBEのメニューから［挿入］を選択して「標準モジュール」を選択する。プロジェクト内にModule1が追加されるので、その中にリスト1のコードを追加する。
Option Explicit Sub フォームを開く() UserForm1.Show vbModeless End Sub
今回は、フォームを「モードレス」で開いている。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
コードは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」で「フォームメニュー」変数を全ての関連付けから解放しておく。
実行結果
最初は、作成したExcelを閉じ、マクロ形式で保存したExcelファイルを開き直す。すると、Excelのメニューの「アドイン」の中に「Excel方眼紙作成」メニューが表示される。これをクリックすると「Excel方眼紙作成フォーム」が開く。
次回は、オートフィルに関するTips
今回は、フォームの起動をExcelのメニューから行った。今回は1階層のみのメニューだが、複数の階層にわたってメニューを追加することも可能だ。この方法については、また別な機会に解説しよう。
次回は「セル」のオートフィルに関するTipsを紹介する予定だ。お楽しみに。
著者プロフィール
薬師寺 国安（やくしじ くにやす） ／ 薬師寺国安事務所
薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。
1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。
1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。
2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。
Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。
Microsoft MVP for Development Platforms - Client App Dev（Oct 2003-Sep 2012）。
Microsoft MVP for Development Platforms - Windows Phone Development（Oct 2012-Sep 2013）。
Microsoft MVP for Development Platforms - Client Development（Oct 2013-Sep 2014）。
Copyright © ITmedia, Inc. All Rights Reserved.