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

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

[薬師寺国安，PROJECT KySS]

連載目次

※本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」で「フォームメニュー」変数を全ての関連付けから解放しておく。

実行結果

　最初は、作成したExcelを閉じ、マクロ形式で保存したExcelファイルを開き直す。すると、Excelのメニューの「アドイン」の中に「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）。