VBA Error Handling Avoiding error conditions
Example
When a runtime error occurs, good code should handle it. The best error handling strategy is to write code that checks for error conditions and simply avoids executing code that results in a runtime error.
One key element in reducing runtime errors, is writing small procedures that do one thing. The fewer reasons procedures have to fail, the easier the code as a whole is to debug.
Avoiding runtime error 91 - Object or With block variable not set:
This error will be raised when an object is used before its reference is assigned. One might have a procedure that receives an object parameter:
Private Sub DoSomething(ByVal target As Worksheet)
Debug.Print target.Name
End Sub
If target isn't assigned a reference, the above code will raise an error that is easily avoided by checking if the object contains an actual object reference:
Private Sub DoSomething(ByVal target As Worksheet)
If target Is Nothing Then Exit Sub
Debug.Print target.Name
End Sub
If target isn't assigned a reference, then the unassigned reference is never used, and no error occurs.
This way of early-exiting a procedure when one or more parameter isn't valid, is called a guard clause.
Avoiding runtime error 9 - Subscript out of range:
This error is raised when an array is accessed outside of its boundaries.
Private Sub DoSomething(ByVal index As Integer)
Debug.Print ActiveWorkbook.Worksheets(index)
End Sub
Given an index greater than the number of worksheets in the ActiveWorkbook, the above code will raise a runtime error. A simple guard clause can avoid that:
Private Sub DoSomething(ByVal index As Integer)
If index > ActiveWorkbook.Worksheets.Count Or index <= 0 Then Exit Sub
Debug.Print ActiveWorkbook.Worksheets(index)
End Sub
Most runtime errors can be avoided by carefully verifying the values we're using before we use them, and branching on another execution path accordingly using a simple If statement - in guard clauses that makes no assumptions and validates a procedure's parameters, or even in the body of larger procedures.
