Excel VBA Reset Error Handling: What Err.Clear Does

To turn off an active VBA error handler in the current procedure, use On Error GoTo 0. Use Err.Clear to clear the Err object’s stored error details; it does not turn the handler off, while On Error GoTo -1 resets the procedure’s current error state.

What “reset error handling” means

In Excel VBA, “reset error handling” can refer to more than one thing. A procedure can have an error-handling rule enabled, an error can be active and executing that rule, and the Err object can contain details about the most recent error. Those are related, but they are not interchangeable. Choosing the right reset depends on which state you want to change.

For example, On Error GoTo HandlerName enables a handler that directs execution to a label in the same procedure when a run-time error occurs. On Error Resume Next tells VBA to continue with the next statement after an error. Both are error-handling modes. Leaving either mode in place longer than intended can make later failures harder to notice.

Most ordinary cleanup is done with On Error GoTo 0: it disables the procedure’s currently enabled handler, so subsequent errors use VBA’s normal behavior unless another handler is set. If you mean “erase the error number and description,” use Err.Clear. If VBA is still in an active error state and you need to reset that state, On Error GoTo -1 is the specialized statement.

On Error GoTo 0, On Error GoTo -1, and Err.Clear

These statements have distinct jobs. The key practical difference is whether you are changing the handler, clearing an active error state, or clearing the diagnostic values:

On Error GoTo 0 disables the enabled error handler in the current procedure. It does not erase the values already stored in Err.Number or Err.Description.
Err.Clear clears the Err object’s error information. It does not disable On Error Resume Next or remove a handler installed with On Error GoTo HandlerName.
On Error GoTo -1 resets the current procedure’s error state and disables its enabled handler. If you want a handler active afterward, set it again with On Error GoTo HandlerName.
Resume, Resume Next, and Resume LabelName are control-flow statements used to leave an active error handler and continue execution. They are not synonyms for clearing the Err object.

It is useful to save the error number and description before clearing or resetting anything. Once cleared, those details are no longer available from the Err object. This matters especially in a handler that needs to report the original failure or write it to a log.

How VBA error handlers work

Error handling is local to a procedure, not a workbook-wide switch. A handler set in one Sub or Function does not automatically become the handler for every other procedure. If a called procedure does not handle an error itself, VBA can pass the error back to the calling procedure, where an enabled handler may deal with it. This makes it important to decide which procedure owns recovery and which one simply reports or passes on the problem.

When an error transfers execution to a handler label, that handler is considered active while its code runs. A second error raised inside the active handler cannot be handled by that same active handler in the usual way; it can propagate outward. Use a clear exit path, and use an appropriate Resume statement when the procedure is meant to continue after handling the error. For an error that should end the procedure, report it and exit rather than accidentally falling through into other code.

Also distinguish a handler being enabled from a handler being active. On Error GoTo HandlerName enables a handler. A run-time error transfers execution to its label and makes it active. On Error GoTo 0 disables the enabled handler; it does not jump to a label or resume execution. The line after it runs next.

A safe pattern for handling and then disabling errors

For a normal procedure, enable a handler near the operation that can fail, capture useful details at the handler, and turn off the handler once it is no longer needed. This example looks for a worksheet named Summary. The error handler reports a missing sheet or another run-time error rather than letting execution fall into the label accidentally.

Sub ReadSummaryCell()
    Dim ws As Worksheet
    Dim errorNumber As Long
    Dim errorText As String

    On Error GoTo HandleError

    Set ws = ThisWorkbook.Worksheets("Summary")
    Debug.Print ws.Range("A1").Value

    On Error GoTo 0
    Exit Sub

HandleError:
    ' Save the details before changing error handling.
    errorNumber = Err.Number
    errorText = Err.Description
    On Error GoTo 0

    MsgBox "Could not read the Summary sheet. Error " & _
           errorNumber & ": " & errorText
End Sub

Here, On Error GoTo 0 in the normal path prevents the handler from remaining enabled for later code in that procedure. The handler also disables itself after saving the details. The Exit Sub before the label is important: without it, successful execution could continue into the handler code. Adapt the message or recovery action to the task; for a required worksheet, stopping with a clear explanation may be safer than silently carrying on.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a macro may change or delete workbook data even if its error handling is correct.

Using Err.Clear with On Error Resume Next

On Error Resume Next can be useful for a narrowly defined operation that may fail in an expected way, such as looking up an optional worksheet. Check Err.Number immediately after the operation, then restore normal handling with On Error GoTo 0. Leaving Resume Next enabled across a long block can conceal unrelated errors, because VBA keeps moving past failing statements.

Sub CheckOptionalSheet()
    Dim ws As Worksheet

    ' Limit Resume Next to the optional lookup.
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Optional")

    If Err.Number <> 0 Then
        Debug.Print "Optional sheet was not found: " & Err.Description
        Err.Clear
    End If

    ' Restore normal error behavior for later statements.
    On Error GoTo 0
End Sub

This example uses Err.Clear after recording the issue so stale details do not get mistaken for a later error. Clearing is not strictly necessary if the procedure exits immediately, but it is useful when code will continue and explicitly check Err again. A more robust design can avoid treating every error as “sheet not found”: if other failures are possible, handle or report them separately rather than assuming the lookup was the only source.

When On Error GoTo -1 is useful

On Error GoTo -1 is for resetting the current procedure’s error state, not for returning execution to the line after the error. It is mainly relevant when code is already handling an error and needs to clear that state before setting up error handling again. Because it disables the enabled handler as part of the reset, re-enable the intended handler explicitly if more protected work follows.

Do not add On Error GoTo -1 as a routine substitute for On Error GoTo 0. If the goal is simply to stop a handler from catching later errors, On Error GoTo 0 communicates that more directly. If the goal is only to clear Err.Number and related information, use Err.Clear. Before applying On Error GoTo -1, save any diagnostics that need to survive the reset.

Code examples using this statement should be tested in the VBA host where they will run. It is a VBA language feature, not a general-purpose instruction that can be assumed to work in every scripting environment or in Excel for the web.

Common reset mistakes and how to avoid them

Using Err.Clear to disable a handler: It only clears error information. Add On Error GoTo 0 when normal error behavior should resume.
Using On Error GoTo 0 to erase diagnostics: It disables the handler but does not clear the Err object’s values. Capture or clear them separately as needed.
Leaving Resume Next enabled: A later error can be skipped without an obvious stop. Keep its scope short and turn it off as soon as the expected operation is checked.
Clearing Err before saving details: The original error number and description may be lost. Copy them to variables or log them first.
Assuming a reset jumps somewhere: The On Error statements configure error handling; they do not themselves resume at a particular line. Use an explicit Resume, Exit Sub, or other control flow where appropriate.
Assuming the reset is global: Error-handling settings apply to the current procedure. Set up handling explicitly in each procedure that needs it.

Excel version and platform notes

These instructions apply to VBA in the desktop editions of Excel for Microsoft 365 on Windows. To inspect or edit VBA on Windows, open the Visual Basic Editor with Alt+F11. On a Mac, use Excel’s Visual Basic Editor through the available Developer or Tools menus; keyboard shortcuts can differ with the keyboard and macOS settings. VBA macros run in desktop Excel for Windows and Mac, but Excel for the web does not run VBA macros. Opening a macro-enabled workbook in the browser does not make these error-handling statements executable there.

The statements discussed here are VBA language features rather than worksheet formulas, so there is no cell formula or ribbon command that resets them. If the VBA editor appears stuck in break mode, that is a separate debugging state: use the editor’s Reset control to stop the paused macro, then inspect the code and run it again. Stopping a macro is not the same as adding On Error GoTo 0 to a procedure.

Frequently Asked Questions

Does Err.Clear turn off On Error Resume Next?
No. It clears the Err object’s stored information, but Resume Next remains active. Use On Error GoTo 0 to restore normal error behavior in that procedure.

What is the difference between On Error GoTo 0 and On Error GoTo -1?
On Error GoTo 0 disables the procedure’s enabled handler. On Error GoTo -1 resets the current error state and disables the enabled handler. Neither statement jumps to another line; set a handler again explicitly if needed.

How do I clear the error number in VBA?
Use Err.Clear after recording any details you need. It clears the Err object’s current error information, but does not change the procedure’s error-handling mode.

Does On Error GoTo 0 clear Err.Description?
No. It turns off the enabled handler for the current procedure. If you need to clear the error details, use Err.Clear; if you need to retain them, copy them into variables first.

Can I reset VBA error handling for the whole workbook?
There is no single workbook-wide On Error reset. Error handling is set within procedures, so each procedure should establish and end its own handling behavior deliberately.

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.