Visual Basic for Applications:
Handling errors

How to:

In VBA, error handling is typically implemented using the On Error statement which instructs VBA how to proceed when an error occurs. The most common error handling strategies involve the On Error GoTo label, On Error Resume Next, and On Error GoTo 0.

Example 1: Using On Error GoTo

This approach allows you to direct the program to a specific section of code, labeled immediately after encountering an error.

Sub ErrorHandlerExample()
    On Error GoTo ErrHandler
    Dim intDivision As Integer

    intDivision = 5 / 0 ' This will cause a divide by zero error

    Exit Sub
ErrHandler:
    MsgBox "An Error Occurred: " & Err.Description, vbCritical, "Error!"
    Resume Next
End Sub

In this example, any runtime error will trigger the jump to ErrHandler, displaying an error message and then proceeding with the next line after the error.

Example 2: Using On Error Resume Next

This strategy instructs VBA to continue executing the next line of code even if an error occurs, which can be useful for errors expected to be harmless or when you plan to handle the error later in the execution.

Sub ResumeNextExample()
    On Error Resume Next
    Dim intDivision As Integer
    intDivision = 5 / 0 ' This will not cause the program to stop; error is ignored
    
    ' Check if error occurred
    If Err.Number <> 0 Then
        MsgBox "An Error Occurred: " & Err.Description, vbExclamation, "Handled Error"
        ' Reset error
        Err.Clear
    End If
End Sub

In this case, the program doesn’t break on error; it checks if an error occurred, handles it if it did, and then clears the error.

Deep Dive

Historically, error handling in programming languages has evolved from simple goto statements to more sophisticated mechanisms like exceptions in languages such as Java and C#. VBA’s error handling, while not as powerful or flexible as modern exception handling, serves its purpose within the context of the language’s application in automating tasks in Microsoft Office environments.

The primary limitation of VBA’s error handling lies in its somewhat cumbersome and manual approach, requiring careful placement of error handling code and clear understanding of the flow of execution. Modern programming languages typically offer more elegant solutions, such as try-catch blocks, that automatically handle the flow to error handling code without the need for manual checks or jumps in code execution.

Despite these limitations, VBA’s error handling mechanisms are suitable for most automation tasks and when used properly, can significantly reduce the likelihood of unhandled errors causing problems for users. Additionally, understanding VBA’s error handling can provide insights into older programming paradigms and the evolution of error handling strategies in software development.