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回実行される理由とブレークポイントをスルーする理由
については調べてみてもわかりませんでした。単なるバグって事なんでしょうか…