VBA – Error handling

Error handiling in VBA is a standard – once the error occurs, something should happen to prevent “breaking” the whole program. And usually this “something” is a MsgBox with information for the error.

General error handling in VBA

The general error handling in VBA looks like this:

In the sub, an On Error GoTo SubName_Error command is written, thus sending the code to the MsgBox line, whenever any error appears. The Err.Number , Err.Source and Err.Description are united with dashes, to provide some information about the error message:

This line could be changed in order to provide nice and meaningful error messages. E.g. if we want the number, the source and the description on a different line and having the title of the worksheet in the caption of the error message, it looks like this:

Handling of 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.
Tagged with: , ,