Lesson 14: Professional Error Handling in Excel VBA 365

14.1 Understanding Error Handling in VBA

Error handling is a critical skill for any Excel VBA developer. When users interact with your macros, they might enter invalid data, reference non-existent ranges, or trigger other unexpected conditions. Professional applications anticipate these scenarios and handle them gracefully.

There are three main types of errors in VBA:

Syntax Errors: Mistakes in code structure caught during compilation
Runtime Errors: Occur during execution (like division by zero)
Logical Errors: Code runs but produces incorrect results

This lesson focuses on handling runtime errors, which are the most common in user-facing applications.

14.2 The On Error Statement

The foundation of VBA error handling is the On Error statement, which comes in three forms:

1. On Error GoTo Label

Directs code to a specific label when an error occurs

2. On Error Resume Next

Ignores the error and continues with next line

3. On Error GoTo 0

Disables current error handling

Example 14.1: Basic Error Handling

Private Sub CommandButton1_Click()
    On Error GoTo err_handler
    num1 = InputBox("Enter first number")
    num2 = InputBox("Enter second number")
    MsgBox num1 / num2
    Exit Sub

err_handler:
    MsgBox "Error: " & Err.Description & vbCrLf & _
           "Please enter valid numbers and ensure denominator isn't zero", _
           vbExclamation, "Calculation Error"
End Sub

Key improvements:

Added Err.Description to show specific error details
Used vbCrLf for better message formatting
Added a proper message box title

Example 14.2: Advanced Nested Error Handling

Private Sub CommandButton2_Click()
    Dim firstNum As Double, secondNum As Double
    
    On Error GoTo input_error
    firstNum = InputBox("Enter first number")
    secondNum = InputBox("Enter second number")
    
    On Error GoTo calculation_error
    MsgBox "Result: " & firstNum / secondNum, vbInformation, "Division Result"
    
    Exit Sub
    
calculation_error:
    Select Case Err.Number
        Case 11 'Division by zero
            MsgBox "Error: Cannot divide by zero!", vbCritical, "Math Error"
        Case 6 'Overflow
            MsgBox "Error: Number is too large!", vbCritical, "Math Error"
        Case Else
            MsgBox "Unexpected error " & Err.Number & ": " & Err.Description, _
                   vbCritical, "Error"
    End Select
    Exit Sub
    
input_error:
    If IsNumeric(firstNum) Then
        MsgBox "Invalid second number entered", vbExclamation, "Input Error"
    Else
        MsgBox "Invalid first number entered", vbExclamation, "Input Error"
    End If
    Resume Next
End Sub

Enhancements include:

Specific handling for different error types
Use of Select Case for multiple error conditions
More informative error messages
Proper variable typing with As Double

14.3 Best Practices for Error Handling

Always include error handling in procedures that interact with users or external data
Use specific error handlers rather than generic ones when possible
Provide meaningful error messages that help users correct the problem
Log errors to a file or worksheet for debugging purposes
Clean up resources (close files, release objects) in error handlers
Use Err.Clear when reusing error handlers

Example 14.3: Error Logging System

Private Sub LogError(errNumber As Long, errDescription As String, _
                    Optional procedureName As String = "")
    Dim logSheet As Worksheet
    On Error Resume Next 'Prevent errors in error handler!
    
    Set logSheet = ThisWorkbook.Sheets("ErrorLog")
    If logSheet Is Nothing Then
        Set logSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        logSheet.Name = "ErrorLog"
        logSheet.Range("A1:D1").Value = Array("Timestamp", "Procedure", "Error #", "Description")
    End If
    
    With logSheet.Cells(logSheet.Rows.Count, 1).End(xlUp).Offset(1, 0)
        .Value = Now
        .Offset(0, 1).Value = procedureName
        .Offset(0, 2).Value = errNumber
        .Offset(0, 3).Value = errDescription
    End With
End Sub

14.4 The Err Object

VBA provides the built-in Err object with several useful properties:

Property	Description
Number	Numeric code identifying the error
Description	Text description of the error
Source	Name of the object/application that generated the error
HelpFile	Path to help file related to the error
HelpContext	Context ID for help topic

14.5 Common VBA Error Codes

Here are some frequently encountered error numbers:

Error #	Description
6	Overflow (number too large)
7	Out of memory
9	Subscript out of range
11	Division by zero
13	Type mismatch
1004	Application-defined or object-defined error

14.6 Summary of Key Points

Always implement error handling in production code
Use On Error GoTo Label for structured error handling
On Error Resume Next can be useful but should be used sparingly
Provide clear, helpful error messages to users
Log errors for debugging and maintenance
Clean up resources properly in error handlers
Use the Err object to get detailed error information
Test your error handlers thoroughly with various error conditions

🔗 Related Resources

❮ Previous Lesson Next Lesson ❯

Contact: Facebook Page