VBA メニューバー生成・自動登録 — CommandBar / auto_open / Workbook_Open / xlam

配布したマクロブックでauto_openが動く人と動かない人がいて、原因を突き止めるのに丸一日費やしたことがあります。xlamアドイン化とWorkbook_Openの使い分けを整理したくて、この記事にまとめました。

CommandBar の基本

図1: CommandBar / CommandBarControl の階層構造

CommandBars コレクションを使うとカスタムメニューバーやツールバーを動的に生成できます。

VBA — CommandBar 生成

Option Explicit

Const MENU_NAME As String = "MyTools"   ' メニュー名（定数で管理）

Sub CreateMenu()
    Dim cb  As CommandBar
    Dim pop As CommandBarPopup
    Dim btn As CommandBarButton

    ' 既存メニューを削除してから作成（重複防止）
    DeleteMenu

    ' メニューバーにポップアップメニューを追加
    Set cb  = Application.CommandBars("Worksheet Menu Bar")
    Set pop = cb.Controls.Add(Type:=msoControlPopup, Temporary:=True)
    pop.Caption = MENU_NAME

    ' ── ボタン追加 ──
    Set btn = pop.Controls.Add(Type:=msoControlButton)
    btn.Caption = "データ取込"
    btn.OnAction = "modProcess.ImportData"   ' 実行するプロシージャ名

    Set btn = pop.Controls.Add(Type:=msoControlButton)
    btn.Caption = "レポート出力"
    btn.OnAction = "modReport.ExportReport"
    btn.BeginGroup = True   ' 区切り線を追加

    Set btn  = Nothing
    Set pop  = Nothing
    Set cb   = Nothing
End Sub

💡 Temporary:=True の意味

Temporary:=True を指定するとExcelを終了したとき自動的に削除されます。False（既定）にすると次回起動時も残りますが、意図しない永続化につながるため True が推奨です。

Click アクション（OnAction）の実装

OnAction には "モジュール名.プロシージャ名" または "プロシージャ名"（同プロジェクト内）を文字列で指定します。

VBA — OnAction の指定パターン

' ── パターン1: 同プロジェクト内のプロシージャ ──
btn.OnAction = "ImportData"

' ── パターン2: モジュール名を明示（推奨） ──
btn.OnAction = "modProcess.ImportData"

' ── パターン3: 別ブックのプロシージャ ──
btn.OnAction = "'Book1.xlsm'!modProcess.ImportData"

' ── 呼び出されるプロシージャ（引数なし） ──
Public Sub ImportData()
    MsgBox "データ取込を実行します"
End Sub

⚠️ OnAction はプロシージャ名の文字列

実行時にプロシージャが見つからないとエラーになります。モジュール名を含めた完全修飾名で書くと名前の衝突を防げます。

自動登録の全手法

メニューをブック起動時に自動登録する方法は5つあります。

方法	タイミング	特徴
`auto_open`	ブックを開いたとき	xlsm内限定・シンプル。ただし `Workbook_Open` と両方あると両方実行される
`Workbook_Open`（ThisWorkbook）	同上	イベント駆動・推奨。`auto_open` より後に実行される
アドイン（.xlam）+ `Workbook_AddinInstall`	アドイン有効化時	Excel全体に常駐させたい場合。複数ブックで共有可能
Personal.xlsb の `Workbook_Open`	Excel起動時	端末固有の個人設定。配布・展開には不向き
レジストリによる自動ロード	Excel起動時	組織展開向け。管理コスト高め

VBA — auto_open（標準モジュール）

' ── 標準モジュール（modSetup）──
Option Explicit

' auto_open はブックを開いたとき自動実行される
Public Sub auto_open()
    CreateMenu
End Sub

Public Sub auto_close()
    DeleteMenu
End Sub

VBA — Workbook_Open（ThisWorkbook）推奨

' ── ThisWorkbook ──
Option Explicit

Private Sub Workbook_Open()
    modSetup.CreateMenu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    modSetup.DeleteMenu
End Sub

VBA — アドイン（.xlam）の場合

' ── アドインとして保存した .xlam の ThisWorkbook ──
Option Explicit

' アドインが有効化されたとき
Private Sub Workbook_AddinInstall()
    modSetup.CreateMenu
End Sub

' アドインが無効化されたとき
Private Sub Workbook_AddinUninstall()
    modSetup.DeleteMenu
End Sub

登録解除を忘れずに

メニュー追加と同じくらい重要なのが登録解除です。ブックを閉じるときにメニューを削除しないと、次回以降の操作に影響します。

VBA — 安全なメニュー削除

Option Explicit

Public Sub DeleteMenu()
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Controls(MENU_NAME).Delete
    On Error GoTo 0
End Sub

重複登録バグと対策

デバッグ中にマクロを何度も実行すると同名のメニューが複数登録されてしまいます。

VBA — 重複防止の CreateMenu

Public Sub CreateMenu()
    DeleteMenu   ' ← 先に削除してから作成
    ' ... 以降メニュー生成処理 ...
End Sub

まとめ

CommandBar + OnAction でカスタムメニューを作り、Workbook_Open で自動登録、Workbook_BeforeClose で解除するパターンが最も汎用的です。Excel全体に常駐させたい場合はアドイン（.xlam）化を検討してください。

✅ 次の章では…

PART 06 では 構造体・連想配列 を解説します。Type 文と Dictionary でJavaのMapを再現する方法を紹介します。

→ PART 06 — 構造体・連想配列へ

CommandBarによるメニュー生成でよくある誤り

CommandBarによるメニューバー追加は、登録・削除処理を誤るとExcelの標準メニューが壊れたり、メニューが重複します。

誤り/失敗パターン	何が起きるか	正しい対処/防止策
Workbook_Openで追加してWorkbook_BeforeCloseで削除しない	Excelを閉じて再度開くたびにメニューが重複追加され、同じ項目が複数並ぶ	Workbook_BeforeCloseでCommandBars("name").Deleteを呼び出し、追加と削除をセットで実装する
既存の標準メニューバーを変更対象にする	Excelの標準メニューが変更・削除され、他のユーザーや他のブックでも影響が出る	自作メニューは専用のCommandBar名を持つバーに追加し、標準メニュー（"Worksheet Menu Bar"等）は変更しない
CommandBar削除前に存在チェックをしない	削除対象が存在しない場合にエラーが発生し、後続の処理が中断する	削除前にOn Error Resume Nextで存在確認するか、CommandBarsコレクションをループしてから削除する

PART 05 — メニューバー生成・自動登録
CommandBar / auto_open / Workbook_Open / xlam