連載
» 2014年05月19日 18時00分 UPDATE

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

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

[薬師寺国安,PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本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方眼紙作成フォーム」が開く。

vbatips0002_01.jpg 図 Excelメニューの「アドイン」から「Excel方眼紙作成」メニューが表示され、クリックすると「Excel方眼紙作成フォーム」が表示された

次回は、オートフィルに関するTips

 今回は、フォームの起動をExcelのメニューから行った。今回は1階層のみのメニューだが、複数の階層にわたってメニューを追加することも可能だ。この方法については、また別な機会に解説しよう。

 次回は「セル」のオートフィルに関するTipsを紹介する予定だ。お楽しみに。

著者プロフィール

kuniyasu.jpg

薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所

薬師寺国安事務所代表。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© 2017 ITmedia, Inc. All Rights Reserved.

@IT Special

- PR -

TechTargetジャパン

この記事に関連するホワイトペーパー

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。