Building a Global Error Handler: The Err.Number and Err.Description guide.

While small scripts can rely on quick inline checks, enterprise-grade Excel applications require a systematic approach to crashes. When a macro breaks in front of business users, an unhandled runtime error exposes raw code modules, leaves system properties like ScreenUpdating frozen, and risks leaving data partially processed.

A professional global error handling pattern intercepts failures, extracts key diagnostic metadata via the Err object, restores the user’s Excel environment, and logs the incident cleanly.

How Do You Build a Procedure-Level Error Handler in VBA?

A procedure-level error handler uses On Error GoTo ErrorHandler at the start of a routine, routes execution to an exit block before the handler, and isolates the error recovery routine under a dedicated label. The handler evaluates Err.Number and Err.Description, logs the failure, resets system flags, and safely exits the procedure.

The Production-Ready Handler Pattern

Here is the standard architectural framework for every mission-critical procedure:

Public Sub ProcessFinancialReport()
    ' 1. Route all errors to the local trap
    On Error GoTo ErrHandler

    ' Optimize performance
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' --- MAIN PROCEDURE WORK ---
    Worksheets("Data").Range("A1").Value = 100 / 0 ' Simulated division by zero
    ' ---------------------------

CleanExit:
    ' 2. ALWAYS reset environment properties before exiting
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    ' 3. Capture details and report or log
    Call LogGlobalError(Err.Number, Err.Description, "ProcessFinancialReport")

    MsgBox "An error occurred during processing:" & vbCrLf & _
           "Code: " & Err.Number & vbCrLf & _
           "Details: " & Err.Description, vbCritical, "Process Error"

    Resume CleanExit
End Sub

Notice the critical placement of CleanExit: with Exit Sub immediately above ErrHandler:. Without Exit Sub, your procedure will fall through and execute the error handler even when the macro runs successfully.

---

Anatomy of the VBA Err Object

Whenever an error occurs, VBA automatically populates the globally accessible Err object with several diagnostic properties:

• Err.Number: The numeric runtime code (for example, 9 for Subscript out of range, 11 for Division by zero, or 1004 for application-level failures).
• Err.Description: A textual explanation of the issue generated by Excel or the operating system.
• Err.Source: The name of the project or application that originated the error.
• Err.HelpFile & Err.HelpContext: References to documentation files (rarely used in custom macros).
• Err.Clear: Explicitly resets all properties of the Err object back to zero and empty strings.
• Err.Raise: Manually generates a custom runtime error to trigger handling logic.

---

Pinpointing the Exact Line of Failure Using Erl

One limitation of standard error handling is that Err.Number identifies what broke, but not where it broke. VBA provides a built-in, often-overlooked function called Erl (Error Line) that returns the exact line number of the failing statement, provided your code uses line numbering.

Public Sub RunAuditSequence()
    On Error GoTo AuditErr

10: Application.ScreenUpdating = False
20: Sheets("Ledger").Select
30: Range("A1").Value = "Audit Started"
40: Range("Z99").Value = 10 / 0 ' Crash here

CleanExit:
    Application.ScreenUpdating = True
    Exit Sub

AuditErr:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") occurred on Line " & Erl, _
           vbCritical, "Execution Failure"
    Resume CleanExit
End Sub

If line 40 throws an error, Erl evaluates to 40. Line numbering can be added automatically using free VBA utilities like MZ-Tools. For advanced implementation patterns, review How to find the exact line causing a crash using Erl..

---

Building a Reusable Centralized Error Logger

Rather than pasting complex message box strings across dozens of subroutines, delegate reporting to a single centralized logging subroutine:

Public Sub LogGlobalError(errNum As Long, errDesc As String, procName As String)
    Dim logFile As String
    Dim fNum As Integer

    logFile = ThisWorkbook.Path & "\vba_error_log.txt"
    fNum = FreeFile

    ' Append failure details with timestamp
    Open logFile For Append As #fNum
    Print #fNum, Format(Now, "yyyy-mm-dd hh:nn:ss") & " | Proc: " & procName & _
                 " | Error " & errNum & ": " & errDesc & _
                 " | User: " & Environ("USERNAME")
    Close #fNum

    ' Output to the developer console if open
    Debug.Print "[LOG] " & Format(Now, "hh:nn:ss") & " - " & procName & " - " & errNum & " - " & errDesc
End Sub

This ensures that whenever a macro fails on an end user’s workstation, a diagnostic audit trail is written locally without requiring user intervention. For background logging tips, see Debug.Print: The developer’s best friend for silent error logging..

---

Transitioning and Clearing Handlers: On Error GoTo -1

If an error occurs inside your error handling routine itself, VBA enters an unresolved exception state and halts immediately.

To clear this exception state and allow a new handler to engage within the same subroutine, issue On Error GoTo -1:

Sub DoubleHandlerPattern()
    On Error GoTo FirstTrap
    Error 11 ' Trigger Division by zero
    Exit Sub

FirstTrap:
    ' Clear the active exception state completely
    On Error GoTo -1

    ' Now we can establish a brand new trap safely
    On Error GoTo SecondTrap
    Error 9 ' Trigger Subscript out of range
    Exit Sub

SecondTrap:
    MsgBox "Second error handled safely: " & Err.Description
End Sub

For surgical line-by-line checks that do not require full branching labels, contrast this pattern with Using On Error Resume Next vs. On Error GoTo 0 (The right way)..

To ensure that sudden halts never leave your workbook in a non-calculating or un-rendering state, build and maintain a dedicated restoration routine following Building a “Reset” macro to fix Excel settings after a crash (Calculation, ScreenUpdating).. For a broader architectural approach to defensive design, explore the VBA Debugging Masterclass: Professional Techniques to Audit and Clean Your Code.

---