[VBA] How to Use Err.Clear: An Essential Technique for Error Handling in Loops

Introduction

When writing code in VBA that uses On Error Resume Next to ignore errors, you must be especially careful when using it inside a loop. The VBA Err object retains error information until a new error occurs or the program ends.

This can cause a serious issue: an error that occurred in the first iteration of a loop might be falsely detected as an “error” in the second iteration, even if the second iteration was successful.

The solution to this problem is the Err.Clear method, which resets the error state. In this article, I will explain the importance of Err.Clear and how to use it correctly within loops.

Sample Code Using Err.Clear

This macro performs a division operation (10 / Cell Value) for each cell in the range A1:A5 and writes the result in column B. If column A contains a 0 or text string, causing an error, it records an error message in column B.

Complete Code

' Check for errors inside the loop and reset with Err.Clear
Sub ClearErrObjectInLoop()

    Dim targetCell As Range
    
    ' Ignore errors from this point on and proceed to the next line
    On Error Resume Next
    
    ' Loop from cell A1 to A5
    For Each targetCell In ActiveSheet.Range("A1:A5")
        ' Operation that might cause an error
        targetCell.Offset(0, 1).Value = 10 / targetCell.Value
        
        '--- Check Err.Number immediately after ---
        If Err.Number <> 0 Then
            ' If an error occurred
            targetCell.Offset(0, 1).Value = "Error: " & Err.Description
            
            '--- Clear the Err object to prepare for the next loop ---
            ' Without this, the If statement would trigger in the next loop 
            ' even if there is no error because the old error remains.
            Err.Clear
        End If
    Next targetCell
    
    ' Return error handling to normal
    On Error GoTo 0

End Sub

Preparation Before Execution

Enter the values 5, 0, 2, "abc", and 4 into cells A1 through A5 respectively. When you run the macro, error messages will appear in B2 and B4, while correct calculation results will appear in the other cells.

Code Explanation

The Importance of Err.Clear

Err.Clear

This single line is the key to safely using On Error Resume Next inside a loop.

The Err object retains its value once Err.Number becomes non-zero. If you do not use Err.Clear, the “Division by zero” error that occurred at cell A2 (value 0) will remain in the Err object.

Consequently, even though the calculation for the next cell, A3 (value 2), is successful, the check If Err.Number <> 0 Then will return True because the error information from A2 is still there.

By executing Err.Clear inside the error handling block, you reset the Err object to “No Error” (.Number = 0). This ensures that the next iteration of the loop starts with a clean slate.

Summary

In this article, I explained how to use Err.Clear to reset error information and correctly implement error handling within loops.

• The Err object keeps holding error information until it is cleared.
• When using On Error Resume Next in a loop, it is standard practice to execute Err.Clear after detecting and handling an error.
• Neglecting this can lead to detecting past errors, causing unexpected bugs.

Err.Clear is an indispensable method for performing precise and robust error handling when using On Error Resume Next.