Handling only specific VBA Errors

1
votes

I have two errors that are possible when running my code. The first is a common error in which my .Find method can't find anything, and I'd like it to resume next if this happens. It's a completely normal occurrence, and I need to leave it in for my manager to approve the code (legacy VBA code is still used and he's scared to change it.

I'd like to specify that if this error is seen then to do nothing, but if it's a specific other error to flag it and be handled by a more robust error handling.

The error I'd like to "ignore" (as in Resume Next or GoTo a specific place in the rest of the code without worrying about the error, I'm not worried about further down the code) is Runtime Error 91. Specifically in the code:

toFindCell1 = Cells.Find(nameVar).Row

where nameVar changes based on a for statement going down a list. I plan to then check it against existing information and use that variable to determine whether or not it exists. If it doesn't, then it will add it.

How can I specify the error I want to handle in VBA?

2
excelvbaerror-handling
What does "ingore" mean in that context? Not assign to toFindCell1? In order to properly answer this, we'd need to see more code than the line you want to suppress an error on. Otherwise, your next question will most likely be "why doesn't X work now that I ignore a .Find error". - Comintern
Ignore as in if I Cells.Find("whatever") comes up with that specific runtime error, I can have it safely resume next, but if it comes up with a different error, it won't follow that same pattern. In essence, I can Resume Next to "ignore" the error, but any other errors are properly caught and sent to the already created error handler. (forgot to tag) @Comintern - Anoplexian
Then why test for the error at all? Assign the .Find result to a Range, and then test to see if it is Nothing. Using error handling for flow control is overly complicated for what you want to do here. The error 91 is because .Find returns Nothing and then you try to use the return value without testing it. - Comintern

2 Answers

5
votes
toFindCell1 = Cells.Find(nameVar).Row

Range.Find returns Nothing, the .Row member call isn't legal. Don't do it!

If your code doesn't throw error 91 in the first place, then you don't need to handle error 91.

Dim result As Range
Set result = Cells.Find(nameVar)
If Not result Is Nothing Then
    toFindCell1 = result.Row
    '....
Else
    'not found.
End If
2
votes

The best practice is indeed to use the If Not result Is Nothing Then, as mentioned in the answer of Mathieu.

However, by some specific cases it could be really a good idea to catch a specific error number and continue, by fixing it. This is definitely not one of them, but is a good illustration, of how to "play" with Err.Number:

Sub TestMe()

    On Error GoTo TestMe_Error

    Dim result As Range
    Set result = Cells.Find("Something")

    Debug.Print result.Row
    Debug.Print "Something here"
    Debug.Print 5 / 0
    Debug.Print "This line is unreachable."

TestMe_Error:

    Select Case Err.Number
    Case 91
        Debug.Print "ERROR 91!"
        Err.Clear
        Set result = Range("A100")
        Resume
    Case Else
        Debug.Print "Some other error!"
    End Select

End Sub

What is happening in the code above? On line Debug.Print result.Row it throws error 91, which is caught by the error handler and then cleared with Err.Clear. As this is expected, Set result = Range("A100") is used in the error handler and the code continues from the line, which threw the error, but this time, result is valid. Once, it reaches the new error 5/0, then it throws error, different than 91 and exits.

This is the output:

ERROR 91!
 100 
Something here
Some other error!

Keep in mind that using conditional error handling could be considered spaghetti code by plenty of devs.