Implementing Simple Error Handling

Before I close out this chapter, I want to introduce the error-handling features of VBA. Implementing error handling into your procedures can get complicated really quickly, at least more complicated than you are prepared to handle at this point. However, it is fairly easy to implement basic error handling. In fact, you can implement the most basic error handling into a procedure by including one statement near the beginning of it:

On Error Resume Next

When an error occurs in a procedure with On Error Resume Next, VBA forgets about it and executes the remaining code as if nothing ever happened. Kind of like an infinite supply of those get out of jail free cards from Monopoly. Before you put this statement in every procedure, you must consider how the remaining code in your procedure will behave if a statement containing an error is ignored. Sometimes On Error Resume Next just delays problems from occurring.

Another benefit to using On Error Resume Next or one of its variations is that when errors occur, you can shield the end user from being dumped into the VBE.

You can use On Error GoTo 0 to disable error handling in a procedure that had previously specified an error-handling mechanism. This allows you to toggle error handling on and off in a procedure as your needs dictate.

Finally, note that On Error Resume Next is disabled if the procedure calls another procedure unless the called procedure also contains On Error Resume Next.

TlP Generally it is best to avoid using On Error Resume Next during the development process unless it is specifically needed. That way, when you're developing and testing, you'll be notified of run-time errors and placed into Break mode on the offending line. If you use On Error Resume Next, the procedure will keep executing and you may not know that an error occurred.. If you do notice that an error occurred, you won't know which line caused it unless you trace it in some way.

Another form of the On Error statement follows: On Error GoTo lineLabel

This form of error handling allows you to direct any errors to a certain segment of your code that is specifically designed for handling errors. A common skeleton procedure that uses this form of error handling is shown in Listing 4.7.

Listing 4.7: A Generic Procedure Skeleton with Basic Error Handling

Sub GenericProcedure()

On Error GoTo ErrHandler ' procedure statements go here

Exit Sub ErrHandler:

' error handling statements go here ' notify user if desired

MsgBox "Sorry, but an error has occurred. " & _ Err.Number & ": " & Err.Description _ , vblnformation + vbOKOnly, _ "Oops. An Error has occurred."

' and/or make a note in the immediate window ' helpful during the development/testing ' process

Debug.Print "ERROR OCCURED IN GenericProcedure..." Debug.Print Err.Number & ": " & Err.Description End Sub

As you can see, error-handling code can add quite a bit of code to otherwise simple procedures. You should note three things about this listing. First, the On Error GoTo ErrHandler line means that if an error occurs, the program should go to the line labeled ErrHandler. (You can label lines by entering a label followed by a colon [:].) Second, notice the Exit Sub statement just before the ErrHandler label. During normal, uneventful (no errors) execution of the procedure, the procedure would end at the Exit Sub statement rather than the End Sub statement. Finally, in the error-handling statements, you'll notice statements that refer to Err.Number and Err.Description. Err is a special object that contains information about any error that occurs. When VBA encounters a run-time error it automatically creates an Err object and populates it with information about the error. You can use If.. .Then statements or Select.. .Case statements that operate on the Err.Number to decide how to handle an error.

0 0

Post a comment