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:
|
1 2 3 4 5 6 7 8 9 |
Sub TestMe() On Error GoTo TestMe_Error Debug.Print 0 / 0 Exit Sub TestMe_Error: MsgBox Err.Number & " " & Err.Source & " - " & Err.Description, vbCritical, "Error" End Sub |
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:
|
1 2 3 |
MsgBox "Error number - " & Err.Number & vbCrLf & _ "Error source - " & Err.Source & vbCrLf & _ "Description - " & Err.Description, vbCritical, Title:=ThisWorkbook.Name |
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:
|
1 2 3 4 |
Sub TestMe() Dim a As Range a = Nothing 'set a = Nothing would not be an error :) End Sub |
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 |
catch (FileNotFoundException e) { // FileNotFoundExceptions are handled here. } catch (IOException e) { // Extract some information from this exception, and then // throw it to the parent method. if (e.Source != null) Console.WriteLine("IOException source: {0}", e.Source); throw; } |
#VBA is not that fancy, but provides possibility to mimic the different exception handling, by checking the error number or the error description:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Sub TestMe() On Error GoTo TestMe_Error Dim result As Range Set result = Cells.Find("Nothing") Debug.Print result.Row Debug.Print "Something here" Debug.Print 5 / 0 Debug.Print "Unreachable Code" TestMe_Error: Select Case Err.Number Case 91 Debug.Print "Error 91 is here" Err.Clear Set result = Range("A100") Resume Case Else Debug.Print "Some other error!" End Select End Sub |
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:
|
1 2 3 4 |
Debug.Print "Error 91 is here" Err.Clear Set result = Range("A100") Resume |
-
- 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.