Excel VBA Error Handler: How to Catch and Fix Errors

An Excel VBA error handler is code that tells a macro what to do when a runtime error occurs. In most procedures, use On Error GoTo HandlerName to route an error to a labeled handler, capture details from the Err object, and then report the problem or clean up safely.

What an Excel VBA error handler does

VBA can stop a macro when it encounters a problem it cannot resolve, such as trying to use a worksheet that does not exist or opening a file that is unavailable. Without deliberate handling, Excel displays a runtime error dialog and highlights the failing line. An error handler gives the procedure a defined response instead: it can show a useful message, close resources, record the error, or pass control to another procedure.

Error handling is not the same as preventing every error. A handler does not make a missing worksheet appear or correct an invalid calculation. It controls what happens after an error is raised, and can make the failure easier to understand and recover from. A good handler avoids hiding a problem that should be fixed.

VBA has three commonly used error-handling statements:

On Error GoTo label activates a handler at the named label in the current procedure.
On Error Resume Next tells VBA to continue with the next statement after an error, so it is best kept to a small, intentional section.
On Error GoTo 0 turns off the current procedure’s enabled error handler.

How VBA error handling works

A procedure with On Error GoTo HandlerName continues normally until an error occurs. VBA then transfers execution to the matching label, usually near the end of the procedure. The handler can inspect Err.Number and Err.Description to identify the error. A label is a location in code, not a separate procedure, and its name must be followed by a colon.

For example, if a macro tries to access a worksheet named Input and that sheet is absent, VBA raises an error. With an active handler, execution jumps to the handler label. Without one, the runtime error interrupts the macro. After handling an error, use Exit Sub or Exit Function before the label when necessary: otherwise, normal execution can fall through into the handler code even when no error occurred.

Each procedure needs to manage its own error flow. A handler in one procedure does not automatically provide a complete handling strategy for every procedure it calls. An unhandled error can propagate back to a calling procedure, where an enabled handler may catch it. This is one reason reusable routines should either handle errors clearly or allow them to reach a caller that can respond appropriately.

Choose the right error-handling pattern

Use On Error GoTo for the main handling strategy in a procedure. It is useful when several operations can fail and you want one place to report errors or release resources. Use On Error Resume Next only when a particular operation is expected to fail sometimes and the next step checks whether it actually did.

A practical choice depends on what the macro should do if the operation fails:

Stop and explain: show a clear message and end the procedure when continuing could produce misleading results.
Recover: try an alternative action, such as asking the user to select a valid file, then continue only if the required data is available.
Clean up: close a file or restore an application setting before the procedure exits.
Ignore a specific expected error: briefly use On Error Resume Next, inspect Err.Number, and restore normal handling immediately.

Do not choose a pattern just because it suppresses a dialog. If the macro carries on with incomplete data, the resulting workbook may look successful while containing incorrect output.

A basic On Error GoTo example

This procedure reads cell A1 on a worksheet named Input. It handles a missing sheet and also checks whether A1 contains an Excel error value. Before running any macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.

Sub ReadInputValue()
    Dim ws As Worksheet
    Dim valueRead As Variant
    Dim errorNumber As Long
    Dim errorDescription As String

    On Error GoTo HandleError

    Set ws = ThisWorkbook.Worksheets("Input")
    valueRead = ws.Range("A1").Value

    If IsError(valueRead) Then
        Err.Raise vbObjectError + 1000, "ReadInputValue", _
            "Cell A1 contains an Excel error value."
    End If

    MsgBox "Value: " & CStr(valueRead), vbInformation, "Read input"
    Exit Sub

HandleError:
    errorNumber = Err.Number
    errorDescription = Err.Description
    MsgBox "Error " & CStr(errorNumber) & ": " & _
        errorDescription, vbExclamation, "Read input"
End Sub

The Exit Sub prevents successful execution from dropping into the handler. When an error occurs, the handler copies the number and description before displaying a message. Capturing these details early is a useful habit, especially if later code might call another routine that changes the current error information. The custom Err.Raise line creates a deliberate error when the cell contains an Excel error value, so the same handler can report it.

Using On Error Resume Next safely

On Error Resume Next does not jump to a label. It allows the next statement to run after a failing statement, which can be handy for a narrow check such as looking for an optional worksheet. The important step is to inspect the error immediately; otherwise, the macro may continue as if the operation succeeded.

Sub CheckForInputSheet()
    Dim ws As Worksheet
    Dim lookupError As Long

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Input")
    lookupError = Err.Number
    Err.Clear
    On Error GoTo 0

    If lookupError <> 0 Then
        MsgBox "The Input worksheet was not found.", _
            vbExclamation, "Check worksheet"
        Exit Sub
    End If

    MsgBox "The Input worksheet is available.", _
        vbInformation, "Check worksheet"
End Sub

Here, error suppression applies only while VBA tries to assign the worksheet reference. The code stores the error number, clears the Err object, and restores normal handling with On Error GoTo 0. Avoid placing a long block of unrelated work under On Error Resume Next: an unexpected error in that block could be silently skipped and leave variables or results in an invalid state.

Understanding Err.Number and Err.Description

The built-in Err object provides information about the most recent error. Err.Number is its numeric code; Err.Description is a text explanation. Err.Source can identify the application or procedure that raised the error. These properties are useful for both user-facing messages and diagnostic logs.

For users, translate technical details into an action they can take. “The Input worksheet was not found” is more helpful than a bare error number. For troubleshooting, include the number and the operation that failed in a log or message. Do not report that a task completed successfully if an error prevented it from finishing.

Err.Clear clears the information stored in the Err object; it does not turn off an active error handler. On Error GoTo 0 disables the procedure’s current handler. These statements solve different problems, so using one does not automatically replace the other.

Cleanup, recovery, and passing errors on

Some procedures change Excel’s application settings, open files, or create other resources. If an error occurs, cleanup may be necessary before the procedure exits. A common structure sends both the normal path and the error path through a cleanup section, while preserving the original error details before cleanup begins.

For example, a procedure that temporarily disables screen updating should restore it even if a later operation fails. The exact cleanup depends on what the macro changed. Restore only settings your procedure owns, and be cautious about swallowing errors raised during cleanup. In larger routines, a clear exit path and a clearly labeled error path make it easier to verify that every route closes files and restores settings.

Sometimes the best response is to let a calling procedure decide what to do. In that case, capture the relevant error details and raise an error onward rather than displaying a message at every level. Repeated message boxes can confuse users when one failure travels through several procedures. Decide which layer has enough context to explain the problem and handle it there.

Common mistakes and troubleshooting

Forgetting to exit before the handler: add Exit Sub or Exit Function before the handler label so successful code does not run the error branch.
Using Resume Next everywhere: this can hide errors and make later results unreliable. Limit it to a small operation and check the result immediately.
Clearing or replacing error details too soon: save Err.Number and Err.Description before calling code that may raise another error.
Assuming the handler fixes the cause: handling an error controls the response; it does not correct bad input, missing files, or incorrect logic.
Ignoring compile errors: error handlers address runtime errors, not syntax errors or undeclared-variable problems that prevent code from compiling.

If a handler does not run, check that the statement raising the error is inside the procedure with the active handler and that execution has not already entered the handler. Also check for On Error GoTo 0 earlier in the procedure, which disables that handler. An error that occurs inside an already active handler is not automatically caught again by the same handler; a caller may be able to handle it, but the procedure should not assume it can simply re-enter its own handler.

Testing and running VBA safely

Test both the normal path and realistic failure paths. For the example above, try it once with the Input sheet and a normal value in A1, then rename or temporarily remove the sheet to confirm the message is useful. You can also enter a worksheet error value in A1 to exercise the deliberate error check. Restore workbook contents after testing.

On Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic. The Developer tab may need to be enabled in Excel Options. On Mac, use Developer > Visual Basic; the keyboard shortcut can vary with the keyboard and function-key settings. VBA macros run in the desktop Excel application, not Excel for the web. Mac Excel supports VBA, but Windows-specific APIs and some automation components may not work on Mac.

Save macro-enabled workbooks as Excel Macro-Enabled Workbook (.xlsm) when they need to retain VBA code. Only enable macros from sources you trust, and save a separate copy before running code that changes data or workbook structure. Excel’s Undo command, including Ctrl+Z on Windows, does not reverse most macro actions; on Mac, Command+Z likewise is not a dependable way to undo a macro.

Frequently Asked Questions

What is the basic VBA error handler?
Use On Error GoTo HandlerName near the start of a procedure, then add a matching label and handling code. Put Exit Sub or Exit Function before the label to keep normal execution from falling into it.

What is the difference between On Error GoTo and On Error Resume Next?
On Error GoTo transfers execution to a named handler. On Error Resume Next skips the statement that failed and continues at the following statement. The latter should be used briefly, with an immediate check of Err.Number.

How do I turn off an Excel VBA error handler?
Use On Error GoTo 0 in the procedure where the handler is active. This disables the current handler. Use Err.Clear when you instead need to clear the stored error information.

Can an error handler catch a syntax error?
No. VBA must be able to compile the procedure before it can run its error-handling code. Correct syntax and compile-time issues in the Visual Basic Editor; handlers are for runtime errors encountered while code is executing.

Can Excel for the web run VBA error handlers?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use VBA, subject to macro security settings and compatibility of the code with that platform.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.