Excel VBA Error Handling: On Error, Err, and Recovery

In Excel VBA, handle runtime errors with On Error GoTo HandlerLabel, inspect the failure through the Err object, and use a cleanup path to restore anything your macro changed. Use On Error Resume Next only for a specific, expected failure you check immediately; otherwise it can hide bugs and leave a macro in an unexpected state.

What VBA error handling does

A runtime error occurs after VBA has started executing a procedure. Common examples include trying to open a missing file, referring to a worksheet that does not exist, converting unsuitable text to a number, or dividing by zero. Without an active handler, VBA displays an error dialog and stops the procedure at the failing statement.

Error handling defines what the procedure should do when that happens. A useful handler can record details, tell the user what went wrong, close a resource, or restore Excel settings before exiting. It does not make a faulty operation succeed, and it does not automatically undo earlier changes. A macro may have written values or deleted data before it reaches an error, so prevention and recovery need to be considered separately.

VBA handles errors within procedures. A handler in one procedure does not automatically manage every error in another: if a called procedure cannot handle an error, VBA can pass it back to the calling procedure, where an enabled handler may catch it. This makes clear procedure boundaries and predictable cleanup especially useful in larger projects.

The three main On Error choices

The On Error statement controls what VBA does when a runtime error occurs in the current procedure. Its three commonly used forms have different purposes:

On Error GoTo ErrorHandler enables an error handler at a named label in the same procedure. This is the usual choice for a macro that needs to report an error and clean up.
On Error Resume Next tells VBA to continue with the statement after the one that failed. It is useful for a narrowly scoped operation where failure is expected and will be checked right away.
On Error GoTo 0 disables the current procedure’s enabled handler. A later unhandled error will stop execution in the normal way.

For example, if a macro briefly uses On Error Resume Next to see whether a worksheet exists, it should inspect Err.Number immediately and then use On Error GoTo 0 to turn that permissive behavior off. Leaving Resume Next active across unrelated statements can make the macro silently skip important failures.

On Error GoTo -1 is different from On Error GoTo 0: it clears the current error condition, whereas GoTo 0 disables the procedure’s enabled handler. It is an advanced control-flow tool, not a routine replacement for a handler label or for checking Err.

How Err describes the failure

When an error occurs, VBA exposes its details through the built-in Err object. The properties most useful for ordinary Excel macros are Err.Number, the numeric error code; Err.Description, a text explanation; and Err.Source, the name of the application or component that raised it. You can include these details in a message or write them to a log.

Capture the details before running other code that might raise an error or change the error information. For example, assign Err.Number and Err.Description to local variables at the start of the handler. Err.Clear clears the stored error information, but clearing it is not a substitute for deciding whether the failed operation was safe to ignore.

VBA also lets your own code raise an error with Err.Raise. This is helpful when an input violates a rule your macro requires, such as a zero denominator. A custom error should explain the condition and, where useful, identify its source; the handler can then present a useful message rather than allowing a confusing failure later.

A dependable handler and cleanup pattern

A practical procedure usually has three parts: normal work, a cleanup section, and an error-handling section. The normal path jumps to cleanup when work succeeds. The handler saves the error details and uses Resume to continue at cleanup. This matters if the macro changed application settings such as screen updating or events: those settings should be restored on both success and failure.

The following illustrative macro calculates values in column D from columns B and C on a worksheet named Data. A zero denominator raises a descriptive error. It also restores the prior application settings rather than assuming they were enabled before the macro began.

Sub CalculateRatios()
    ' Save a copy of the workbook before running: Ctrl+Z cannot undo a macro.
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    Dim oldScreenUpdating As Boolean
    Dim oldEnableEvents As Boolean
    Dim savedErrNumber As Long
    Dim savedErrDescription As String
    Dim savedErrSource As String

    On Error GoTo ErrorHandler

    oldScreenUpdating = Application.ScreenUpdating
    oldEnableEvents = Application.EnableEvents
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For currentRow = 2 To lastRow
        If CDbl(ws.Cells(currentRow, "C").Value) = 0 Then
            Err.Raise vbObjectError + 1000, "CalculateRatios", _
                "Zero denominator in row " & currentRow
        End If
        ws.Cells(currentRow, "D").Value = _
            CDbl(ws.Cells(currentRow, "B").Value) / _
            CDbl(ws.Cells(currentRow, "C").Value)
    Next currentRow

    GoTo CleanExit

ErrorHandler:
    savedErrNumber = Err.Number
    savedErrDescription = Err.Description
    savedErrSource = Err.Source
    Resume CleanExit

CleanExit:
    Application.EnableEvents = oldEnableEvents
    Application.ScreenUpdating = oldScreenUpdating

    If savedErrNumber = 0 Then
        MsgBox "Ratios calculated.", vbInformation
    Else
        MsgBox "Error " & savedErrNumber & " from " & _
            savedErrSource & ": " & savedErrDescription, vbExclamation
    End If
End Sub

Paste code into a standard module in the VBA editor, and make sure the workbook has a worksheet named Data and the expected columns. The example assumes row 1 contains headings, column A identifies the rows to process, column B contains numerators, and column C contains denominators. It writes results as it goes, so an error on a later row does not roll back earlier results. Add validation before writing, or write to a separate output area, if partial results would be a problem.

The handler uses Resume CleanExit rather than simply jumping out with GoTo. Resume leaves the active error-handling state and resumes at the named statement. Also note that a failure inside the handler itself is not automatically caught again by that same active handler. Keep cleanup short and straightforward, and consider a separate strategy if restoring a resource can itself fail.

Choosing Resume, Resume Next, or an exit

After handling an error, the next action depends on what failed. Resume retries the statement that raised the error; it is appropriate only if the cause has been corrected and retrying makes sense. Resume Next continues after the failed statement, which can be useful when the procedure has a deliberate fallback. A jump to a cleanup or exit label is often safest when the procedure cannot continue reliably.

Do not use Resume Next as a general “keep going” switch. If a worksheet lookup fails and the macro then uses the missing worksheet, later lines may fail too—or produce misleading results. Instead, limit the permissive section to the single lookup, examine the outcome, and choose an explicit path for “found” and “not found.” For independent records, a loop can handle a bad row separately, report or log it, and then proceed to the next row without hiding failures elsewhere.

Common mistakes and how to avoid them

Suppressing every error: A broad Resume Next can hide misspelled object names, invalid assumptions, and failed writes. Scope it tightly and check Err.Number immediately.
Forgetting to restore Excel state: If a macro turns off events or screen updating and exits early, Excel may behave strangely afterward. Save the old values and restore them in a shared cleanup path.
Showing an unhelpful message: “Something went wrong” gives little direction. Include the error number and description, and add relevant context such as the file name, worksheet, or row.
Assuming the macro rolled back: Error handling does not reverse cell edits. Plan for partial work, use a backup, or validate inputs before changing the workbook.
Using the wrong scope: An error handler label must be in the same procedure as its On Error GoTo statement. Each procedure that needs its own recovery behavior should define it explicitly.

Debugging errors while developing

During development, stopping at the failing line often reveals the real cause faster than suppressing the error. In the Visual Basic Editor, use Debug > Compile VBAProject to catch many compile-time problems, and run the macro with F8 to step through it. Set a breakpoint by clicking beside a code line or pressing F9, then inspect variables in the Locals window or with the Immediate window.

In the editor, Tools > Options > General includes the Error Trapping choices. “Break on Unhandled Errors” is generally useful: VBA stops for an error no active handler catches, while handled errors can follow the handler. “Break on All Errors” can be useful when diagnosing a problem hidden by an error handler, but it also interrupts execution for errors the code is designed to handle. The VBA editor and menu wording can vary slightly by Excel version.

Running macros safely in Excel

In Excel for Microsoft 365 on Windows, open the editor with Alt+F11, or select Developer > Visual Basic. If the Developer tab is missing, enable it through File > Options > Customize Ribbon and select Developer. On Mac, the editor is available from Tools > Macro > Visual Basic Editor; Option+F11 may open it, though some keyboards require the Fn key. Excel for the web can open a macro-enabled workbook but cannot create, edit, or run VBA macros; use the desktop application.

Save macro workbooks in a format that supports VBA, such as .xlsm, and save a separate copy before running unfamiliar code. Ctrl+Z cannot undo actions performed by a VBA macro. Only enable macros from sources you trust; do not lower macro security settings just to run an unknown file. On Windows, macro controls are available through File > Options > Trust Center > Trust Center Settings > Macro Settings. Organizations may manage these settings centrally.

Frequently Asked Questions

What does On Error GoTo 0 do in VBA?
It disables the error handler currently enabled in that procedure. It does not mean “go to line zero” or erase workbook changes. Use it when you want later errors to be unhandled rather than sent to the handler.

What is the difference between On Error GoTo 0 and On Error GoTo -1?
GoTo 0 disables the enabled handler. GoTo -1 clears the current error condition. They are not interchangeable; most everyday macros can use a named handler and do not need GoTo -1.

How do I ignore an error in Excel VBA?
Use On Error Resume Next only around the operation whose failure is expected, check Err.Number immediately, and then disable that mode with On Error GoTo 0. Ignoring an error without checking can conceal a real problem.

Can Ctrl+Z undo a VBA macro?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse its changes. Save a copy first, and design the macro to validate inputs or preserve data when its changes need to be recoverable.

Can Excel for the web run VBA?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop Excel application to use its VBA code; web-based automation is a separate feature and does not execute VBA.

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.