On Error in Excel VBA: How to Handle Runtime Errors

On Error is the VBA statement that controls what a procedure does when a runtime error occurs in Excel. Use On Error GoTo HandlerName for a planned recovery path, and reserve On Error Resume Next for a small, specific operation whose error you immediately check.

What On Error Means in Excel VBA

VBA normally stops a procedure when it encounters a runtime error, such as trying to use a worksheet that does not exist or opening an unavailable file. Excel displays an error message, and the macro may stop before completing its intended work. An On Error statement lets the procedure respond instead: it can show a useful message, clean up resources, try an alternative, or deliberately continue after a known, harmless failure.

Error handling does not make an invalid operation succeed. It determines what VBA does after that operation fails. A good handler makes failure easier to understand and limits its impact; a poor one can conceal a bug and leave a workbook partly changed.

These statements handle runtime errors, not every kind of problem in VBA. A syntax or compile error—such as a misspelled keyword or a missing End If—must be corrected before the code can run. Likewise, an error value produced in a worksheet cell is a spreadsheet result, not automatically an exception handled by an VBA procedure.

The Main On Error Statements

VBA provides three commonly used forms. They affect the current procedure, so set the behavior where the operation that might fail is running.

On Error GoTo HandlerName enables an error handler at the named label in the same procedure. This is the usual choice when the procedure needs to report a failure or perform cleanup.
On Error Resume Next tells VBA to continue with the statement after the one that failed. The code should then inspect Err or otherwise verify the outcome before moving on.
On Error GoTo 0 disables the procedure’s currently enabled error handling. Later errors use VBA’s normal behavior unless another handler is enabled.

A label is a name followed by a colon, such as HandleError:. It is not a separate procedure. The handler code is part of the same procedure, and normal execution should usually leave the main code before reaching the handler label; otherwise, it could fall through into the handler even when there was no error.

How a GoTo Error Handler Works

With On Error GoTo HandleError, VBA transfers control to HandleError: when a runtime error occurs in that procedure. The code at the label can read details from the Err object, explain what failed, and exit. Use an Exit Sub or Exit Function before the label so successful execution does not accidentally run the error-handling code.

For example, the following procedure formats a report sheet. If the named sheet is missing or the formatting operation fails, it displays the error number and description rather than ending with only an unhandled-error dialog. Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.

Public Sub FormatReport()
    Dim ws As Worksheet
    Dim savedNumber As Long
    Dim savedDescription As String

    On Error GoTo HandleError

    Set ws = ThisWorkbook.Worksheets("Report")
    ws.Range("A1:D1").Font.Bold = True
    Exit Sub

HandleError:
    ' Preserve the error details before doing anything else.
    savedNumber = Err.Number
    savedDescription = Err.Description
    MsgBox "Could not format the report." & vbCrLf & _
           "Error " & CStr(savedNumber) & ": " & savedDescription, _
           vbExclamation
End Sub

In this example, ThisWorkbook means the workbook containing the VBA code. That is often safer than ActiveWorkbook, which refers to whichever workbook is active when the macro runs. The sample reports the problem but does not try to repair it; a real procedure might also close a file it opened or restore a setting it changed.

Using Resume Next Safely

On Error Resume Next is useful when a particular failure is expected and the next line can determine whether it happened. It is risky when applied to a whole procedure: VBA may skip a failed operation and continue with missing data or an unset object, making the eventual symptom confusing.

For instance, a macro may look for an optional worksheet named Archive. The following example limits Resume Next to the worksheet lookup, captures the error number immediately, and turns the mode off before deciding what to do. Error number 9 is the usual VBA subscript-out-of-range error produced when that worksheet name is absent from the collection.

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

    ' Save a copy before running a macro that changes a workbook.
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Archive")
    lookupError = Err.Number
    On Error GoTo 0

    If lookupError = 9 Then
        MsgBox "The optional Archive sheet is not present."
    ElseIf lookupError <> 0 Then
        Err.Raise lookupError
    Else
        MsgBox "The Archive sheet is available."
    End If
End Sub

This pattern is not a reason to suppress every lookup error. If the missing sheet is required, the better response may be to stop with a clear message. Also, check Err.Number immediately after the operation you are testing: a later operation can replace the details you meant to inspect. Use Err.Clear when you intentionally reuse Resume Next and need to clear a previous error before another check.

The Err Object and Error Details

When a runtime error occurs, VBA makes information about it available through the Err object. Its most commonly used members are Err.Number, a numeric code, and Err.Description, a text explanation. Err.Source can identify the source of the error, while Err.Clear resets the stored error information.

Capture useful details at the start of a handler, especially before calling other code that might itself fail or change the error state. Then provide context in the message. “Could not format the Report sheet” is more helpful to a workbook user than a bare error number, while including the number and description can help someone diagnose the cause.

VBA also supports the Erl function, which returns the line number associated with an error when the code has line numbers. Most modern VBA procedures are not numbered, so Erl often returns 0. It can help in deliberately numbered code, but it does not automatically report the source line for ordinary unnumbered procedures.

Common Errors and Handler Mistakes

One frequent error is using On Error Resume Next as a blanket fix. It can make a macro appear to finish even though important statements failed. Restrict it to the smallest possible block, inspect the result, and restore normal handling with On Error GoTo 0.

Another mistake is forgetting to exit before the handler label. If normal execution reaches the label, the handler may display a misleading error message with no meaningful error to report. A related problem is writing code inside the handler that can fail without a plan; a handler is not automatically protected from further errors.

Do not show a generic success message after a failed step unless the procedure has verified that the required work was completed.
Do not assume a handler in one procedure automatically handles errors in every procedure it calls. Handle errors at the appropriate level and pass useful information back when needed.
Do not discard the original error details before recording them. Capture the number and description before cleanup or other operations.
Do not use error handling in place of a straightforward check when a condition can be tested clearly, such as checking a cell value before using it.

Error handling should also reflect partial changes. If a macro edits several ranges and then fails, a message alone does not roll back those edits. Consider validating inputs before making changes, saving important state, and arranging cleanup or recovery where appropriate.

Choosing Between Error Handling and Checks

Use ordinary conditions for expected choices that can be checked directly: whether a cell is blank, whether a value is within a range, or whether a setting is already enabled. Use an error handler for operations that can still fail for reasons outside a simple test, such as opening a file that may be unavailable or interacting with an external application.

For example, a macro can test whether a required input cell contains a value before calculating with it. That is clearer than deliberately causing a type mismatch and catching it. By contrast, a file might disappear between checking its path and opening it, so the open operation still needs a handler even if the path was checked first.

A useful rule is to treat errors as exceptional outcomes, not as the normal branching mechanism. If an error is expected and narrowly defined, handle it locally and verify the specific outcome. If the error means the procedure cannot safely continue, report it and stop rather than silently proceeding.

Testing and Running VBA Safely

Test error paths as deliberately as successful paths. For the report example, try it once with a sheet named Report and once without that sheet. Confirm that the message is understandable and that the rest of the workbook remains in a sensible state. Test with a copy of important workbooks, particularly if the macro changes or deletes data, because Ctrl+Z does not reverse VBA macro actions.

On Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On a Mac, Option+F11 is commonly used; depending on the keyboard, you may also need Fn. The editor’s exact menus and shortcuts can differ by platform, but the VBA error-handling statements themselves are broadly the same. Excel for the web cannot run VBA macros; open the workbook in the desktop Excel app to use them.

Frequently Asked Questions

What does On Error Resume Next do in Excel VBA?
It tells VBA to continue at the next statement after a runtime error. Because it can conceal failures, use it only around a specific operation and check Err.Number or the result immediately afterward.

What is the difference between On Error GoTo 0 and On Error GoTo -1?
On Error GoTo 0 disables the active error handler in the current procedure. On Error GoTo -1 clears the current exception state in VBA, allowing a subsequent error to be handled again by an enabled handler. They are not interchangeable; for routine code, GoTo 0 is the common way to turn off Resume Next or a handler.

Can On Error handle a syntax error?
No. A syntax or compile error prevents the code from running correctly in the first place. Fix the code in the Visual Basic Editor; On Error is for runtime errors raised while a procedure executes.

Why does my macro continue after an error?
It may have an active On Error Resume Next statement, or it may have jumped to a handler that does not stop execution as intended. Check where the error mode is set, whether it is reset, and whether the handler exits the procedure.

Does an error handler work across multiple VBA procedures?
An error handler belongs to its procedure. A called procedure needs its own handling or must allow the error to propagate to a caller that can handle it. A handler in one macro does not automatically protect every separate macro in the workbook.

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.