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.
No comments:
Post a Comment