OnActionのマクロに引数を渡す方法と、それActionControlプロパティで出来るよ!ってお話

先日、業務でとあるツールを作ってた際にハマったので自分用メモと、有効な代替策が見つかったのでエントリにしてみます。

まずはハマった内容

マクロでオリジナルのコマンドバーやボタンを作って、そこからマクロを実行させる事は結構あります。こういった場合、自作のコントロールのOnActionプロパティに実行させるマクロを設定します。で、この時は、そのマクロに引数を渡したかったので、↓みたいなコードを書いたんですが、

'ThisWorkbookモジュール
Private Sub Workbook_Open()

    Const cnsCaption As String = "Sample Macro"

    Dim btn     As CommandBarButton

    On Error Resume Next
    With Application.CommandBars("Cell")
        .Controls(cnsCaption).Delete '新規作成するボタンを予め削除しておく
        Set btn = .Controls.Add(msoControlButton)
    End With
    On Error GoTo 0

    With btn
        .Caption = cnsCaption
        .OnAction = "ForExample(0)" 'ForExmapleがプロシージャ名。()内が引数でこのサンプルでは0が引数
    End With
        
    Set btn = Nothing

End Sub

'標準モジュール
Sub ForExample(arg As Variant)

    MsgBox arg '渡された引数を表示するだけ

End Sub

↑を実行すると、セル上右クリックで表示されるコンテキストメニューに、↓なボタンが追加されます。

で、追加されたボタンをクリックすると、なんとビックリ!マクロが2回実行されちゃいます。↓が2回表示されます。

どこをどう見ても2回も表示させる様には書いてないので、ホントもう ( ゚ д ゚ )ポカーン です。
穴があくほどコードを見てもどこが悪いのかわからないので、仕方なくコードを書き直してみたり、別のモジュールに書いてみたり、プロシージャ名変えてみたり、と色々試してみたところで状況は変わらず。更に苛立ったのが、デバッグしようと、ForExampleプロシージャのMsgBox関数んとこにブレークポイントを置いても華麗にスルー、ブレークポイントで処理が止まってくれない*1んです。なもんで、しばらくはホント悩んでました。

OnActionに引数を渡したい時は

こういう時には困った時のグーグル先生です。という訳でググってみたところ、あっさりOnActionプロパティの書き方が拙い、って事がわかりました。OnActionに引数付きのプロシージャを設定したい場合、↓の様にシングルコーテーションで囲ってやらないと駄目らしいです。

    With btn
        .Caption = cnsCaption
        .OnAction = "'ForExample(0)'"
    End With

ひとまずこれで自分のやりたかった事は出来た訳ですが…

OnActionになぜ引数を渡すの?

シングルコーテーションで囲んでやる事によって、無事OnActionに引数を渡せる様になった訳ですが、そもそも、なぜOnActionのマクロに引数を渡したかったかというと、渡した引数によって処理を分岐させ、DBへ問い合わせるSQLの検索条件を変えたかったからです。

'ThisWorkbookモジュール
Private Sub Workbook_Open()

    Const cnsCaption1 As String = "Field1で検索"
    Const cnsCaption2 As String = "Field2で検索"

    Dim btn     As CommandBarButton

    On Error Resume Next
    With Application.CommandBars("Cell")
        .Controls(cnsCaption1).Delete
        .Controls(cnsCaption2).Delete
        Set btn = .Controls.Add(msoControlButton)
    End With
    On Error GoTo 0

    With btn
        .Caption = cnsCaption1
        .OnAction = "'ForExample(1)'"
    End With
    
    Set btn = Application.CommandBars("Cell").Controls.Add(msoControlButton)
    With btn
        .Caption = cnsCaption2
        .OnAction = "'ForExample(2)'"
    End With
        
    Set btn = Nothing

End Sub

'標準モジュール
Sub ForExample(arg As Variant)

    Const cnsSQL As String = "select * from hoge where "
    Dim strSQL As String
    
    If arg = "1" Then
        strSQL = cnsSQL & "Field1 = '" & Selection.Value & "';"
    Else
        strSQL = cnsSQL & "Field2 = '" & Selection.Value & "';"
    End If

    MsgBox strSQL
    
End Sub

↑のサンプルコードを実行し、「123」という値が入力されたセル上で右クリック、Field1のボタンの方をクリックすると

なメッセージが表示されます。

これをActionControlプロパティでやると

OnActionに引数付きでマクロを登録する方法でもいけるんですが、引数の使い方が本来の使い方でない気がするし、もっとスマートな方法はないのかと調べてたら、ActionControlプロパティってのが存在する事がわかりました。
VBAのヘルプにはこうあります。

OnAction プロパティに実行中のプロシージャが設定されている CommandBarControl オブジェクトを取得します。実行中のプロシージャがコマンド バー コントロールによって開始されていない場合、このプロパティの戻り値は Nothing になります。値の取得のみ可能です。

ふむふむ、このプロパティを使うと、どのボタンをクリックしたかがわかるみたいですね。
という訳で、ActionControlを使ったコードに書き換えると

'ThisWorkbookモジュール
Private Sub Workbook_Open()

    Const cnsCaption1 As String = "Field1で検索"
    Const cnsCaption2 As String = "Field2で検索"

    Dim btn     As CommandBarButton

    On Error Resume Next
    With Application.CommandBars("Cell")
        .Controls(cnsCaption1).Delete
        .Controls(cnsCaption2).Delete
        Set btn = .Controls.Add(msoControlButton)
    End With
    On Error GoTo 0

    With btn
        .Caption = cnsCaption1
        .OnAction = "ForExample" '引数なんて要らないわよっ
    End With
    
    Set btn = Application.CommandBars("Cell").Controls.Add(msoControlButton)
    With btn
        .Caption = cnsCaption2
        .OnAction = "ForExample" 'だから要らないってば!
    End With
        
    Set btn = Nothing

End Sub

'標準モジュール
Sub ForExample()

    Const cnsSQL As String = "select * from hoge where "
    Dim strSQL As String
    
    If Application.CommandBars.ActionControl.Caption = "Field1で検索" Then
        strSQL = cnsSQL & "Field1 = '" & Selection.Value & "';"
    Else
        strSQL = cnsSQL & "Field2 = '" & Selection.Value & "';"
    End If

    MsgBox strSQL
    
End Sub

うん、こっちの方がスマートですね!*2

マクロが2回実行される理由とブレークポイントをスルーする理由

については調べてみてもわかりませんでした。単なるバグって事なんでしょうか…

*1:自分的には、2回実行されるバグ!?よりも、むしろ、ブレークポイントで止まってくれないバグの方がキツかった…

*2:って、単に好みの問題かもしれませんが(;^ω^)