Some errors can be incidental to your program's logic and easily rectified, such that you can use VBA's On Error GoTo statement to jump to a statement block to fix the error, then re-execute the statement that caused the error with Resume 0. 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.) Try this in the Immediate Pane: ? Sheets(99).Name or ? Sheets("Blah").Name.
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. (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. 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. 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.ĭebug.Print "Key c " & IIf(Err.Number = 0, " found", " not found") & " in collection" 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).ĭebug.Print "z " & IIf(Err.Number = 0, "found at " & dblMatchResult, "not found")Īnother common situation where you may explicitly test error codes is when you use a Collection.
#Excel vba on erro go to code#
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).īelow, we explicitly use the run-time error to (trivially) determine what to print in the statement after the error with On Error Resume Next. In this case, the error means that Match() did not find z in the range B1:B8. However, if Match() fails, Excel generates this error message: Run-time error '1004': Unable to get the Match property of the WorksheetFunction class. If Match() function succeeds, then the relative row number is returned. 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:ĭblMatchResult = ("z", wks., 0) This article discusses how run-time errors can be used, explicitly or implicitly, in VBA programs. VBA provides On Error and Resume statements for handling 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.