Error-Handling in Excel Visual Basic

It is difficult to make your program foolproof, because fools are so ingenious!  Even so, one should make an effort to trap and handle errors. You should not let the program simply crash if there is a problem, whether due to bad data, faulty calculation, or whatever. This is what we call Error-Handling.  Excel Visual Basic provides the On Error statement for this purpose:

1.        Basic Error-Handling with On Error

For this, use On Error GoTo, as in the following code for a new Sub:

Sub Invert4() ' Invert Numeric Text, with "On Error" trap:
  Dim Text1$
  On Error GoTo Err_
  Text1$ = InputBox("Input a Number to Invert")
  MsgBox Text1$ & " Inverted = " & (1 / Val(Text1))
Exit_: Exit Sub

Err_: MsgBox "Error " & Err & vbLf & Error$, vbExclamation, _
  "Error on Sub Invert4!"
  Resume Exit_
End Sub

Note the line Exit_: Exit Sub – this is important!  It stops normal code execution from running into the error-handler, a cause of much needless frustration!

2.        Syntax of the On Error Statement

The full syntax of On Error is:

On Error GoTo lineLabel   ' set up error-trapping
On Error GoTo 0           ' Cancel error-trapping (GoTo zero)
On Error Resume Next      ' Ignore errors

The latter form is best avoided unless you are sure that an error in subsequent code can safely be ignored.

The error-handler should end with a Resume statement in one of these forms:

Resume linelabel    ' Resume at specific line
Resume Next         ' Continue at line after error
Resume             ' Continue at error line

The latter is best avoided unless you are sure that the cause of the error has been fixed.

Tip:  At the end of your simple error-handler (after Resume Exit_), insert a new line: Resume.  The program cannot get there in normal program flow, but you can use it when debugging.  It will let you return to the error line when you have fixed the error.  If your error-handling message box appears and you want to debug it, press Ctrl+Break and click OK.  When you have dealt with the error, you can resume execution at the error line as follows.  Click on the Resume line and press Ctrl+F9 to set it as the next statement.  Or drag the yellow arrow to point at Resume.  Press F8 (Step) to go to the offending statement, and F8 again to execute it.

3.        The Err Object and the Error$ Function

You can use the Err object to determine the error number (if this means anything to you).  The Error$ function provides the error message, which is more useful.  Err = 0 if there was no error since the last On Error statement.

Any On Error statement clears the values of Err and Error.  So, after an On Error Resume Next, you must check for Err>0 before another On Error statement.

4.        A More General Error-Handler

Here is a very general error-handler that allows the user to decide on the next action:

Err_:  '  General Error-Handler:
  Select Case MsgBox("Error " & Err & vbLf & Error$, _
    vbQuestion + vbAbortRetryIgnore, "Error!")
  Case vbAbort:   Resume Exit_
  Case vbIgnore:  Resume Next
  Case Else:      Resume
  End Select
End Sub

Note:

In the Select Case statement, we used the line continuation character “ _” (space underscore) to continue a long statement on the following line.  You can do this in your code to keep it to a manageable width.

5.        Scope of Error Handlers

An error-handler is valid only within the procedure in which it is set.

An error-handler set with On Error is valid until the next On Error statement.  Use On Error GoTo 0 (zero) to cancel error-handling.

If an error-handler is set in Sub A which then calls Sub B where an unhandled error occurs, the error is trapped in Sub A’s error-handler.  You could then have a hard time tracking down where the error occurred: It could be anywhere in Sub B!

Say that both Sub A and Sub B have error-handlers set.  If Sub A calls Sub B, the latter will handle any error that occurs in it.  When Sub B ends and execution returns to Sub A, its error-handler will again be in force.