2.5. Error Handling: On Error Statements

Error handling is a critical part of programming in VBA, as it allows you to anticipate and manage runtime errors (unexpected events or mistakes in the program execution). The On Error statements in VBA are used to trap errors and control how your program responds when an error occurs.

VBA provides several ways to handle errors, but the On Error statement is the main tool used for this purpose.

1. Syntax of On Error Statements

The syntax of the On Error statements can vary depending on how you want to handle the error.

• On Error GoTo label: Directs VBA to jump to a specific line (label) in your code when an error occurs.
• On Error Resume Next: Tells VBA to continue executing the next statement after an error without halting the code.
• On Error GoTo 0: Disables any active error handler and returns to default error handling behavior.

2. On Error GoTo label

The On Error GoTo label statement is used to specify a specific block of code to handle errors when they occur. It helps in redirecting the program's flow to a predefined label when an error is encountered.

Syntax:

On Error GoTo ErrorHandler
' Code to execute

Exit Sub ' Exit to avoid running the error handler code when no error occurs

ErrorHandler:
    ' Error handling code
    MsgBox "An error occurred: " & Err.Description
    ' Handle the error (e.g., clean up resources, log the error, etc.)
    Resume Next ' Continue execution after handling the error

• ErrorHandler: The label where VBA will jump if an error occurs. This label should be placed at the end of your procedure or function.
• Err.Description: A built-in object that returns a description of the most recent error.
• Resume Next: This ensures that the program continues with the next line of code after handling the error.

Example:

Sub DivideNumbers()
    On Error GoTo ErrorHandler
    
    Dim result As Double
    Dim num1 As Double
    Dim num2 As Double
    
    num1 = 10
    num2 = 0  ' This will cause a divide-by-zero error
    
    result = num1 / num2
    
    Exit Sub ' Prevents the error handler from running if no error occurs
    
ErrorHandler:
    MsgBox "Error: " & Err.Description
    Resume Next  ' Continues to the next line after the error handling
End Sub

In this example, the code attempts to divide by zero, which causes an error. The error is handled by displaying a message box with the error description, and execution continues after the error handling block.

3. On Error Resume Next

The On Error Resume Next statement tells VBA to ignore the error and move on to the next line of code. This is useful when you want to skip an error or handle it gracefully without interrupting the program’s execution.

Syntax:

On Error Resume Next
' Code that might cause an error
' If an error occurs, the program will move to the next statement

Example:

Sub IgnoreError()
    On Error Resume Next
    
    Dim result As Double
    result = 10 / 0  ' This will cause a divide-by-zero error, but it will be ignored
    
    MsgBox "Code continues without interruption"
End Sub

In this example, the On Error Resume Next statement ignores the error caused by dividing by zero and allows the program to continue executing the next line.

4. On Error GoTo 0

The On Error GoTo 0 statement is used to disable any active error handling. After this statement is used, VBA will use its default error handling behavior, which is to display an error message and stop execution.

Syntax:

On Error GoTo 0

• This is often used after a custom error handling block has been processed, ensuring that any further errors are handled by default error handling.

Example:

Sub ResetErrorHandler()
    On Error Resume Next  ' Ignore errors temporarily
    ' Code that may produce errors
    
    On Error GoTo 0  ' Reset to default error handling
    ' Code that should use default error handling
End Sub

5. Types of Errors in VBA

VBA errors can generally be divided into two categories:

• Syntax Errors: Errors in the code structure that prevent it from being compiled (e.g., missing parentheses, undeclared variables).
• Runtime Errors: Errors that occur during execution (e.g., divide-by-zero, file not found).
• Logical Errors: Errors that don't stop the program but produce incorrect results (e.g., incorrect calculations).

Error handling focuses mainly on runtime errors, as syntax errors are caught during compilation, and logical errors require careful review and testing.

6. Handling Specific Errors with Err Object

VBA's built-in Err object can be used to handle specific errors by referencing its properties, such as:

• Err.Number: The error number of the most recent error.
• Err.Description: A description of the most recent error.
• Err.Source: The name of the object or procedure where the error occurred.
• Err.Clear: Clears the current error.

Example:

Sub HandleSpecificError()
    On Error GoTo ErrorHandler
    
    Dim result As Double
    result = 10 / 0  ' Will cause a divide-by-zero error
    
ExitSub:
    Exit Sub
    
ErrorHandler:
    If Err.Number = 11 Then
        MsgBox "Division by zero error: " & Err.Description
    Else
        MsgBox "An unexpected error occurred: " & Err.Description
    End If
    Resume ExitSub
End Sub

In this example, the error handler checks if the error number is 11 (which corresponds to a divide-by-zero error) and handles it accordingly.

7. Conclusion

Error handling is a vital aspect of robust VBA programming. Using the On Error statements allows you to:

• Control the flow of your program when errors occur.
• Provide meaningful feedback to users about errors.
• Prevent the program from crashing unexpectedly. By using On Error GoTo, On Error Resume Next, and On Error GoTo 0, you can ensure your code behaves predictably and handles errors gracefully.