[VBA] How to Identify Error Types and Branch Processing (Err Object)

Introduction

In VBA error handling, On Error GoTo jumps to a specific label regardless of the error, which can make it difficult to implement fine-grained responses based on the error type. On the other hand, On Error Resume Next simply ignores errors, making it hard to notice that an error occurred at all.

However, by combining On Error Resume Next with the built-in Err object, it is possible to achieve advanced error handling: “identifying the type of error generated and branching processing according to that error number.”

This article explains the main properties of the Err object and how to use them to handle specific errors.

Sample Code for Error Processing Using the Err Object

This macro performs a division of the value in cell C2 by the value in cell D2. It uses On Error Resume Next to temporarily ignore errors, and immediately checks Err.Number.

If the error is “Division by zero (Error 11),” a specific message is displayed. If it is another error, a generic message is displayed.

Complete Code

' Handle specific errors using the Err object
Sub HandleSpecificError()

    Dim result As Double
    
    ' Ignore errors from this point and proceed to the next line
    On Error Resume Next
    
    '--- Process where an error might occur ---
    result = Range("C2").Value / Range("D2").Value
    
    '--- Check Err.Number immediately after to identify the error type ---
    Select Case Err.Number
        Case 0
            ' No error (Err.Number 0 means normal completion)
            Range("E2").Value = result
            MsgBox "Calculation completed successfully.", vbInformation
            
        Case 11
            ' Error Number 11: "Division by zero" error
            MsgBox "Calculation Error: The divisor is 0.", vbCritical, "Division by Zero"
            
        Case 13
            ' Error Number 13: "Type mismatch" error (e.g., trying to divide by text)
            MsgBox "Calculation Error: Cells contain non-numeric values.", vbCritical, "Type Mismatch"
            
        Case Else
            ' Unexpected errors other than the above
            MsgBox "An unexpected error occurred." & vbCrLf & _
                   "Error Number: " & Err.Number & vbCrLf & _
                   "Description: " & Err.Description, vbCritical, "Unknown Error"
    End Select
    
    '--- Clear the Err object to reset the error state ---
    Err.Clear
    
    '--- Return error handling to normal state ---
    On Error GoTo 0

End Sub

Code Explanation

What is the Err Object?

The Err object is a global object shared throughout VBA that holds information about runtime errors that have occurred. The main properties are:

• .Number: Returns the error number (integer) to uniquely identify the error. If no error has occurred, this is 0.
• .Description: Returns a string describing the error content (e.g., “Division by zero”).
• .Clear: Manually resets the Err object information and returns .Number to 0.

Combination with On Error Resume Next

Using On Error Resume Next allows the program to proceed to the next line without stopping even if an error occurs. However, the information that an error occurred is recorded in the Err object.

Therefore, by checking Err.Number using If Err.Number <> 0 Then or Select Case Err.Number immediately after the process that might cause an error, the code can determine “Did an error occur?” and “If so, what kind of error was it?” to take appropriate action.

Importance of Err.Clear

Once the Err object holds error information, it retains that information until another error occurs or Err.Clear is explicitly called.

If Err.Clear is not executed after error processing is finished, checking Err.Number in a subsequent process might incorrectly detect past error information. It is a safe practice to call Err.Clear to reset the error state once the error determination and processing are complete.

Summary

This article explained advanced error handling techniques using the Err object to identify the type of error and branch processing.

1. Use On Error Resume Next to continue processing after an error.
2. Check Err.Number immediately to identify the error number.
3. Use Select Case or If to write processing logic according to the error number.
4. Reset error information with Err.Clear when finished.

Mastering this technique allows for the creation of robust programs that provide accurate feedback based on the cause of the error, rather than simply ignoring errors or performing uniform error handling.