VBA Error Handling for Multiple Errors in One Macro

VBA handles runtime errors one at a time: use On Error GoTo to route an unexpected failure to a handler, or use a narrowly scoped On Error Resume Next when you need to check an operation and continue. To deal with several possible failures, capture each error’s details promptly, decide whether to stop or continue, and restore any changed application state.

What “multiple errors” means in VBA

The phrase can describe two different situations. A macro may encounter different kinds of errors at different points, such as a missing worksheet followed by an invalid value. Or it may process many records and need to report every bad record instead of stopping at the first one. VBA’s built-in error handling helps with both, but it does not automatically collect a list of errors.

A procedure can have one enabled error-handling strategy at a time. A handler receives control when a runtime error occurs; it can inspect the built-in Err object, clean up, and then exit, resume, or pass the failure onward. If another error occurs while that handler is already active, the same handler cannot catch it. That limitation is why a handler should be short and why cleanup operations sometimes need their own limited protection.

How On Error and the Err object work

On Error GoTo HandlerName enables a procedure’s named handler. When an eligible runtime error occurs, VBA transfers execution to that label. The handler can read Err.Number, Err.Description, and Err.Source to learn what failed. Capture those values before doing other work: a later operation can change the current error information.

On Error Resume Next tells VBA to continue with the statement following the one that failed. It is not a general fix or a way to make a failed operation succeed. Check Err.Number immediately after the risky statement, record what matters, and then turn off this behavior with On Error GoTo 0 when the guarded section ends. Otherwise, a later error may be silently skipped too.

After a handler has dealt with an error, Resume, Resume Next, or Exit Sub can leave the handler appropriately. A handler that falls through into ordinary procedure code can accidentally run that code as part of error handling. Use an explicit exit before the handler label in normal execution.

Use a central handler when the macro should stop

For a task that cannot safely continue after a failure, a single handler is usually clearest. This example saves and restores Excel’s screen-updating setting, captures the original error, and raises it again after cleanup. It includes a placeholder for the work your macro performs.

Public Sub ProcessReport()
    Dim oldScreenUpdating As Boolean
    Dim savedNumber As Long
    Dim savedSource As String
    Dim savedDescription As String

    ' Save a copy of the workbook before running a macro.
    oldScreenUpdating = Application.ScreenUpdating
    On Error GoTo Failed

    Application.ScreenUpdating = False
    ' Perform the report work here.

CleanExit:
    Application.ScreenUpdating = oldScreenUpdating
    Exit Sub

Failed:
    savedNumber = Err.Number
    savedSource = Err.Source
    savedDescription = Err.Description

    ' Protect cleanup if restoring the setting also fails.
    On Error Resume Next
    Application.ScreenUpdating = oldScreenUpdating
    On Error GoTo 0

    ' Pass the original failure to the caller.
    Err.Raise savedNumber, savedSource, savedDescription
End Sub

The normal route reaches CleanExit; the error route saves the original details before attempting cleanup. Re-raising lets a calling procedure handle the problem or lets VBA show the error if no caller handles it. The short Resume Next region is deliberately limited to cleanup, where a secondary failure should not hide the first one. In a real macro, add cleanup for resources you opened, such as files or object references, when applicable.

Continue through many items and collect failures

If a batch should check every row, handle the risky conversion for each row and store its failure before moving on. This example expects numbers in Data!A2:A20; it prints a message for values that cannot be converted to a number. The procedure can continue checking later cells rather than stopping on the first conversion error.

Public Sub CheckNumericCells()
    Dim target As Range
    Dim numericValue As Double
    Dim savedNumber As Long
    Dim savedDescription As String
    Dim issues As Collection
    Dim issue As Variant

    ' Save a copy of the workbook before running a macro.
    Set issues = New Collection

    For Each target In ThisWorkbook.Worksheets("Data").Range("A2:A20").Cells
        On Error Resume Next
        Err.Clear
        numericValue = CDbl(target.Value2)
        savedNumber = Err.Number
        savedDescription = Err.Description
        On Error GoTo 0

        If savedNumber <> 0 Then
            issues.Add target.Address(False, False) & ": " & savedDescription
        End If
    Next target

    For Each issue In issues
        Debug.Print issue
    Next issue
End Sub

The conversion is the operation that may fail, so the error check follows it immediately. The code saves the number and description before continuing, and then disables Resume Next. It stores readable messages in a Collection, which can be reviewed in the Visual Basic Editor’s Immediate window. The savedNumber variable is reset by reading Err.Number after each attempt; Err.Clear also clears any prior error before the next conversion.

This sample demonstrates a policy, not a universal validation rule. For example, whether a blank cell should count as an error depends on the task. You can add a separate blank check before conversion, or store the cell address and error number in a more structured report. Also note that CDbl follows regional conventions for numeric text. If inputs may contain currency symbols, dates, or locale-specific separators, define and validate the accepted format rather than assuming every value should convert.

Choose whether to stop, skip, or report

There is no single correct response to every error. Decide what the macro should do before writing the handler. A failure to find an optional worksheet may be recoverable, while a failure to save an important output may require the process to stop. Common policies include:

Stop immediately: use a central handler when later steps rely on the failed operation.
Skip the current item: appropriate for batch work when one invalid row should not block valid rows; record which item was skipped.
Collect and summarize: continue through independent checks, then show or save a report of all failures.
Retry: useful only when the failure may be temporary and the operation is safe to repeat. Set a limit so a macro cannot loop forever.

Do not treat every error as harmless just to finish a run. If a failed step leaves data incomplete, later output can look successful while being wrong. Make the policy visible in the report: distinguish completed work, skipped records, and steps that prevented completion.

Common mistakes and ways to avoid them

Leaving Resume Next active: follow a small guarded section with On Error GoTo 0. Avoid wrapping an entire long procedure in it.
Checking Err too late: copy the number and description immediately after the operation that might fail, before calling another procedure or attempting cleanup.
Using a broad handler to hide bugs: a handler that reports “something went wrong” and continues can conceal programming mistakes. Include context such as the record or worksheet involved.
Assuming a handler catches errors inside itself: the active handler cannot handle a new error in the same procedure. Protect cleanup narrowly or move complex cleanup into a separate routine with its own handling.
Confusing compile errors with runtime errors: misspelled variables, invalid syntax, and other compile-time problems are not caught by On Error. Fix them in the editor before running the macro.

When you deliberately use On Error Resume Next to test whether something exists, such as a workbook object or optional worksheet, keep that test local. Check the result, clear or disable the handler, and then proceed through normal code. This makes it much easier to tell an expected absence from an unrelated failure.

Logging, cleanup, and passing errors onward

A useful error report identifies both the failure and its context. In a batch process, include a row number, key value, filename, or task name alongside Err.Number and Err.Description. A message alone may not tell someone which record needs correction. For repeated runs, write details to a worksheet or text log rather than relying only on a message box or the Immediate window.

Keep the original error separate from errors that happen during cleanup. Save its number, source, and description first, as the earlier example does. If cleanup itself needs to be best-effort, protect only those cleanup statements with On Error Resume Next; then restore ordinary handling. Do not overwrite a meaningful original failure with a less useful cleanup error unless the cleanup failure is the one that matters most to the task.

Use Err.Raise when a procedure cannot resolve the problem and its caller should decide what to do. When creating a new application-specific error, choose a documented number and provide a useful source and description; do not claim a failed operation succeeded. Conversely, if the procedure has fully handled an expected condition, it can record the result and return normally.

Testing and Excel considerations

Test both the normal route and realistic failure routes: an empty cell, invalid text, a missing sheet, a read-only destination, or a file that is unavailable. Confirm that the macro records the right item, does not silently skip later failures, and restores any Excel settings it changed. A deliberate test should be done on a copy, because macros can change or delete workbook data and cannot be undone with Ctrl+Z.

On Excel for Microsoft 365 for Windows, VBA code runs from the Visual Basic Editor and can be stored in a macro-enabled .xlsm workbook. Excel for Mac also supports VBA, though Excel’s object model and some automation features differ between platforms; error-handling statements such as On Error and the Err object are VBA language features. Excel for the web cannot create, run, or edit VBA macros, so use the desktop app for this code.

Frequently Asked Questions

Can VBA handle more than one error in a procedure? Yes, a procedure can encounter and respond to errors at different points, but it has one enabled handler at a time. Use a central handler for failures that should stop the procedure, or carefully scoped checks when the procedure should continue.

Can one VBA error handler catch every error? No. It can handle runtime errors that occur while it is enabled, but it cannot catch compile-time errors, and an active handler cannot catch a new error raised while that handler is executing. Unexpected failures in called procedures may also need handling at the appropriate level.

How do I show all errors instead of only the first one? Process independent items in a loop, capture each item’s error immediately, and add its context and description to a collection or report. Continue only when it is safe to do so, then present the accumulated issues after the loop.

What is the difference between On Error GoTo 0 and Err.Clear? On Error GoTo 0 disables the current procedure’s enabled error handler. Err.Clear clears the current error information in the Err object. They serve different purposes; clearing the object does not replace turning off a broad Resume Next region.

Can I use On Error Resume Next for an entire macro? You can, but it is usually unsafe because many failures may be ignored and later results may be misleading. Restrict it to a small operation you intend to inspect, check Err immediately, and restore normal handling right away.

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.