CODEWITHSUNDEEP
excelvbaerror-handling
Anoplexian
Anoplexian

Reputation: 117

Handling only specific VBA Errors

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?

Upvotes: 1

Views: 1784

Answers (2)

Vityata
Vityata

Reputation: 43595

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.

Upvotes: 2

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

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

Upvotes: 5

Related Questions