01 December 2009

Excel VBA programming with run-time errors

When writing VBA programs for Microsoft Excel 2003, you quickly find that you have to detect and handle run-time errors in your programs. VBA provides On Error and Resume statements for handling run-time errors. This article discusses how run-time errors can be used, explicitly or implicitly, in VBA programs.

Let's start with a script below, which calls the WorksheetFunction class' Match() function to find a cell with the value z in the range B1:B8:

Sub TestMatch()
  Dim wks As Worksheet
  Set wks = Sheet1
  Dim dblMatchResult As Double
  dblMatchResult = Application.WorksheetFunction.Match("z", wks.[B1:B8], 0)
  Debug.Print "Found at " & dblMatchResult
End Sub

If Match() function succeeds, then the relative row number is returned. However, if Match() fails, Excel generates this error message: Run-time error '1004': Unable to get the Match property of the WorksheetFunction class. In this case, the error means that Match() did not find z in the range B1:B8. It is similar to a #N/A result in a cell (though Error 1004 is a VBA error code and not related to the Excel-specific xlErrNA).

Below, we explicitly use the run-time error to (trivially) determine what to print in the statement after the error with On Error Resume Next. We also explicitly clear the error handler and allow errors to be detected again with On Error GoTo 0 (of course, detecting errors again doesn't matter in this short example).

Sub TestMatch()
  Dim wks As Worksheet
  Set wks = Sheet1
  Dim dblMatchResult As Double
  On Error Resume Next
  dblMatchResult = Application.WorksheetFunction.Match("z", wks.[B1:B8], 0)
  Debug.Print "z " & IIf(Err.Number = 0, "found at " & dblMatchResult, "not found")
  On Error GoTo 0
End Sub

Another common situation where you may explicitly test error codes is when you use a Collection. VBA's Collection class do not have a method to test if a key exists, so the only way to know if a key exists in your collection object is to try and retrieve the key from the collection and get a run-time error, as in the script below.

Sub TestCollection()
  Dim col As New Collection
  col.Add Key:="b", Item:="bog"
  col.Add Key:="d", Item:="dog"
  Dim str As String
  On Error Resume Next
  str = col("c")
  Debug.Print "Key c " & IIf(Err.Number = 0, " found", " not found") & " in collection"
  On Error GoTo 0
End Sub

In this example, the error would be Run-time error '5': Invalid procedure call or argument since the key c doesn't exist in the collection.

(Some run-time error messages generated by VBA are misleading: Error 1004 sounds like VBA failed to resolve the Match() function in the WorksheetFunctions class, and Error 5 is unclear because neither the procedure nor the argument is invalid. Error messages are also inconsistent. The commonly used Sheets Collection generates a different run-time error message than a VBA Collection if you try to access a key that doesn't exist. Try this in the Immediate Pane: ? Sheets(99).Name or ? Sheets("Blah").Name. Unless you have 99 sheets or a sheet called Blah in your workbook, you will see this more comprehensible error, Run-time error '9': Subscript out of range rather than Error 5.)

Some errors can be incidental to your program's logic and easily rectified, such that you can use VBA's On Error GoTo <label> statement to jump to a statement block to fix the error, then re-execute the statement that caused the error with Resume 0. For instance, the following function returns a reference to a sheet. It tries to obtain a sheet reference using the statement on line 3, jumping to lblCreateSheet to create a sheet first if it does not exist, then re-executing line 3.

1 Function AddSheetGoTo(ByVal str As String) As Worksheet
2   On Error GoTo lblCreateSheet:
3   Set AddSheetGoTo = Sheets(str) 'Run-time error '9': Subscript out of range
4   Exit Function
5 lblCreateSheet:
6   Sheets.Add.Name = str
7   Resume 0
8 End Function

Using On Error GoTo <label> ... Resume 0 means that the body of your function is not cluttered with exception handling statements, though you have to include Exit (Function or Sub) to explicitly end your function (and avoid inadvertently executing the error handler), and you should ensure that your error handler can actually fix the error, otherwise you will end up with an infinite loop. Compare this approach with explicitly testing and clearing an error using On Error Resume Next ... On Error GoTo 0, where you end up with a slightly longer and more cluttered function:

 1 Function AddSheetResume(ByVal str As String) As Worksheet
 2   Dim wks As Worksheet
 3   On Error Resume Next
 4   Set wks = Sheets(str) 'Run-time error '9': Subscript out of range
 5   If Err.Number <> 0 Then
 6     On Error GoTo 0
 7     Sheets.Add.Name = str
 8     Set wks = Sheets(str)
 9   End If
10   Set AddSheetResume = wks
11 End Function

This article discussed how VBA's run-time error handling can be used explicitly as part of your program logic, or implicitly to keep your program logic clear. It also notes that VBA run-error codes are not always clear or consistent.