Error Trapping & Handling

There may come a time when you wish that a macro could itself respond to non-emergency errors and continue, rather than simply ending the macro and generating an error message. You may even wish you could create your own error messages in easily-upset sections of a macro, based on events that normally wouldn't trigger an error. This is where error handling comes in.

The first command to learn is On Error. It takes control of error handling away from the computer and gives it to the macro, and tells the macro what to do when an error occurs.

  On Error Goto ErrorHandler

  On Error Resume Next

The first statement tells the macro to go to a section of the macro with the ErrorHandler: label. The second tells the macro to skip the program line that generated the error and continue running the macro. On Error requires the use of Resume, either as part of the statement or as part of the error-handling routine. Here is an example of an error-handling routine in action:

  Sub Main

    Dim CS as Object

    Set CS = CreateObject("Connex.Client")

    On Error Goto ErrorHandler

    bool = CS.Validater(Errors$)

    Goto Done 'You could also use: Exit Sub



  ErrorHandler:

    MsgBox "There was an error.  Macro ending..."

    Resume Done



  Done:

  End Sub

Right after setting up the "CS" short-cut, the macro is told that if an error occurs, it should go to "ErrorHandler" instead of immediately stopping the macro. The macro then goes through its procedures; in this case validating a record. If everything runs smoothly, it then skips to the end of the macro. However, everything will not run smoothly, because Validate has been misspelled "Validater"; because it is preceded by "CS.", the macro editor will not find this mistake until you actually try to run the macro, at which point it generates an error. The macro then jumps down to the ErrorHandler section, and executes the commands it finds there; in this case, a message box. Then the error handler tells the macro where to resume running the program, usually either through Resume Next, which returns to the line after the one that generated the error, or Resume <label> (in the above case, Resume Done), which jumps to the specified label and resumes executing commands there.

Some related statements you may find useful are Err, Error, and Erl. Erl gives you the program line that triggered the error, Err gives you the error code, and Error gives you the error description. For the following examples, assume that a "Command failed" error just occurred in the 54th line of the program:

  ELine% = Erl

  ENum% = Err

  e$ = Error

  MsgBox "Error (" & CStr(ENum%) & ") in line " & CStr(ELine%) & ": " & e$

The message box displays "Error (102) in line 54: Command failed". There is a list of all of the built-in trappable errors with their associated numbers and text in the macro help file, under "Trappable Errors." Using these commands, you can perform triage of sorts on the errors that come up, as in this program fragment:

  On Error Resume Next

  x = 3 / 0

  a$= "2" + 2

  Select Case Err

    Case 102

      MsgBox "Command Failed: Line " & Erl

      Goto Done

    Case 11

      MsgBox "Division by zero: Line " & Erl

      Goto Done

    Case 438

      MsgBox "No such property or method: Line " & Erl

      Goto Done

    Case Else

      Goto Done

  End Select

It is also possible to create and trap your own errors. If you want a program to trigger an error if the data in a string is not numerical, you might try something along these lines:

  Sub Main

    On Error Goto ErrorHandler

  StartStuff:

    Err = 0

    a$ = InputBox("Enter a number:")

    x = 1

    Do While x <= Len(a$)

      If Asc(Mid(a$, x, 1)) < 48 Or Asc(Mid(a$, x, 1)) > 57 Then 

        Error 10000

      End If

      x = x + 1

    Loop



  ErrorHandler:

    If Err = 10000 Then

      MsgBox "Input must be numeric only"

      Resume StartStuff

    Else

      MsgBox "Error (" & Err & ") in line " & Erl & ": " & Error Resume Done

    End If



  Done:

  End Sub

The "Err = 0" line at the beginning is important, because it clears the system of any error codes that have been triggered so far. (Otherwise, if an error has been trapped and dealt with earlier in the program, the wrong code could be reported.) When the program gets down to the Do...Loop, it checks each character in the string to see if it is 0-9 (0 is ASCII 48, 9 is ASCII 57); if it isn't, it triggers an Error of 10000, and jumps to the error handler. Once there, the handler checks to see if the error is the one you've created. If it is, it displays a message box, then goes back to the beginning of the program. Once back at the beginning, "Err = 0" resets the error status; if that line were missing, the program will reach the ErrorHandler section after a successful attempt and act as if there were an error anyway, as Err would still equal 10000 from the last error. If the error is not the one you've created, the program displays the error information in a message box and ends the macro.

There is one last piece of the puzzle of errors. If you have set up an error handler and have used an On Error statement to direct the program to an error handler, but want to turn off error handling for part of your macro and just use the program's standard method of error reporting, insert On Error Goto 0. If an error occurs after this line and before any other On Error statements, then the macro will immediately stop running and a message box will appear showing the error information.

Next time, dialog boxes...

Lesson 12 is long in comparison to previous lessons. You may wish to try it in smaller doses.