Trapping errors

You can use the On Error statement to specify what happens when an error occurs. Basically, you have two choices:

♦ Ignore the error and let VBA continue. You can later examine the Err object to determine what the error was and then take action if necessary.

♦ Jump to a special error-handling section of your code to take action. This section is placed at the end of the procedure and is also marked by a label.

To cause your VBA code to continue when an error occurs, insert the following statement in your code:

On Error Resume Next

Some errors are inconsequential and can simply be ignored. But you may want to determine what the error was. When an error occurs, you can use the Err object to determine the error number. The VBA Error function can be used to display the text for Err.ValueNumber, which defaults to just Err. For example, the following statement displays the same information as the normal Visual Basic error dialog box (the error number and the error description):

MsgBox "Error " & Err & ": " & Error(Err)

Figure 9-6 shows a VBA error message, and Figure 9-7 shows the same error displayed in a message box. You can, of course, make the error message a bit more meaningful to your end users by using more descriptive text.

Referencing Err is equivalent to accessing the Number property of the Err object.Therefore,the following two statements have the same effect:

MsgBox Err MsgBox Err.Number

|(General) ^rj jchaiigecase

JSlx)

J

I Sub ChangeCas

Worksheets["Sheet2").Name = "Data"

End Sub

Microsoft Visual Basic

Run-time error '9':

Subscript out oF range

End | |i Debug j| Help

|H"JLJ_1 _U

ô

Figure 9-6: VBA error messages aren't always user-friendly.

Figure 9-6: VBA error messages aren't always user-friendly.

Figure 9-7: You can create a message box to display the error code and description.

Figure 9-7: You can create a message box to display the error code and description.

You also use the On Error statement to specify a location in your procedure to jump to when an error occurs. You use a label to mark the location. For example:

On Error GoTo ErrorHandler

0 0

Post a comment