Chapter 8: Errors and the Error Function

Overview

Runtime errors can creep into code very easily, through no fault of the programmer. The user does something outside the scope of the code and causes an error message to occur that stops all execution of code. This may occur where you are accessing an external data source such as a database. The user may also take an action that the programmer never envisaged.

No matter how carefully you try to provide for all conditions, there is always a user who does something that you never thought of and effectively breaks the set of rules that you have written. It may be as simple as including a name with an apostrophe in such as O'Brien. If you use this name in a SQL query string, it will cause problems in the way it is handled.

Another example is reading a file in from a disk device. The programmer may allow the user to select the drive letter for the file to be read. You assume that the files will mainly come from network drives , and an A: floppy drive option is just nice to have, but it will probably never be used. However, if the user selects A: (floppy disk drive), then there is a possibility that there will not be a disk in the drive. This will create an error that will stop execution of your program. The user will be very unhappy and will lose a great deal of faith in your application. The error needs to be trapped, and an appropriate action needs to be taken from within the VBA code.

Error handling is also used on common dialog forms (see Chapter 10) to show that the Cancel button has been clicked. In order to test whether the user clicked the Cancel button, you had to set the CancelError property to True and then put in an On Error statement to direct the code where to go for an error.

Try this simple example without a disk in drive A. Place the code into a code module and then run it by pressing F5 :

 Sub Test_Error() 
 
 temp = Dir("a:\*.*") 
 
 End Sub 

This will produce an error message saying that the A drive is not ready. In normal terms, your program has crashed and will not work any further until this error is resolved. Not very good from the user's point of view!

You can place a simple error-trapping routine as follows :

 Sub Test_Error() 
 On Error GoTo err_handler 
 temp = Dir("a:\*.*") 
 Exit Sub 
 err_handler: 
 MsgBox "The A drive is not ready" & "   " & Err.Description 
 End Sub 

The first line sets up a routine to jump to when an error occurs using the On Error statement. It points to err_handler , which is a label just below the Exit Sub line further down that will deal with any error condition. The purpose of a label is to define a section of your code you can jump to by using a GoTo statement.

The line to read the A drive is the same as before, and then there is an Exit Sub line, because if all is well you do not want the code continuing into the err_handler routine.

If an error happens at any point after the On Error line, the code execution jumps to err_handler and displays a message box that says drive A is not ready. However, you may have noticed that the code execution jumps to err_handler when any error occurs, not just the drive not ready error. An error could occur because you made a mistake in typing this code in. This could have unfortunate consequences in your code.

Fortunately, you can interrogate the error to find out what went wrong. You can also use the Err object to give the description of the error and to concatenate it into your message so that it also says ‚“Drive not ready. ‚½ You do this using the Err function. This will return the number associated with the runtime error that happened . You can add this into the previous example as follows:

 Sub Test_Error() 
 On Error GoTo err_handler 
 temp = Dir("a:\*.*") 
 Exit Sub 
 err_handler: 
 If Err.Number = 71 Then 
 MsgBox "The A drive is not ready" 
 Else 
 MsgBox "An error occurred" 
 
 End If 
 End Sub 

You saw from the first example that the number for ‚“Drive not ready ‚½ came up in the error message box as 71. The program looks at Err (a system variable that holds the last error number) and checks to see if it is 71. If it is, it displays the message box, ‚“The A drive is not ready ‚½; if it is not, it displays the message box, ‚“An error occurred. ‚½