The Err Object

You may not have realized it, but when you got the “division by zero” error message, what you were really seeing was the information stored in an object. As soon as an error occurs, VBA generates an object of type Err, which holds details about that error—specifically, the error number and the error description. You may be wondering how that is any different from what you saw in the message box. Let’s take a look at the possibilities in coding.

In the first example, we are going to set up an object as we have done in previous chapters, create an error, and then query the object for the error number and description:

Sub errorTest()
    Dim intNumerator As Integer
    Dim intDenominator As Integer
    Dim intResult As Double
    On Error GoTo mytrap

        intNumerator = InputBox("Please enter a numerator", "Numerator")

enterDenominator:
       intDenominator = InputBox("Please enter a denominator", _
                                 "Denominator")

       intResult = intNumerator / intDenominator
        MsgBox "The result is " & intResult
    Exit Sub
mytrap:
    MsgBox "The number of this error is " & Err.Number
    MsgBox "The description of the error is " & Err.Description
    Resume enterDenominator
End Sub

In this code, you queried the object and isolated each piece of information. If you run it and set the denominator to 0, you end up with two message boxes, first showing the returned error number:

then, showing the returned error message:

If you are not completely sold as to the programming implications of this, let’s take this to the next step by making some changes to the error handler:

mytrap:    
    If Err.Number = 11 Then
       MsgBox ("The description of the error is " & Err.Description)
    Else
       MsgBox ("Something else is going wrong")
    End If
    Resume enterDenominator

Remember at the outset I asked what would happen if there was the possibility of multiple things going wrong. In this simple example, you see that you can handle multiple situations by conditionally testing the Err.Number property.

In essence, this program says that if an error occurs, transfer control of the code to the designated error handler called myTrap. Once in the handler, the handler will determine how to handle the error based on the error number.

The Err object offers a nice feature with the Source property: it tells you what is generating the error. Let’s modify the event handler as follows:

mytrap:    
    If Err.Number = 11 Then
      MsgBox (Err.Source)
    Else
       MsgBox ("Something else is going wrong")
    End If
    Resume enterDenominator

If you generate the “divide by zero” error now, you end up with this message showing the source of the error:

Since we are in TheCornerBookstore project, Source is showing that as the source of the error.