VBA Error Handling in Loops: Safe Patterns That Work

In VBA, use an error handler to decide what a loop should do when one item fails: log the problem and continue, or stop and report it. For Excel macros, a reliable pattern is to handle each item, save the error details, and use Resume to return to a clear point before the next iteration.

What error handling means in a loop

A loop repeats an operation across a collection of cells, rows, files, or other objects. If one operation raises a run-time error, VBA normally stops the procedure and displays an error message. An error handler changes that response: it can record the issue, skip the faulty item, try a fallback, or end the procedure in a controlled way.

VBA error handling is set up at the procedure level, not inside a separate block for each iteration. An instruction such as On Error GoTo ItemError makes a named handler available in the current procedure. When an error occurs, execution jumps to that label. The handler must then use a Resume statement to leave the active error-handling state and continue at an appropriate point.

The key design question is not simply how to suppress an error. It is whether the macro can safely continue after that particular failure. A malformed value in one row may be independent of other rows; a missing worksheet or unavailable output range may make the whole task unreliable. Those situations deserve different responses.

A per-item handler that skips a bad value

This example tries to convert the values in column A of a worksheet named Data into dates in the adjacent column. If a conversion or write fails, it prints the cell address and error details in the Immediate window, then carries on. Open the Visual Basic Editor and press Ctrl+G on Windows to show that window; on Mac, use the Visual Basic Editor’s View menu to display it.

Option Explicit

Sub ConvertDates()
    Dim ws As Worksheet
    Dim cell As Range
    Dim errorNumber As Long
    Dim errorText As String
    Dim failedAddress As String

    Set ws = ThisWorkbook.Worksheets("Data")

    For Each cell In ws.Range("A2:A100").Cells
        On Error GoTo ItemError
        cell.Offset(0, 1).Value = CDate(cell.Value)

NextItem:
        On Error GoTo 0
    Next cell

    Exit Sub

ItemError:
    errorNumber = Err.Number
    errorText = Err.Description
    failedAddress = cell.Address(False, False)
    Debug.Print "Skipped " & failedAddress & ": " & _
                errorNumber & " - " & errorText
    Resume NextItem
End Sub

The label NextItem is deliberately placed after the attempted conversion. If an error occurs, the handler saves the error information before doing anything else, then Resume NextItem returns control there. The code turns the handler off with On Error GoTo 0 before the loop advances. On the next pass, it enables the handler again for that item.

Using Resume NextItem is more explicit than relying on Resume Next. The latter resumes at the statement after the one that failed, which may not be the right place if the failed operation left the current item incomplete. Also note that CDate can interpret some values, including a blank, as a date rather than reject them. Validate the kinds of inputs your workbook permits instead of assuming every unwanted value will trigger an error.

How On Error, Err, and Resume work

On Error GoTo label routes a run-time error to a label in the same procedure. The handler is considered active while its code is running. If the handler itself encounters another unhandled error, VBA does not simply restart that same handler; the new failure can interrupt the procedure. Keep handlers short and avoid complicated work inside them.

The Err object provides information about the most recent error, including Err.Number and Err.Description. Capture the values you need promptly. A later operation may change the error information, so do not wait until after logging or cleanup to read it. Err.Clear clears the stored error details, but it does not turn off the active error-handling instruction.

Resume retries the statement that caused the error.
Resume Next continues at the statement immediately following the one that failed.
Resume label continues at a named line in the procedure.
On Error GoTo 0 disables the procedure’s current enabled handler.

Choose the resume destination deliberately. Retrying a failed conversion without changing its input will usually fail again. Continuing after an incomplete operation can leave an output cell blank or inconsistent. In the example, the macro explicitly treats the failed row as skipped and continues at the loop’s next-item checkpoint.

When On Error Resume Next is appropriate

On Error Resume Next tells VBA to continue with the next statement after an error rather than jumping to a label. It can be useful when one specific operation is expected to fail sometimes and the code checks immediately whether it did. It is risky across a whole loop or procedure because it can make unrelated failures look like success.

For example, a short, checked conversion can use this pattern. The cell variable and result variable should be declared in the procedure, and the error state should be inspected before moving on:

Dim convertedValue As Double
Dim conversionError As Long

Err.Clear
On Error Resume Next
convertedValue = CDbl(cell.Value)
conversionError = Err.Number
On Error GoTo 0

If conversionError <> 0 Then
    Debug.Print "Could not convert " & cell.Address(False, False)
Else
    cell.Offset(0, 1).Value = convertedValue
End If

This pattern limits the period in which errors are ignored and checks the result before using it. In production code, you may also save Err.Description before turning off the handler if you need a useful log entry. Remember that On Error Resume Next is not a block-scoped setting: once enabled, it remains in effect in that procedure until changed or the procedure ends. An error in a called procedure also needs appropriate handling there; the caller’s setting should not be treated as a substitute for a handler in every procedure.

Log failures and decide whether to continue

Debug.Print is handy while developing, but its output is temporary and may not be suitable for a macro used by other people. For a repeatable process, write failures to a dedicated worksheet, a text file, or another approved log location. Record enough context to find the cause, not just a generic message.

Identify the item, such as a worksheet name, row number, cell address, or file name.
Record the error number and description before the handler continues.
State the action taken, such as skipped, retried, or stopped.
Keep a total of successful and failed items so the final result is visible.

Logging itself can fail—for example, a log worksheet might be protected or a file path might be unavailable. Avoid letting a fragile logging step create a second unhandled error inside the handler. A simple fallback, such as retaining a count and displaying a final summary, may be safer than elaborate logging. If the work must be auditable, design and test the log destination as part of the process.

Common mistakes that hide or misroute errors

Leaving Resume Next enabled: A later statement can fail silently, making the result appear complete when it is not. Restrict it to the one operation you intend to check, then restore normal handling with On Error GoTo 0.
Forgetting to resume: A handler that does not use Resume, Resume Next, or Resume label does not provide a clean route back into normal execution.
Resuming at the wrong statement: Continuing halfway through an item can skip required work or use incomplete data. Place a clear checkpoint for the next iteration.
Overwriting Err details: Save the number and description before calling other code or performing additional operations.
Treating every error as recoverable: A bad input row might be skippable; a missing worksheet or failed setup may mean the macro cannot produce a trustworthy result.
Assuming a handler covers every procedure: Error handling is not automatically inherited as a safe, universal handler for every called routine. Give procedures that can fail their own appropriate handling or let errors propagate to a deliberate caller handler.

One misconception is that adding an error handler makes a macro robust by itself. It only changes what happens after an error. The code still needs validation, clear recovery rules, and a way to tell the user whether the output is complete.

Choosing between skipping, retrying, and stopping

Use a per-item handler when failures are isolated and the remaining items can still produce useful results. Examples include importing records where a small number of rows have invalid dates, or checking several independent files where one file may be missing. Report the skipped items so that users can correct them and rerun the process.

Stop the procedure when continuing could corrupt data, misstate a result, or create output that looks valid but is incomplete. For instance, if a macro cannot open the required source workbook, processing later steps against old or empty data is usually worse than stopping with a clear message. Retrying is useful only when there is a plausible way the cause can change; repeating a deterministic conversion error without changing the input is not recovery.

A useful rule is to separate setup errors from item errors. Establish that required sheets, ranges, and files exist before entering the loop. Handle expected variations within the loop, and keep a higher-level error path for unexpected failures. This makes it easier to explain which records were skipped and whether the overall task finished.

Excel and macro safety considerations

The VBA language patterns described here apply to Excel for Microsoft 365 on Windows and Excel for Microsoft 365 on Mac, though the editor’s menus and some platform-specific features can differ. Excel for the web does not run VBA macros; use the desktop Excel application to execute this code. If a workbook’s values or formulas matter, save a separate copy before running a macro. VBA changes cannot be undone with Ctrl+Z, and a handler does not reverse changes made before an error.

Test with a small range that includes both valid and invalid inputs. Check the output cells, the error log, and the final success or failure count. Also consider whether rerunning the macro overwrites existing results or duplicates records. Those safeguards are part of reliable error handling: a macro should make its outcome understandable and its recovery path predictable.

Frequently Asked Questions

Can I use On Error GoTo inside a VBA loop?
Yes. It applies to the current procedure, so set it before the operation that may fail and use a handler that resumes at a deliberate point. Re-enable or reset handling as appropriate for each iteration.

How do I skip an iteration when an error occurs?
Send the error to a handler, capture the details, and use Resume to jump to a label positioned at the next-item checkpoint. Make sure the failed item cannot leave partial output that looks successful.

Does On Error Resume Next skip the rest of the loop?
No. It continues at the statement after the one that failed, then proceeds normally. That may mean later statements in the same iteration still run, which is why you should check Err.Number immediately and keep the setting narrowly scoped.

What is the difference between Err.Clear and On Error GoTo 0?
Err.Clear clears the stored error information. On Error GoTo 0 disables the current enabled error handler. Clearing Err does not by itself turn off error handling.

Can I use VBA error handling in Excel for the web?
No. Excel for the web can open workbooks containing macros, but it does not run VBA. Open the workbook in desktop Excel to run a VBA procedure.

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.