Error Logging and Robust Macros

Analogy categoryAnalogies

From Crashing Macros to Robust Ones

A robust macro anticipates that files may be missing, cells may hold unexpected text, and network drives may vanish mid-run. Instead of showing users a raw runtime error and dumping them into the code editor, robust code traps the failure, records enough context to diagnose it later, restores a clean state, and reports a friendly message. The two pillars are structured error handling with On Error and persistent logging that survives after the workbook closes — because a message box a user dismissed at 2 a.m. tells you nothing the next morning.

🏏

Cricket analogy: A robust macro is a batting side with a deep order and a nightwatchman — when a top-order wicket falls unexpectedly, the innings recovers and the scorebook records exactly how, rather than the whole team collapsing without explanation.

Structured On Error Handling

The core pattern uses 'On Error GoTo ErrHandler' near the top of a procedure to redirect execution to a labelled block when any runtime error occurs. The procedure body ends with 'Exit Sub' so a normal run never falls into the handler; the ErrHandler label follows, reading the Err object for Number, Description, and Source. Crucially you should always include a cleanup section that restores application state — turning ScreenUpdating and EnableEvents back on, releasing object references — reached by both the normal exit and the error path so the environment is never left corrupted.

🏏

Cricket analogy: On Error GoTo is the DRS review that intercepts a wrong on-field call before it counts; Exit Sub is the declaration that ends the innings cleanly, and the cleanup block is the groundstaff resetting the pitch whether the day ended in a win or a washout.

vba

Sub ProcessOrders()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Orders")
    ' ... processing that might fail (bad data, missing file, etc.) ...
    Dim total As Double
    total = ws.Range("D2").Value / ws.Range("E2").Value   ' could raise div-by-zero (11)

CleanExit:
    Application.ScreenUpdating = True   ' always restore state
    Set ws = Nothing
    Exit Sub

ErrHandler:
    LogError "ProcessOrders", Err.Number, Err.Description
    MsgBox "Could not process orders. The issue was logged. " & _
           "(Error " & Err.Number & ")", vbExclamation, "SkillVeris"
    Resume CleanExit          ' route through cleanup, do not just End
End Sub

' Central logger: appends one timestamped line to a text file
Sub LogError(procName As String, errNum As Long, errDesc As String)
    Dim f As Integer
    f = FreeFile
    Open Environ("USERPROFILE") & "\vba_error_log.txt" For Append As #f
    Print #f, Format(Now, "yyyy-mm-dd hh:nn:ss") & vbTab & _
              procName & vbTab & errNum & vbTab & errDesc
    Close #f
End Sub

The Err Object and Resume

Inside a handler the Err object exposes Err.Number (0 means no error), Err.Description, and Err.Source. You can raise your own errors with 'Err.Raise 513, "MyModule", "Custom message"' using numbers 513–65535 for application-defined errors. The Resume family controls what happens next: 'Resume' retries the exact line that failed (useful after fixing a transient condition), 'Resume Next' skips to the line after the failure, and 'Resume label' jumps to a named cleanup section. Calling 'Err.Clear' resets the object, and 'On Error GoTo 0' disables the current handler so later errors surface normally.

🏏

Cricket analogy: Err.Number is the exact dismissal code on the scorecard — lbw versus caught; Resume retries the delivery like a no-ball being re-bowled, Resume Next moves to the next ball, and Err.Raise is the fielding side appealing to flag a specific breach.

'On Error Resume Next' silently swallows every error and continues, which hides bugs and can leave your workbook in a corrupt state. Use it only around a single statement whose failure you genuinely expect and then immediately check Err.Number — and re-enable proper handling right after with 'On Error GoTo 0' or a labelled handler. Blanket Resume Next across a whole procedure is a classic source of hard-to-find data corruption.

Persistent Logging Strategies

A message box vanishes the moment it is dismissed, so robust macros persist errors somewhere durable. The lightweight approach writes timestamped lines to a text file using FreeFile with Open ... For Append, as in the LogError routine above — one tab-separated record per event including procedure name, error number, and description. For richer logs, write rows to a hidden 'Log' worksheet, or use the FileSystemObject (Microsoft Scripting Runtime) for CreateTextFile and OpenTextFile with more control. Whatever the target, log the timestamp, the failing procedure, Err.Number, Err.Description, and any key input values, and consider rotating or capping the file so it does not grow without bound.

🏏

Cricket analogy: Persistent logging is the official scorebook that survives long after the match ends, recording every wicket with over and bowler — unlike a spectator's fleeting cheer, it lets analysts study the collapse weeks later.

Wrap FreeFile/Open logging in its own tiny error handler (or On Error Resume Next around just the file write) so that if the log target is unavailable — a locked file or missing folder — the logging failure itself doesn't crash the macro it is meant to protect. A logger that can take down the caller defeats the whole purpose.

Robust macros trap errors, restore application state, log context, and show a friendly message instead of crashing.
'On Error GoTo ErrHandler' with an 'Exit Sub' before the label is the standard structured-handling pattern.
Always route both normal and error paths through a cleanup label (Resume CleanExit) that restores ScreenUpdating, EnableEvents, and object references.
The Err object gives Number, Description, and Source; Err.Raise creates custom errors in the 513–65535 range.
Resume retries the failing line, Resume Next skips it, and Resume label jumps to cleanup; On Error GoTo 0 disables the handler.
Avoid blanket 'On Error Resume Next' — it hides bugs; scope it to one expected statement and check Err.Number immediately.
Persist errors to a text file (FreeFile/Append), a hidden log sheet, or FileSystemObject with timestamp, procedure, and error details.