MENU
トップ > エクセルVBA > エクセルVBAワークショップ（２）メニュー作成ツールAutoMenuを作る

エクセルVBAワークショップ（２）メニュー作成ツールAutoMenuを作る

エクセルVBA

前回アドインにはメニューが不可欠だというお話をしました。アドイン内のプロシージャを呼ぶためのトリガーとして、メニューがなければならないという話でした。

エクセルのメニューについてはExcel2007からリボンUIが導入されたので、それまで使われていたメニューバーはすっかり影をひそめてしまいました。メニューバーが使えなくなったのかというとそういうことはなく、現在のExcel2019でもまったく支障なく使うことができます。リボンUIのようなデザイン性はありませんが、ツリー表示ができるのでプログラム内の構造を直観的に表現することに優れています。

メニューバーを追加すると［アドイン］という名前のリボンができます。このリボンの中に追加したメニューツリーが表示されます。


ワークショップの内容

メニューバーの基本については他の多くのサイトにサンプルが載っています。一般的にメニューはロジックを持ちません。どのサイトでも「メニューツリーの作り方」「クリック時に呼び出すプロシージャの登録方法」が説明の中心です。簡単なメニューであれば十数行で実現することができるでしょう。しかし複雑なメニューではどうでしょうか？プログラム行数はメニュー数に比例して増えてきます。工夫してもロジックがないので行数はそれほど少なくはなりません。しかも単純作業です。メニュー数が増えてもメニュー作りが苦にならないような方法はないものでしょうか？

ということで簡単にメニューを作成するツール(AutoMenu)を作ってみました。VBEでAutoMenuを参照登録すればコードを書かなくてもメニューを作ることができます。作りたいメニューの指図書をワークシートに作成しAutoMenuに渡すだけです。今回のワークショップではAutoMenuを実際に作ってみましょう。

１．AutoMenuの概要

AutoMenuを実行するとワークシートのメニュー指図書に従ってメニューが作成されます（AutoMenuはAuto_Openプロシージャで実行します）。作成されたメニューをクリックすると関連付けられたプロシージャが呼ばれます。

２．メニュー指図書

メニュー指図書は作成するアドインのワークシートに作成します。メニューのツリー構造、クリック時に実行するプロシージャ、クリックのショートカットキーを登録します。

  • A列（NodeMenu）親メニュー
  • B列（LeafMenu）子メニュー
  • C列（onAction）メニュークリックで呼び出すプロシージャ
  • D列（ShortCutKey）メニュークリックのショートカットキー

A列とB列でメニューの親子関係を表現します。子メニューがない場合は親メニューのみで子メニューは空にします。C列はメニューをクリックしたときのプロシージャです。イベントは子メニューに（子メニューがない場合は親メニューに）設定されます。D列はショートカットキーです（記法は"^"と"{}"を知っておけば十分でしょう。"q"はCtrl+q、"{F10}"はF10の意味です）。またセルの上下罫線でメニューの区切り線を判断します。図の子メニューの上下罫線を削除すればメニューの区切りがなくなります。

３．AutoMenuを実行するまで

ここからは完成したAutoMenuがアドインフォルダ(C:\Users\<ユーザー>\AppData\Roaming\Microsoft\AddIns)に保存されているものとして話を進めます。AutoMenuの作成についてはそのあとで説明します。
テスト用に新しいプロジェクトを作成してください。プロジェクト名は説明の都合上"VBAワークショップ"とします。

（１）ワークシートにメニュー指図書をつくる

アドインも通常のブックですからワークシートを持っているのですが、通常は非表示になっていて操作することはできません。ワークシートを表示させるにはVBEでThisWorkBookのisAddinプロパティをFalseにします。するとエクセルのウィンドウにワークシートが表示され、通常の操作が可能となります。

このワークシートに前項（２）のメニュー指図書を作っていきます。

NodeMenu LeafMenu onAction ShortCutKey
親メニュー 子メニュー1 Module1.Sub1 ^q
子メニュー2 Module1.Sub2 {F10}
子メニュー3 Module1.Sub3

メニュー指図書が完成したら、isAddinプロパティをTrueに戻しておきます。

（２）AutoMenuを参照設定する

VBAで他のアドインのプロシージャや関数を利用するには参照設定が必要になります。VBEの［ツール］→［参照設定］で表示されるダイアログで［参照］をクリックします。



ファイルの場所にアドインフォルダ(C:\Users\<ユーザー>\AppData\Roaming\Microsoft\AddIns)、ファイルの種類に「Microsoft Excel Files (*.xlsm,･･･)」を指定すると、ペインにAutoMenuが表示されるので選択して［開く］をクリックします。戻ったダイアログのリストにAutoMenuが表示されるのでチェックを確認して［ＯＫ］をクリックしてください。

（３）Auto_OpenプロシージャにAutoMenuを記述する

プロジェクトにモジュールを追加します。モジュール名は"Module1"のままでかまいません。"Module1"に以下のコードを記述します。

Const ProjectName = "VBAワークショップ.xlam!VBAワークショップ"
Const RootMenuCaption = "ルートメニュー"
Public myMenu As Collection

Sub Auto_Open()
   Set myMenu = AutoMenu.AutoMenu(RootMenuCaption, ThisWorkbook.Sheets(1), ProjectName)
End Sub

  • ProjectName："VBAワークショップ.xlam!VBAワークショップ"はプロジェクト名の正式な書式です。プロジェクトを一意にするために、ブック名.拡張子+"!"+プロジェクト名と書きます。前回も触れましたがブック名とプロジェクト名は同じ方が便利です。上の例ではブック名もプロジェクト名も同じ"VBAワークショップ"です。

  • RootMenuCaption：作成するルートメニューのボタンキャプションなので文字列は自由です。

  • myMenu：AutoMenuの戻値を保存するコレクションです。AutoMenuが作成したメニューオブジェクトが作成した順に保存されます（1オリジン）。このコレクションを使ってメニューのプロパティが操作できます。例えばEnabledプロパティをFalseにすればクリック不可にできます。

  • AutoMenu.AutoMenu：AutoMenuプロジェクト内のAutoMenuプロシージャ

  • ThisWorkbook.Sheet(1)：メニュー指図書のワークシート

（４）アドインを実行する

VBEの［実行］→［マクロの実行］は開発途中のデバッグ機能です。アドインを正式に実行するためには保存する必要があります。VBEの［ファイル］→［〇〇の上書き保存］を選択して表示されるダイアログで、ファイルの種類を"xlam"にすると保存場所がアドインフォルダ(C:\Users\<ユーザー>\AppData\Roaming\Microsoft\AddIns)に変更されるので、ファイル名をプロジェクト名と同じにして保存、一旦エクセルを終了します。
次にエクセルを起動して［ファイル］→［オプション］で表示されるダイアログの左ペインで［アドイン］、右ペインの下の管理リストが「Excelアドイン」であることを確認して［設定］をクリックします。表示されるダイアログに先ほど保存したファイルがあるのでチェックして［ＯＫ］をクリックします（このとき"AutoMenu"も一緒に表示されますが、前述した参照登録用なのでチェックは不要です）。
以上でエクセルの起動と同時にアドインのAuto_Openが実行されます。

（５）メニューの動作を確認する

メニュー指図書では「子メニュー１」をクリックすると「Module1.Sub1」を実行するようになっています。VBEでModule1 に Sub1 を作って動作を確かめてみましょう。

Sub Sub1()
   MsgBox "Hello World !!", , ThisWorkbook.Name
End Sub

変更をコンパイル・保存して一度エクセルを終了します。その後エクセルを再起動して「子メニュー１」をクリックすると"Hello World !!"が表示されるはずです。

次はAutoMenuから返されたコレクション(myMenu)を使って、メニューボタンのプロパティを変更してみましょう。

Sub Sub2()
   With myMenu.Item(3)
      If .State = msoButtonUp Then
         .State = msoButtonDown
      Else
         .State = msoButtonUp
      End If
   End With
End Sub

  • myMenu.Item(3)：myMenuにはAutoMenuで作られたメニューオブジェクトが作られた順に保存されます。「親メニュー」「子メニュー１」「子メニュー２」「子メニュー３」の順です。OptionBaseを０に指定しても、なぜか１オリジンとなるのでmyMenu(3)は「子メニュー２」です。

  • State：ボタンの状態(msoButtonUpとmsoButtonDown)を設定(取得)できます。リボンＵＩの導入前は３Ｄ表現だったのですが、現在はチェックマークの有無に変更されています。

  • Enabled：ここでは取り上げませんでしたがボタン押下の可否も設定(取得)できます。

４．AutoMenuの作成

最後にAutoMenuの作成です。本来は最初にAutoMenuを作成しないと他の作業ができないのですが、説明の都合で順番が最後になってしまいました。

新規にVBAプロジェクトを作成して、プロジェクト名を"AutoMenu"としてください。つぎにモジュールを追加します。名前は"Module1"のままでかまいません。

まずメインのAutoMenu関数です。他から呼ぶことができるようにpublicで宣言します。

Option Explicit
Option Base 0

Public Function AutoMenu(RootCaption As String, Sheet As Worksheet, ProjectName As String) As Collection
   '見出し辞書を作成
   Dim Header As Object
   Set Header = CreateObject("Scripting.Dictionary")
   If HeaderInitialize(Header, Sheet) = False Then
      Exit Function
   End If
   'ワークシートメニューバーを取得
   Dim WorksheetMenuBar As Object
   Set WorksheetMenuBar = Application.CommandBars("Worksheet Menu Bar")
   'ルートメニューがなければ作成
   Dim Root As Object
   Set Root = addRoot(WorksheetMenuBar, RootCaption)
   'スクリプトを読んでルートメニューにツリーを作成
   Set AutoMenu = addTree(Header, Root, Sheet, ProjectName)
End Function

  • RootCaption：エクセルのリボンに表示されるルートメニューの文字列

  • Sheet：メニュー指図書のあるワークシート

  • ProjectName：呼び出し元のプロジェクト名（ブック名+"."+拡張子+"!"+プロジェクト名）

次にAutoMenu関数から呼ばれる関数です。

  • HeaderInitialize関数：見出し辞書をHeaderに作成。戻り値は見出しのチェック結果(OK=True,NG=False)
Private Function HeaderInitialize(Header As Object, Sheet As Worksheet) As Boolean
   Dim Col As Integer
   Dim Caption As String
   For Col = 1 To 4
      Select Case Col
         Case 1: Caption = "NodeMenu"
         Case 2: Caption = "LeafMenu"
         Case 3: Caption = "onAction"
         Case 4: Caption = "ShortCutKey"
      End Select
      If Sheet.Cells(1, Col).Text <> Caption Then
         MsgBox "メニュー指図書 見出しエラー！ " & "(行,列)=(1," & Col & ")" & _
                vbCrLf & vbCrLf & Caption, , Sheet.Name
         HeaderInitialize = False
         Exit Function
      End If
      Header.Add Caption, Col
   Next
   HeaderInitialize = True
End Function
  • addRoot関数：ルートメニューを検索し同じものがなければ追加します
Private Function addRoot(WorksheetMenuBar As Object, RootCaption As String) As Object
   Dim Root As Object
   For Each Root In WorksheetMenuBar.Controls
      If Root.Caption = RootCaption Then
         Set addRoot = Root
         Exit Function
      End If
   Next
   Set addRoot = WorksheetMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
   addRoot.Caption = RootCaption
End Function
  • addTree関数：メニュー指図書を読みながらメニューツリーを新規作成します
Private Function addTree(Header As Object, Root As Object, Sheet As Worksheet, ProjectName As String) As Collection
   Set addTree = New Collection
   Dim myNode As Object   '親メニュー用ワーク
   Dim myLeaf As Object   '子メニュー用ワーク
   Dim Row As Integer
   Row = 2
   Do While True
      Dim NodeCaption As String   '親メニューのキャプション
      Dim LeafCaption As String   '子メニューのキャプション
      Dim onAction As String      'クリック時の処理
      Dim ShortCutKey As String   'ショートカットキー
      '指図書１行分を取得
      With Sheet
         NodeCaption = .Cells(Row, Header.Item("NodeMenu"))
         LeafCaption = .Cells(Row, Header.Item("LeafMenu"))
         onAction = .Cells(Row, Header.Item("onAction"))
         If onAction <> "" Then
            onAction = ProjectName & "." & onAction
         End If
         ShortCutKey = .Cells(Row, Header.Item("ShortCutKey"))
      End With
      'メニュー作成
      Select Case True
         '親メニューのみ
         Case NodeCaption <> "" And LeafCaption = ""
            Set myNode = addButton(Root, NodeCaption, onAction, ShortCutKey, isBeginGroup(Sheet, Row, 1))
            addTree.Add myNode
         '親子両方あるとき
         Case NodeCaption <> "" And LeafCaption <> ""
            Set myNode = addPopup(Root, NodeCaption, isBeginGroup(Sheet, Row, 1))
            addTree.Add myNode
            Set myLeaf = addButton(myNode, LeafCaption, onAction, ShortCutKey, isBeginGroup(Sheet, Row, 2))
            addTree.Add myLeaf
         '子メニューのみ
         Case NodeCaption = "" And LeafCaption <> ""
            Set myLeaf = addButton(myNode, LeafCaption, onAction, ShortCutKey, isBeginGroup(Sheet, Row, 2))
            addTree.Add myLeaf
         '親子両方ないときは終了
         Case Else
            Exit Do
      End Select
      Row = Row + 1
   Loop
End Function


最後にaddTree関数からコールする関数です。

  • isBeginGroup関数：区切り線の判断(上罫線があればTrue)
Private Function isBeginGroup(Sheet As Worksheet, Row As Integer, Col As Integer) As Boolean
   With Sheet
      .Activate
      .Cells(Row, Col).Select
      isBeginGroup = (Selection.Borders(xlEdgeTop).LineStyle <> xlLineStyleNone)
   End With
End Function
  • addPopup関数:ポップアップメニューを追加する
Private Function addPopup(Parent As Object, Caption As String, BeginGroup As Boolean) As Object
   Set addPopup = Parent.Controls.Add(Type:=msoControlPopup, Temporary:=True)
   With addPopup
      .Caption = Caption
      .BeginGroup = BeginGroup
   End With
End Function
  • addButton関数：ボタンメニューを追加する
Private Function addButton(Parent As Object, Caption As String, onAction As String, ShortCutKey As String, BeginGroup As Boolean) As Object
   Set addButton = Parent.Controls.Add(Type:=msoControlButton, Temporary:=True)
   With addButton
      .Caption = Caption
      .onAction = onAction
      If ShortCutKey <> "" Then
         Application.OnKey ShortCutKey, onAction
      End If
      .BeginGroup = BeginGroup
   End With
End Function


コードが完成したらアドインとして保存しておきます。それからこの記事の最初「１．AutoMenuの概要」に戻って読んでいただくと分かりやすいと思います。

５．最後に

こうしたツールのようなプログラムは単体では動作できないので開発時のデバッグ環境に苦労します。なので最初はプロジェクト内でツール本体とそれを使うデバッグ用プログラムを一緒に開発し、あとでバラして仕上げたりします。今回は（バラしたあとの）完成したものを順序良く説明しようとして肝心の本体の説明が一番最後になってしまいました。

とても読みにくい状態でリリースしてしまいましたが、最後までお読みいただきありがとうございました。