t-hom’s diary

主にVBAネタを扱っているブログです。

VBA リボンから呼び出すマクロに共通の前処理をつける方法

最近仕事で、特定のシートが選択されているときだけ実行できるマクロを作った。
とあるExcel台帳を効率的に処理するためのマクロだが、私はその台帳を作り替える権限が無いので、勝手にボタンなどを配置することはできない。
そこで、台帳そのものに変更を加えずに操作性を改善するため、リボンインターフェースで操作できるアドインを作った。

マクロボタンは、全部で8点。すべてそのシート用に作ったもので、他のシートが選択されていると具合が悪い。そこでボタンが押された時に、該当シートが選択されているかどうかをチェックする前処理が必要になったというのが背景である。

前処理ではシートチェックだけでなく、その台帳のカレント行の取得、最終行の取得などもやりたい。
ボタンから呼び出されるマクロ8点すべてにひとつずつ処理を記入するのはメンテナンスの観点からもやりたくない。

そこで、リボンボタンからの入り口を一つに絞り、そこで共通処理をしたあとに個別の処理を呼び出すような仕組みを考えてみた。

※リボンの作成方法自体は、前に以下の記事で紹介したのでそちらを見て欲しい。

まず、CustomUI.xmlの、グループ部分を以下のように編集する。

<group id="CustomGroupA" label="○○グループ">
<button id="Hello" imageMso="HappyFace" size="large" label="マクロA" onAction="Start" />
<button id="GoodBye" imageMso="SadFace" size="normal" label="マクロB" onAction="Start" />
</group>

このときポイントは、ボタン2つとも、onAction="Start"になっていること。
それと、button idにはそれぞれマクロ名を入れておく。

そしてこれが、vba側のマクロである。

Sub Start(ByVal control As IRibbonControl)
    Call 共通処理
    Application.Run control.ID
End Sub

Private Sub Hello()
    MsgBox "hello"
End Sub

Private Sub GoodBye()
    MsgBox "GoodBye"
End Sub

Private Sub 共通処理()
    MsgBox "RibbonClicked!"
End Sub

まずどのボタンが押されても入り口はStartである。
そこから共通処理が呼び出され、次にApplication.Runの引数に押されたボタンのIDを渡してやる。
先ほどxmlでボタンのIDにマクロ名を設定しているので、そのマクロが実行される。

これで、共通処理を一カ所にまとめることができた。
今回は直接Application.Runにマクロ名の文字列を渡しているが、ボタンのIDを連番にしておいて、Select Caseで判定しても良いかと思う。

2015/3/6追記

この後Twitterでアドバイスをいただき、改訂しました。
改訂版は以下の記事です。

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。