Error Handling

---

Every procedure should have some form of error-handling abilities, even if it does no more than inform the user of what caused the error and then exits. Procedures that are meant to be called from other procedures and not necessarily invoked by the user, for example, should return to the calling procedure some type of indication that it was able to complete successfully.

Consider the Select Case example covered earlier in the chapter that calculated the number of days in a month. It specifically checked whether or not the variable intMonth matched any of the expected clauses. If intMonth contained any value outside of the 1 to 12 range, the Select Case statement returned a -1.

By returning a value that is outside the realm of expected values, it allows any statement that depended upon the value of intMonth to confirm that it was a legal value.

Besides adding data validation code to check your own code, VBA also allows you to trap errors that it detects. By trapping the errors that VBA detects, your procedure might be able to correct them and continue executing.

To trap VBA errors, you use the On Error command. It takes the basic form

On Error Goto line label 

In this line of code, line label specifies the section of code you have written to handle errors.

The following code fragment shows how you could trap errors and branch to a label named ErrorHandler:

On Error GoTo ErrorHandler
    [statements]
ErrorHandler:
    [error handler statements]

Because you would want to start trapping errors as quickly as possible, the On Error statement should be at the beginning of the procedure. Also, you are not limited to only a single error handler per procedure; you could specify different error handlers as the procedure progresses, even returning to earlier error handlers. The following code fragment illustrates one way to construct a series of error- handling routines:

On Error GoTo ErrorHandler1
    [statements]
On Error GoTo ErrorHandler2
    [statements]
On Error GoTo ErrorHandler1
    [statements]
ErrorHandler1:
    [errorhandler1 statements]
ErrorHandler2:
    [errorhandler2 statements]

Because VBA will execute an entire procedure until the end is reached, it is necessary to include instructions so that VBA will skip any error handlers you have written if no error has been trapped. The most common way to cause VBA to avoid executing error-handling code is to place all error-handling code at the end of a procedure and then, in the line immediately before the error handler, placing the Exit Sub or Exit Function statement to exit the procedure.

For more information about the two types of procedures (Sub procedures and Function procedures), see Chapter 5, 'Creating Sub and Function Procedures.'

Once an error has been trapped, how can you tell what the error was and take corrective actions if possible? VBA sets various properties of the Err object to describe the error. Two of the properties, Number and Description, provide a numerical error code and a string description, respectively, of the error. You can use the error code to recognize and correct common or expected errors.

Error handlers work only for the procedure in which they appear; once the procedure has ended, the error trap is disabled. You can also disable the error handler yourself by using the following statement:

On Error GoTo 0 

If your error handler is able to fix the problem that raised the error to begin with, you will want to resume execution of your procedure. That is done by using the Resume command. Placed inside of an error handler, it resumes execution with the statement that generated the error originally.

At times, the error handler might not be able to correct the problem, but if the error itself was not fatal to the execution, the error would not cause the rest of the procedure to fail. If that's the case, you can resume execution of the procedure by using the Resume Next statement. This continues execution with the statement immediately after the statement that raised the error. You can also specify execution to resume at a specific statement by using the Resume line statement. This will continue execution at the statement named by line.

The following code fragment illustrates a common method of using error handlers:

On Error Goto ErrorHandler
    [statements]
FinishedSub:
Exit Sub
ErrorHandler:
MsgBox(str$(Err) & ": " & Err.Description, vbInformation, "Error Handler")
Resume FinishedSub
 

This rather lengthy, though hopefully complete, chapter has covered all of the basic skills you need to begin creating VBA macros. You've learned how to define and assign values to variables, to control the flow of your programs using loops and tests, and to handle any errors that come along. In Chapter 5, you'll learn how to create and use procedures that contain your VBA code.

---