Excel VBA 右クリック メニュー に自作マクロを追加する CommandBarを探ってみた

自作マクロVBAをExcelの右クリックメニューに追加したかったので、
いつものように デバッグ テスト してみます。
※最後の後始末がうまくいかなかったのは・・・実力・確認不足です。

下記、いつもの、あのあの そのその 動画解説です
youtu.be
https://youtu.be/Zeccp33SPQA
目次
00:00 0.こんなことできます
00:52 1.登録は簡単でControls.Add
02:10 2.Cell や Row って、どこでさがすの？
11:00 3.余計なコマンドが残っているので、リセットしたい

'VBA 右クリック メニュー などをキーワードに検索すると、親切な解説ページが見つかります
' http://officetanaka.net/excel/vba/tips/tips30.htm
' https://atmarkit.itmedia.co.jp/ait/articles/1408/25/news030.html

ここから、CommandBarを探ってみました。

0.こんなことできます
右クリックメニューに作成したマクロを登録して、
新規の機能みたいに使えます(使うのか？)
選択範囲を 赤字の太字 に するマクロを右クリックメニューに登録しました。

1.登録は簡単で
　使用したいマクロ・コードを用意したら、

Sub AddMenu_Cell()
    With CommandBars("Cell").Controls.Add()
        .Caption = "test赤太字にする"
        .OnAction = "test赤太字にする"
    End With
End Sub

みたいな感じで、簡単に登録できます。

With CommandBars("cell や rowなどの右メニュー").Controls.Add()
.Caption = "右メニューに表示される文字列"
.OnAction = "マクロ、関数名"

2.Cell や Row って、どこでさがすの？

現在の設定をExcelシートへ書き出してみました。

Sub CommandBarの内容をセルに書き出す()
    Dim n As Integer
    Dim y As Integer
    Dim x As Integer
   
    y = 2
    For n = 1 To CommandBars.Count  'いつものように.Count分ループ
        Cells(y, "A") = CommandBars(n).Name  '名前が付いてます CommandBars("名前")で使えます
        Cells(y, "B") = CommandBars(n).Controls.Count  '↑のコマンド数、アクションの数
        For x = 1 To CommandBars(n).Controls.Count
            Cells(y, x + 2) = CommandBars(n).Controls(x).Caption  'ココでは表題を横に書き込みます
        Next x
        y = y + 1  '1コマンド1行にしたいので、次のセット位置へ
    Next
    
    MsgBox "終了"
End Sub

2.1 コピー や 貼り付け　、 行の高さ など固有の名称があると探しやすいかな？
右クリックメニューのコマンドを 頭に覚えておいて ぉぃぉぃ
探してみるといいかも？
コピー や 貼り付け でしぼったり、
行の高さ など固有の名称があると探しやすいかな？

3.余計なコマンドが残っているので、リセットしたい
使ったら、作ったら、後始末しない、、
私みたいな、行儀の悪い プログラムが変な破片を残していくと・・・

Sub ResetMenu_Cell()
    CommandBars("Cell").Reset
End Sub

で、設定した右クリックのコマンドをリセットできます。

3.1 リセットしないで、ファイルを閉じたら？どうなるの？
リセット忘れて、ファイルを閉じると、
爪痕 じゃなかった、作業の破片が残ります。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'ブック終了時にキレイニする
    Application.CommandBars("Cell").Reset   'これだと、他の登録もリセットされるので、
    Application.CommandBars("Row").Reset    '違うツールで登録があったらマズイけど・・・
    '↑これが、無いと、ブックを閉じた後も、残骸が残っていたり
End Sub

↑をコメントにして、やってみると・・・

終了時、自分が使ったものは、
キレイにしてから出ていきましょう・・・

あれ、、、失敗している。
Workbook_BeforeClose
で、テスト時はできていたのですが・・・

中途半端な動画ですが、一つでも参考となれば幸いです。
#CommandBar #デバッグ

Option Explicit

'VBA 右クリック メニュー などをキーワードに検索すると、親切な解説ページが見つかります
' http://officetanaka.net/excel/vba/tips/tips30.htm
' https://atmarkit.itmedia.co.jp/ait/articles/1408/25/news030.html
'ここで、CommandBars を発見したので、遊んでみます。

Sub test赤太字にする()  'マクロ記録などで作りました
    
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True

End Sub

Sub AddMenu_Cell()
    With CommandBars("Column").Controls.Add()
        .Caption = "★赤太字にする222"
        .OnAction = "test赤太字にする"
    End With
End Sub

Sub DeleteMenu_Cell()
    CommandBars("Cell").Controls("★赤太字にする").Delete
End Sub

Sub ResetMenu_Cell()
    CommandBars("Row").Reset
End Sub


'確認コメと色付け マクロ記録などで作りました
'みたいなマクロを作ったら、右クリックメニューへ追加したい時

Sub 確認コメと色付け()

    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
    Selection.Item(1).AddComment
    Selection.Item(1).Comment.Visible = False
    Selection.Item(1).Comment.Text Text:="このアイテムを:" & Chr(10) & "確認してください"

End Sub

Sub AddMenu_Row()
    With CommandBars("Row").Controls.Add()
        .Caption = "確認済み"  'マクロ名じゃなく、自由につけられるのでよろしく
        .OnAction = "確認済み" '実行されるマクロ
    End With
    
End Sub

Sub DeleteMenu_Row()
    CommandBars("Row").Controls("確認コメと色付け").Delete
End Sub

Sub ResetMenu_Row()
    CommandBars("Row").Reset
End Sub

Sub 確認済み()
'
' Macro1 Macro
'

'
    Selection.ClearComments
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

End Sub

Sub CommandBarの内容をセルに書き出す()

    Dim n As Integer
    Dim y As Integer
    Dim x As Integer
    
    y = 2
    For n = 1 To CommandBars.Count  'いつものように.Count分ループ
        Cells(y, "A") = CommandBars(n).Name  '名前が付いてます CommandBars("名前")で使えます
        
        Cells(y, "B") = CommandBars(n).Controls.Count  '↑のコマンド数、アクションの数
        For x = 1 To CommandBars(n).Controls.Count
            Cells(y, x + 2) = CommandBars(n).Controls(x).Caption  'ココでは表題を横に書き込みます
        Next x
        
        y = y + 1  '1コマンド1行にしたいので、次のセット位置へ
    Next
    
    MsgBox "終了"
    
End Sub