VBA – How to handle only specific errors in VBA

Sometimes, but only sometimes, someone needs to handle specific VBA errors and to avoid them. Or to ignore them. Or anything like this.

Let’s say, you want to avoid error number 91, because you think that it is a case that should be handled differently. Usually, this error is shown, when an object variable is not set. This is some minimal way of achieving this error:

So, let’s imagine, that in our scenario, we need to “catch” error number 91 and act differently if it exists, than if any other error exists. In the “normal” world of C# development, this is named try-catch exceptions – (code from here):

#VBA is not that fancy, but provides possibility to mimic the different exception handling, by checking the error number or the error description:

The code above returns the following:

 

 

 

 

And actually is a bit of spaghetti code. How does it work?

    • With  Set result = Cells.Find("Nothing"), considering that the word “Nothing” is not present on any cell, the range result is set to  Nothing
    • On the next line, accessing result.Row  throws automatically error 91
    • On the error handler, there is explicit check for the number of the error –  Select Case Err.Number
    • And because the error is number 91, the next 4 lines are carried out:

    • With the  Resume keyword, the program continues on the place, where it was broken. However, this time  result is equal to Range("A100") , thus 100 is printed on the console
    • Then  Debug.Print "Something here" is printed on the next line
    • Then the 5/0 returns an error, which is handled by the  Case Else of the error handler.

Pretty much, this is all!

Tagged with: , ,