ExcelマクロでMsgBoxの処理をインターセプト

Excelマクロで以下のMacro1ように、中でMsgBoxを呼び出していると、いちいちメッセージボックスが表示される。


これを別マクロ(Macro2)から複数回呼び出すと、複数回メッセージボックスが表示され、何度も応答しなければならなくなる。

Macro1を別マクロから複数回呼び出しにも耐えられるように、Macro1に手を加えないで、メッセージボックスにOKボタンを押したことにしたい。


そこで、MsgBoxの呼び出しをインターセプトしてみる。

オブジェクトブラウザによると、MsgBoxはVBAライブラリにあるFunctionで第1引数が必須で、戻り値がVbMsgBoxResultのよう。

ということで、VBAライブラリよりも早く呼び出されるように、同プロジェクトの標準モジュールにMsgBox関数を定義する。
まず、標準モジュール追加で、



追加されたモジュールのオブジェクト名をわかりやすく「MsgBoxインターセプト」とする。




この「MsgBoxインターセプト」標準モジュールにMsgBox関数を追加する。



コードは以下のもの。
If文の条件をインターセプトするかどうかの判断で、インターセプトするなら、Then以降の部分に処理を記述し、
インターセプトしないなら、Else以降の部分で、VBAライブラリのMsgBox関数を受け取った引数で呼び出して、結果をMsgBoxに格納する。


Function MsgBox(Prompt As String, Optional Buttons As VbMsgBoxStyle = vbOKOnly, Optional Title, Optional HelpFile, Optional Context) As VbMsgBoxResult
    If Left(Prompt, 2) = "終了" Then
        MsgBox = vbOK
        Exit Function
    Else
        MsgBox = VBA.MsgBox(Prompt, Buttons, Title, HelpFile, Context)
    End If
End Function



これで、メッセージボックスを表示したくない場合の動きができる。
実用ではメッセージボックスを表示しない代わりに、引数と呼び出し時間をログ出力する方がよいかもです。

Excelセル結合での裏技と注意点

  Excelではセル結合という機能があり、これを使うと長方形の複数セルを1まとめで表示させることができる。

この機能を使うことは(他の有識者と同様に)私もお勧めしませんが、使わなければならない時や、
使っても実害がないときで、ダメな理由を説明するのが面倒な時があり、私自身たまに使っています。

実例として、以下のA2～A6セルを結合しようとすると、


以下のメッセージのように結合したセルは、左上セル以外のセルが空欄になる。
(A2セルの値はグループAだが、A3セルからA6のセルの値は空)


なので、オートフィルターを使ってA列の値が「グループA」の行を抽出しようとすると、A2の行のみ表示される。


で、ここからが本題。結合したA2からA5セルと同形のセルをコピーし、「形式を選択して貼り付け」から、
  「数式(F)」もしくは「数式と数値の書式(R)」もしくは「値と数値の書式(U)」をすると、結合セルの左上以外のセルにもコピーされる。

  
  
結果、A7セルで表示している、A2～A6までで値が入っているセルの数え上げが5(貼り付け前は1)、E列で表示しているA列とB列の一致が
2行目から6行目まで一致となっている(貼り付け前は2行目のみ一致)。
これで、オートフィルターも使えるようになる。


問題はここから、
集計する予定があるセルを結合した場合、結合時点では、表示されている値のみで集計される。(C7セルが1)



ただ、同じように形式:数式で貼り付けをした場合、非表示の値も集計結果に表れる。(C7セルが15)




普通に過ごしていたら、こんなことはしないと思うのだけど、
数式で貼り付けすると結合セルの中身まで更新されることが一般的ではない状況なので、悪い考えを持った人が、
自分の数字をごまかすために使ってくるかもしれないと警戒する必要はあるかと思います。
(Excelのごまかし表示方法は、テキストボックスでの覆いかぶせなど、いくらでもあるので結局Excelの作成者の人を見て信じるしかないのですが)