VBA Error Handler: How On Error Works and When to Use It

In VBA, an error handler is code that responds when a statement fails while a procedure is running. You create one with an On Error statement: use On Error GoTo label for a dedicated handler, or use On Error Resume Next for a narrowly controlled operation whose error you check immediately.

A good handler explains what went wrong, takes any necessary cleanup action, and lets the procedure finish or report failure safely. It should not silently ignore errors that could leave a workbook in an incorrect state.

What an error handler does

VBA reports a run-time error when it cannot complete an operation, such as dividing by zero, opening an unavailable file, or referring to a worksheet that does not exist. Without a handler, execution stops at the failing statement and VBA displays an error dialog. A handler lets your code decide what happens instead.

Error handling is separate from checking whether a value is valid. For example, checking that a cell is not blank before converting it to a number can prevent a predictable problem. A handler is still useful for failures that are difficult to predict, including a missing external file or a locked resource. The strongest procedures use ordinary validation for expected conditions and error handling as a safety net.

VBA error handling is procedure-based. An active handler in one procedure does not automatically become the handler for every procedure it calls. A called procedure can handle its own error; if it does not, the error can propagate to the calling procedure, where an enabled handler may be able to deal with it.

The three main On Error forms

Choose the form that matches what your code needs to do:

On Error GoTo ErrorHandler enables a handler at a named label in the same procedure. This is the usual choice for a procedure that needs to report or recover from unexpected failures.
On Error Resume Next tells VBA to continue with the next statement after a run-time error. It is useful for a short operation where an error is an expected possibility, but it requires an immediate check of the Err object.
On Error GoTo 0 disables the enabled error handler in the current procedure. It does not jump to a label; it restores normal error behavior.

There is also On Error GoTo -1, which clears the current error state without disabling the procedure’s enabled handler. It is a more specialized VBA feature, not a substitute for choosing a clear handler structure. In everyday code, the first three forms cover most needs.

How a GoTo handler works

When an error occurs while On Error GoTo ErrorHandler is enabled, VBA transfers control to the label. The label must be in the same procedure. The built-in Err object provides details such as the error number and description; capture those details before doing other work in the handler.

This example deliberately divides by zero to demonstrate the flow. In normal code, replace the calculation with an operation that can fail in your application.

Sub DemonstrateHandler()
    Dim divisor As Double
    Dim result As Double
    Dim errorNumber As Long
    Dim errorText As String

    On Error GoTo ErrorHandler

    divisor = 0
    result = 10 / divisor
    MsgBox "Result: " & result
    Exit Sub

ErrorHandler:
    ' Save the error details before reporting them.
    errorNumber = Err.Number
    errorText = Err.Description
    MsgBox "Error " & errorNumber & ": " & errorText
    Resume CleanExit

CleanExit:
    ' The procedure ends here after success or error.
End Sub

Exit Sub is important: without it, successful execution could continue into the handler label and display an error message even though nothing failed. Resume CleanExit exits the active error-handling state and continues at the cleanup label. If there is no cleanup to perform, the handler can instead finish the procedure after reporting the problem.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, even if they make changes before an error occurs.

When Resume Next is appropriate

On Error Resume Next is often appropriate for a single operation where failure has a simple, expected meaning. For example, code may try to find a worksheet by name and create or report it if it is absent. The key is to inspect the error immediately, then turn Resume Next off so later mistakes are not hidden.

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

    ' Limit Resume Next to the worksheet lookup.
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Summary")
    lookupError = Err.Number
    Err.Clear
    On Error GoTo 0

    If lookupError <> 0 Then
        MsgBox "The Summary worksheet was not found."
    Else
        MsgBox "The Summary worksheet is available."
    End If
End Sub

The code stores Err.Number before clearing the error and restoring normal handling. Err.Clear resets the error information; it does not itself disable Resume Next. On Error GoTo 0 does that. Keeping this pattern short makes it much less likely that an unrelated error will pass unnoticed.

Resume, Resume Next, and retrying

Inside a handler, the Resume statement determines where execution continues. These forms have distinct meanings:

Resume retries the statement that caused the error. Use it only after correcting the condition that made the statement fail; otherwise the same error may happen again.
Resume Next continues at the statement immediately after the one that failed. This is different from the procedure-level On Error Resume Next setting.
Resume SomeLabel continues at a named location, often a cleanup or exit section.

For example, retrying a file operation makes sense only if the handler can resolve the cause, such as asking for a different valid file path. Continuing after an error is not automatically safe: later statements may rely on a value the failed statement never produced. If the procedure cannot continue correctly, report the failure and exit rather than pretending it succeeded.

Using Err details usefully

Err.Number identifies the error, while Err.Description supplies a text explanation. A handler can use them to make a useful message or record a failure in a log. Avoid messages that say only “Something went wrong”; include the operation or item involved where practical, while keeping technical details available for troubleshooting.

Do not assume that an error number alone explains the cause in every context. The same broad type of problem can arise from different operations, and a description may depend on the source of the error. Capture the details promptly because subsequent operations can change the Err information. If your code raises its own error with Err.Raise, give it a clear description and handle it consistently with other failures.

Cleanup and reliable procedure structure

Procedures that change application settings, open files, or acquire resources need a plan for cleanup on both the success and error paths. For example, code may temporarily turn off screen updating or open a workbook. If an error interrupts the procedure, leaving a setting changed or a resource open can affect later work.

A common structure is to enable a handler near the start, perform the work, and use a shared exit section to restore state. The handler records the failure, performs or reaches cleanup, and then reports the error. Avoid placing ordinary work after the handler label unless it is intentionally part of the error path. Also avoid a handler that resumes at a point where required setup has not occurred.

When restoring application state, preserve the original value rather than assuming it was on. For example, if code changes Application.ScreenUpdating, store its prior value and restore that value on every exit path. This is more dependable than always setting it to True, because another procedure or the user may have had it off before your procedure began.

Common mistakes and misconceptions

Leaving Resume Next active: Later errors may be ignored, and variables can keep default or stale values. Restrict it to a small block and restore normal handling with On Error GoTo 0.
Forgetting Exit Sub or Exit Function: Normal execution can fall through to the handler label. Exit the main path before the handler label.
Assuming a handler fixes the cause: Handling an error changes control flow; it does not make a missing sheet, invalid value, or failed file operation succeed.
Using a broad handler to hide bugs: A blanket “ignore every error” approach can make a macro appear successful while producing incomplete results. Handle known cases explicitly and surface unexpected failures.
Expecting Ctrl+Z to reverse a macro: Excel’s undo history does not provide a general undo for VBA actions. Save a backup before running code that changes data.

Excel, other Office apps, and platform notes

The VBA error-handling statements are used across Office applications, including Excel, Word, and Access. The error handler belongs to the VBA procedure, not to a worksheet formula. Excel-specific operations, such as working with Worksheet objects, naturally differ from operations in another Office program.

VBA is available in Excel for Mac as well as Windows, but some platform-specific features and external components differ. Code that depends on Windows APIs, particular file paths, or certain third-party controls may need changes on Mac. The basic On Error statements and the principle of checking Err remain the same. Test platform-dependent macros in the environment where they will run.

Choosing the right pattern

Use this quick decision guide when writing a procedure:

If an unexpected failure should stop the operation and be reported, use On Error GoTo label.
If one specific lookup or optional operation may fail normally, use On Error Resume Next for that operation only, inspect Err, then disable it.
If execution must continue after failure, first confirm that later code does not depend on the failed result.
If the procedure changes settings or opens resources, design cleanup for both success and failure.
If an input problem is predictable, validate the input directly instead of relying on an error dialog or a broad handler.

A useful handler is deliberate and limited: it accounts for a failure the procedure can meaningfully respond to, while leaving unexpected problems visible enough to diagnose.

Frequently Asked Questions

What does On Error GoTo 0 do in VBA?
On Error GoTo 0 disables the currently enabled error handler in the current procedure. It does not jump to a label or clear the error details by itself.

What is the difference between On Error GoTo and On Error Resume Next?
On Error GoTo label transfers control to a handler when an error occurs. On Error Resume Next skips the failed statement and continues with the next one, so you must check whether the operation failed.

Can VBA have more than one error handler in a procedure?
A procedure can contain more than one label, but only one enabled error-handling setting applies at a time. A later On Error statement can change that setting; labels do not act as independent, simultaneously active handlers.

Does Err.Clear turn off error handling?
No. Err.Clear clears the current error information. To turn off Resume Next or another enabled handler in that procedure, use On Error GoTo 0.

Can I undo a VBA macro with Ctrl+Z?
Generally, no. Running a macro does not provide Excel’s usual undo history for its changes. Save a separate copy before running a macro that modifies workbook content or settings.

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.