VBA Macro Error Handling: Patterns, Examples, and Fixes

VBA macro error handling lets a procedure respond to runtime problems instead of stopping without explanation. Use On Error GoTo Label for a clear error-handling routine, reserve On Error Resume Next for short, expected checks, and always restore any Excel settings your macro changes.

What VBA error handling covers

VBA errors are not all the same. A syntax error, such as a misspelled keyword, is found while editing or compiling the code; an error handler does not repair it. A runtime error occurs while the macro runs—for example, when code refers to a worksheet that does not exist, attempts an invalid operation, or encounters a problem opening a file. Error handling is designed primarily for these runtime failures.

In Excel for Microsoft 365 on Windows, you can open the Visual Basic Editor with Alt+F11, then use Debug > Compile VBAProject to catch many compile-time problems. On a Mac, Option+F11 opens the editor on many keyboards; you may need to press Fn as well, depending on the keyboard. Excel for the web does not run VBA macros, so use desktop Excel to edit and run them.

An error handler cannot make an unsafe operation safe by itself. It gives you a controlled place to record what happened, clean up, notify the user, or stop the procedure. The right response depends on whether the error is expected and recoverable or signals that the macro cannot complete its task correctly.

How On Error statements work

VBA provides three commonly used forms of the On Error statement. Each affects error handling in the current procedure:

On Error GoTo ErrorHandler enables a handler at the named label. When a runtime error occurs, execution jumps there.
On Error Resume Next tells VBA to continue with the statement immediately after the one that caused an error. The macro must check whether an error occurred before relying on the result.
On Error GoTo 0 disables the current procedure’s enabled error handler. It does not jump to a label named zero.

When an error occurs, VBA exposes information through the built-in Err object. Its Number identifies the error, and Description provides a message. Source can identify the object or application that raised it. Capture useful details promptly: later operations may change the current error information. Err.Clear clears the stored error details, which is useful after deliberately checking an expected failure.

A handler is local to its procedure. A procedure that calls another procedure should not assume its handler will catch every error inside the called code: the called procedure can handle its own error, or an unhandled error can propagate to the caller. Also, while a handler is actively running, it cannot handle another error in that same procedure. Keep handler code short and make cleanup operations as safe as possible.

Choosing a handling pattern

For most macros that need a clear failure message or cleanup, start with On Error GoTo. It makes the normal path and failure path visible, and it avoids silently treating a failed operation as successful. A common structure is to enable the handler near the start, do the work, pass through a cleanup section on success, and put the error label near the end.

Use On Error Resume Next only when you expect a particular statement might fail and you have a specific way to check the outcome. Keep that window narrow: turn it on immediately before the risky statement, inspect Err or the result, then turn it off with On Error GoTo 0. Leaving it on for a long procedure can hide unrelated faults, such as a failed worksheet lookup followed by code operating on an unset object.

Do not add an error handler that simply ignores all errors. A macro that continues after a failed calculation, data import, or save may produce an incomplete workbook that looks finished. If continuing is genuinely safe, state what condition is being tolerated and verify it explicitly.

Example: report an error and restore Excel settings

This pattern remembers Excel’s current screen-updating setting, turns screen updating off while it works, and attempts to restore the original setting on both success and failure. It also saves the error number and description before cleanup, so the message can explain the original problem. 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 oldScreenUpdating As Boolean
    Dim stateCaptured As Boolean
    Dim savedErrorNumber As Long
    Dim savedErrorDescription As String

    On Error GoTo ErrorHandler

    oldScreenUpdating = Application.ScreenUpdating
    stateCaptured = True
    Application.ScreenUpdating = False

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

CleanExit:
    If stateCaptured Then Application.ScreenUpdating = oldScreenUpdating
    Exit Sub

ErrorHandler:
    savedErrorNumber = Err.Number
    savedErrorDescription = Err.Description

    On Error Resume Next
    If stateCaptured Then Application.ScreenUpdating = oldScreenUpdating
    On Error GoTo 0

    MsgBox "Update failed (" & savedErrorNumber & "): " & _
           savedErrorDescription, vbExclamation
    Exit Sub
End Sub

The stateCaptured flag prevents the handler from restoring a default value if the original setting was never read. The cleanup attempt uses On Error Resume Next because a failure during cleanup should not cause a second unhandled error to interrupt the message. This is a targeted use, not permission to ignore failures throughout the macro. For code that changes other application settings, such as alerts or calculation mode, preserve and restore each original value too.

Example: check an expected missing worksheet

Sometimes a missing worksheet is an expected condition, such as when a macro supports workbooks with optional input tabs. In that case, a short inline check can be clearer than treating the absence as an unexpected crash. Declare the variables at the top of the procedure, as shown:

Public Sub CheckInputSheet()
    Dim ws As Worksheet
    Dim lookupError As Long
    Dim lookupDescription As String

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

    If lookupError <> 0 Then
        MsgBox "The Input sheet could not be found: " & _
               lookupDescription, vbExclamation
        Exit Sub
    End If

    MsgBox "The Input sheet is available."
End Sub

The important detail is that the error is checked immediately and the handler is reset before the rest of the procedure continues. If the sheet is required rather than optional, a normal On Error GoTo handler may be preferable because it can report the failure and stop the operation consistently.

Logging errors and raising meaningful ones

A message box is convenient for a one-off desktop macro, but it is not always enough. A user may dismiss it without recording what happened, and an unattended macro may have no one available to see it. For recurring processes, consider writing a log entry with the procedure name, time, error number, and description. Avoid recording sensitive cell contents or other private data unless there is a clear need and an appropriate place to store it.

When your own code detects invalid input, it can raise a meaningful VBA error rather than allowing a confusing failure later. For example, after checking that a required column heading is absent, code can use:

Err.Raise vbObjectError + 513, "ImportData", _
          "The required header is missing."

Err.Raise supplies an error number, source, and description. The vbObjectError constant is used as the base for application-defined error numbers; choose a consistent range for your project and document what each custom error means. Raise an error only when the procedure cannot safely meet its stated purpose, and let a suitable handler decide whether to report it or pass it along.

Debugging common error-handling problems

A macro that stops without showing your intended message may be running into a problem before the handler is enabled, or the handler may be in a different procedure. Put the On Error GoTo statement before the operations it should protect, and check the called procedures too. A handler label must be in the same procedure as its On Error GoTo statement.

If a macro appears to skip a failed line and then produces wrong results, look for a broad On Error Resume Next. Check every risky statement’s outcome and use On Error GoTo 0 once the special case is dealt with. A missing object assignment, for example, can leave an object variable set to Nothing; later code may then fail for a different reason.

When the handler runs but the message is unhelpful, inspect Err.Number, Err.Description, and, where useful, Err.Source. While debugging, the VBA editor’s break mode and error prompts can help locate the original failing statement. Compile the project to distinguish syntax or declaration problems from runtime errors. Error handling does not replace stepping through code or checking assumptions about workbook names, sheet names, ranges, and file paths.

If an error occurs inside the handler itself, the handler cannot reliably handle that second error while it is active. Keep the handler’s work minimal: capture the original error first, attempt only necessary cleanup, and avoid complicated calculations or additional file operations there. If cleanup must perform risky work, use a small, deliberate inline error-handling window and make clear what failure can safely be ignored.

Safer macro design and Excel-specific limits

Error handling is one part of making a macro safer. Validate inputs before changing data, avoid relying on whichever workbook happens to be active, and qualify references with the intended workbook and worksheet. For example, ThisWorkbook means the workbook containing the VBA code; ActiveWorkbook means the workbook currently active in Excel, which may be different. That distinction can prevent a macro from writing to the wrong file.

Test with a copy of the workbook and include realistic failure cases: a missing sheet, an empty input range, a protected sheet, or an unavailable file. Macros cannot be reversed with Ctrl+Z, so saving a backup before running code is important even when the macro has an error handler. Save workbooks containing VBA in a macro-enabled format such as .xlsm, and only enable macros from sources you trust. Error handling does not make an untrusted macro safe.

Most VBA error-handling statements work similarly in desktop Excel for Windows and Mac, but menus, keyboard behavior, and some object-model features can vary. Macros do not run in Excel for the web. If a workbook must work across platforms, test it in the desktop versions your readers use; do not assume that catching an error fixes a platform-specific feature difference.

Frequently Asked Questions

What is the best error handler for an Excel VBA macro?
For unexpected runtime errors, use On Error GoTo with a handler that records the error, restores changed settings, and reports or stops safely. Use On Error Resume Next only for a narrow, expected check.

What does On Error Resume Next do in VBA?
It continues execution at the statement after the one that failed. It does not automatically fix the problem or tell you that the result is valid, so check Err.Number or the relevant object or value, then reset handling with On Error GoTo 0.

How do I display the VBA error number and message?
Inside the handler, read Err.Number and Err.Description. Store them in variables before performing cleanup, then display or log those saved values.

Can an error handler catch a syntax error?
No. Syntax and many compile-time errors must be corrected in the code before it can run. Use the VBA editor’s compile command and resolve those issues; runtime handlers address errors that occur during execution.

Can I undo a macro with Ctrl+Z after an error?
No. Running VBA generally clears Excel’s ordinary undo history, and Ctrl+Z does not reverse the macro’s changes. Save a separate copy before running code that changes workbook 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.