VBA Error Handling: On Error, Cleanup, and Debugging Tips

VBA error handling lets a macro respond when a statement fails instead of stopping unexpectedly. Use On Error GoTo HandlerName for errors that need a deliberate response and cleanup, and reserve On Error Resume Next for a small, controlled operation whose result you check immediately.

What VBA error handling covers

VBA errors are not all the same. A syntax or compile error—such as a misspelled keyword or a missing End If—is detected before the procedure runs, so a runtime error handler cannot catch it. A runtime error happens during execution: for example, a macro refers to a worksheet that does not exist, divides by zero, or tries to use an object that was never set. A logic error is different again: the code runs, but produces the wrong result. VBA will not automatically identify that kind of mistake.

Error handling is therefore not a way to make every macro safe or to hide problems. It is a way to decide what the program should do when an operation fails: explain the failure, stop safely, release resources, or take a documented alternative route. A useful handler preserves enough information to diagnose what happened and avoids continuing with invalid data.

How the On Error statements work

VBA provides three common forms of On Error. They affect the current procedure, not every procedure in a project, so a called procedure may need its own handling strategy.

On Error GoTo Label enables an error handler at a label in the same procedure. When a runtime error occurs, execution transfers there.
On Error Resume Next tells VBA to continue with the statement immediately after the one that failed. It is useful only when the code checks whether the operation succeeded.
On Error GoTo 0 disables the current procedure’s enabled error handler. A later unhandled error can then stop execution normally.

There is also On Error GoTo -1, which clears the current procedure’s error condition without disabling its handler. It is less common in ordinary macros. Do not confuse it with GoTo 0: the latter turns off the handler.

A handler label is an ordinary label, often named Fail or ErrorHandler. Put an Exit Sub or Exit Function before the handler label so successful execution does not fall through into the error-handling code.

Using the Err object to understand a failure

When an error occurs, VBA records details in the global Err object. The most commonly useful properties are Err.Number, a numeric error code; Err.Description, a human-readable explanation; and Err.Source, which can identify the object or application that raised the error. The description can help a user, while the number and source are valuable in a log or diagnostic message.

Capture these values as soon as the handler begins. A later operation can raise another error and replace the information you intended to report. Err.Clear clears the current error information; it does not fix the condition that caused the error or turn off the handler. After a successful procedure, an old Err.Number should not be treated as proof that the most recent statement failed unless the code has deliberately reset and checked it.

A practical handler with a cleanup path

This example updates a cell on a worksheet named Report. If the sheet is missing or another statement fails, the handler saves the error details, displays a message, and reaches the cleanup section. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.

Public Sub UpdateReport()
    Dim ws As Worksheet
    Dim previousScreenUpdating As Boolean
    Dim savedErrNumber As Long
    Dim savedErrDescription As String
    Dim savedErrSource As String

    previousScreenUpdating = Application.ScreenUpdating
    On Error GoTo Fail

    Application.ScreenUpdating = False
    Set ws = ThisWorkbook.Worksheets("Report")
    ws.Range("A1").Value = "Updated"

CleanExit:
    Application.ScreenUpdating = previousScreenUpdating
    Exit Sub

Fail:
    savedErrNumber = Err.Number
    savedErrDescription = Err.Description
    savedErrSource = Err.Source
    MsgBox "Update failed (" & CStr(savedErrNumber) & "): " & _
           savedErrDescription & vbCrLf & "Source: " & savedErrSource, _
           vbExclamation, "Update report"
    Resume CleanExit
End Sub

The saved application setting is restored on both success and failure. In a larger macro, the cleanup section might also close a file, restore calculation settings, or release resources. Keep cleanup focused: if cleanup itself can fail, decide how to report that without hiding the original error. Also be aware that the example’s message box is appropriate for a person running a macro, but an unattended process may need to write errors to a log instead.

When Resume Next is appropriate

On Error Resume Next is sometimes the right tool, but leaving it active across a long procedure is risky. A failed statement may be followed by code that assumes it succeeded, producing incorrect results or a later error that obscures the first one. Keep the scope narrow, inspect Err.Number immediately, and turn off this mode with On Error GoTo 0 when the special case is over.

For example, a macro might try to access a worksheet by name and create it only if the lookup fails. The code should verify that the lookup failed for the expected reason, rather than treating every possible error as “sheet not found.” A missing object, a protected workbook, or another unexpected condition should not silently become a new worksheet.

Public Sub CheckForReportSheet()
    Dim ws As Worksheet
    Dim lookupError As Long

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

    If lookupError = 0 Then
        MsgBox "The Report sheet exists."
    Else
        MsgBox "The Report sheet could not be found. Check the workbook."
    End If
End Sub

This short example illustrates checking the result, but a production macro should consider whether creating a sheet is appropriate and should distinguish expected errors from other failures where possible. Do not use an empty On Error Resume Next block to make a macro appear successful when it has not completed its work.

Propagation, Resume, and handler boundaries

Each procedure has its own error-handling context. If a called procedure does not handle an error, the error can propagate to the calling procedure, where an enabled handler may handle it. This makes it reasonable for a low-level routine to report failure to its caller rather than display a message itself, especially when the caller knows what the user should see.

Inside a handler, VBA provides Resume, Resume Next, and Resume Label. These are not interchangeable with the On Error statements. Resume retries the statement that failed, so it is useful only if the cause has been corrected. Resume Next continues after the failed statement. Resume Label transfers to a chosen location, commonly a cleanup label. In a handler, using Resume Next does not mean “continue after the current line in the handler”; it means continue after the original failing statement.

A handler that is already handling an error cannot simply handle a second error in the same way. If an error occurs while the handler is active, it may be passed to a caller that has an available handler. This is one reason to keep handlers short, save the original error details first, and make cleanup operations simple.

Common mistakes and debugging

The most common mistake is suppressing an error without checking it. Another is placing the handler label above normal code without an exit statement, causing successful execution to run the handler accidentally. A third is showing a generic “something went wrong” message while discarding the error number and description, leaving no useful clue for troubleshooting.

Do not use On Error Resume Next as a project-wide substitute for fixing errors.
Do not assume every error means a missing worksheet, file, or object; check the actual failure and handle only expected cases.
Do not report success after a failed operation unless the macro has genuinely recovered and verified its result.
Do not rely on an error handler to catch syntax or compile errors, or to detect incorrect business logic.

For debugging in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Use Debug > Compile VBAProject to find compile problems, then run the procedure and inspect the highlighted statement when execution breaks. F8 steps through code one line at a time; the Locals and Immediate windows can help inspect variable values. On Mac, Option+F11 opens the editor on many keyboards; some require Fn+Option+F11, depending on keyboard settings. Menu names and available editor features can vary slightly by platform.

Choosing a pattern for a macro

Choose the simplest handling pattern that matches the work. A short procedure that changes a cell may need a handler that reports failure and exits. A procedure that changes Excel application settings or opens external resources should include cleanup so those settings or resources are restored even if a later step fails. A reusable function may be better off raising the error to its caller rather than displaying a message, because the caller can decide whether to retry, log, or notify someone.

Before adding a handler, ask what the macro should do when each important operation fails. Identify which failures are expected, what state may have changed already, and what needs to be restored. Then test both a normal run and a realistic failure, such as temporarily renaming a worksheet the macro expects. A handler is useful only if it leaves the workbook in a known state and gives enough information to diagnose the problem.

Frequently Asked Questions

What does On Error GoTo 0 do in VBA?
It disables the enabled error handler in the current procedure. It does not erase the code at the handler label; it changes how subsequent errors in that procedure are handled.

What is the difference between On Error Resume Next and On Error GoTo?
On Error Resume Next continues after the failed statement, so code must check the result immediately. On Error GoTo Label transfers execution to a handler, where the macro can report the problem and clean up.

Can VBA error handling catch compile errors?
No. Runtime error handlers operate while code is running. Fix syntax and compile errors in the Visual Basic Editor before relying on runtime handling.

Should every VBA procedure have an error handler?
Not necessarily. Procedures that perform risky operations or change state need a clear failure strategy, but a trivial helper may appropriately let an error propagate to its caller. Avoid adding handlers that only hide failures.

Can I undo a VBA macro with Ctrl+Z?
Usually not. Excel’s normal Undo history is cleared or unavailable after a macro runs, so save a separate copy before running code that changes important data.

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.