概要

VBAでOn Errorを書くと、どこでエラーになっているのかマジでわからなくなる。
付属のデバッガ以外の例外機構／エラーレポーティングが貧弱であるため、全てを手で実装しなければならない。
ある程度の規模のアプリケーションで実践できたので、共有。

結論（テンプレート）

Worksheet / ThisWorkbook の書き方テンプレート

' /////////////////////////////////////////////////////////////////
' // 何かのボタンがクリックされたときのイベントハンドラ 
' /////////////////////////////////////////////////////////////////
Private Sub BtnA_click()
On Error Goto HANDLING
    ' メソッドに入ったら必ずロギング
    Call ModuleUtil.Log("Sheet1.BtnA_click() - start")

    Application.ScreenUpdating = False
    
    ' 入力値検証
    If Range("A2").Value2 = "" Then
         ' 注：ここでは安直にRaiseしているけど、やりたい内容によって変えてよい
         Err.Raise Description:="〇〇が空欄です"
    End If

    ' 本処理
    call ExecMain()

    ' メソッド終わったらロギング
    Call ModuleUtil.Log("Sheet1.BtnA_click() - end")
    Application.ScreenUpdating = True
    Exit Sub

' エラーハンドリング
' ------------------------
HANDLING:
    ' エラー内容をユーザーに通知する。独自のヘルパメソッドを介す。
    Call ModuleUtil.MessageBox(Err.Description)
    ' どこのエラーかしっかり記録する。独自のヘルパメソッドを介す。
    Call ModuleUtil.Log("[ERROR] Sheet1.BtnA_click():" & Err.Description)
    ' 例外は握りつぶす

    Application.ScreenUpdating = True
    
End Sub

' /////////////////////////////////////////////////////////////////
' // 本処理
' /////////////////////////////////////////////////////////////////
Private Sub ExecMain()
On Error Goto HANDLING
    ' メソッドに入ったら必ずロギング
    Call ModuleUtil.Log("ExecMain() - start")

    ' 何かのメイン処理をここで行う
    ' 試しにErrorを起こさせてみる
    Call Mid("", -1)

    ' メソッド終わったらロギング
    Call ModuleUtil.Log("ExecMain() - end")
    Exit Sub

HANDLING:
    ' どこのエラーかしっかり記録する。独自のヘルパメソッドを介す。
    Call ModuleUtil.Log("[ERROR] Sheet1.ExecMain():" & Err.Description)
    ' Errorを呼び出し上位に伝搬させる
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Sub

ヘルパメソッドを実装するモジュールのテンプレート

' /////////////////////////////////////////////////////////////////
' // メッセージボックスを出力する
' // 引数省略時は、なぜか、Call で呼ばないとスタックが壊れる
' /////////////////////////////////////////////////////////////////
Public Function MessageBox(Text As String, Optional buttons As Long = 0, Optional title As String = "-1") As Long
On Error GOTO HANDLING
    If (DisabledMsgBox()) Then
        Call Log(Text)
        Exit Function
    End If
    
    Dim argTitle As String
    If (title = "-1") Then
        argTitle = ThisWorkbook.Name
    Else
        argTitle = title
    End If
        
    MessageBox = MsgBox(Text, buttons, argTitle)
    
    Exit Function
    
HANDLING:
    Call Log("[ERROR]ModuleUtil.MessageBox():" & " " & Err.Description)
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Function


' /////////////////////////////////////////////////////////////////
' // ログを出力する
' /////////////////////////////////////////////////////////////////
Public Sub Log(Text As String)
    Dim StrNow As String
    StrNow = Format(Now(), "yyyy/mm/dd hh:nn:ss")
    Debug.Print "[" & StrNow & "] " & Text
    
    If (EnabledLog()) Then
        Dim ts As Object
        Set ts = CreateObject("Scripting.FileSystemObject").OpenTextFile(GetLogFilePath(), 8, True)
        With ts
            .WriteLine "[" & StrNow & "] " & Text
            .Close
        End With
    End If
    
End Sub

ヘルパメソッド

    Call ModuleUtil.MessageBox(Err.Description)
    Call ModuleUtil.Log("[ERROR] Sheet1.BtnA_click():" & Err.Description)

今回携わったアプリは自動実行モード（CUI）と手動実行モード（GUI）を使い分けるため、それぞれのモードで動作を切り替える必要があった。
そのため、過剰にラップしている。この部分は、要件次第で、MsgBoxやDebug.Print直呼びでも構わない。
ただ、実運用を考えると、ログファイルへの出力機構は用意したほうが追跡性あがる。
年月日時分秒の出力はマジで助かる。

開始／終了ログ

    Call ModuleUtil.Log("Sheet1.BtnA_click() - start")
    Call ModuleUtil.Log("Sheet1.BtnA_click() - end")

最悪なくてもいいけどあったらうれしい。具体例は記事の最後に。

Errを捕捉したときの処理

最上位呼出階層

HANDLING:
    Call ModuleUtil.MessageBox(Err.Description)
    Call ModuleUtil.Log("[ERROR] Sheet1.BtnA_click():" & Err.Description)

それ以外の中間処理

HANDLING:
    Call Log("[ERROR]ModuleUtil.MessageBox():" & " " & Err.Description)
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext

とりあえずなんでもログに出しておく。本番運用で泣く。
余裕があれば、引数に何渡されたか全部出しておいてもよいぐらい。
VBA標準関数（Mid、MkDirとか）やCreateObjectしたAPI（Scripting.xxxとか）呼ぶようなやつはとりあえず書いていたほうがよい。入力値次第ですぐにErrorを吐くAPIがあまりにも多い。
Erl() でエラー発生した行番号取れるよって記事を見たけど手元の環境では「0」しか取得できなかった。

以上全盛りしたらどうなるか

テキストログやイミディエイトウィンドウに以下が出る。

[2022/08/21 00:55:01] -------- [AUTORUN START] 〇〇〇.xlsm ---------
[2022/08/21 00:55:01] Sheet1.btnABC_click() - start
[2022/08/21 00:55:01] Sheet1.Validate() - start
[2022/08/21 00:55:01] Sheet1.Validate() - end
[2022/08/21 00:55:01] Sheet1.Generate() - start
[2022/08/21 00:55:14] Save: C:\xxx\sample.txt
[2022/08/21 00:55:15] Sheet1.Generate() - end
[2022/08/21 00:55:15] FIN.
[2022/08/21 00:55:15] Sheet1.btnABC_click() - end
[2022/08/21 00:55:15] Sheet2.btnABC_click() - start
[2022/08/21 00:55:15] Sheet2.Validate() - start
[2022/08/21 00:55:15] [ERROR] Sheet2.Validate():引数の数が一致していません
[2022/08/21 00:55:15] [ERROR] Sheet2.btnABC_click():引数の数が一致していません
[2022/08/21 00:55:16] AUTORUN RESULT: NG
[2022/08/21 00:55:16] -------- [AUTORUN END] 〇〇〇.xlsm ---------

「引数の数が一致していません」とだけメッセージが出る状況に比べてだいぶマシ。

packpak’s diary

【Excel VBA】エラー処理（例外処理）をちゃんと書く

概要

結論（テンプレート）

ヘルパメソッド

開始／終了ログ

Errを捕捉したときの処理

以上全盛りしたらどうなるか