Trapping Errors

No one writes bug-free programs the first time. For this reason, when you create VBA procedures you have to determine how your program will respond to errors. Many unexpected errors happen at run time. For example, your procedure may try to give a new file the same name as an open file.

Run-time errors are often discovered not by a programmer, but the user who attempts to do something that the programmer has not anticipated. If an error occurs when the procedure is running, Visual Basic displays an error message and the procedure is stopped. Most often the error message that VBA displays is quite cryptic to the user. You can guard users from seeing many run-time errors by including error-handling code in your VBA procedures. This way, when Visual Basic encounters an error, instead of displaying a default error message it will show a much friendlier, more comprehensive error message, perhaps advising the user how to correct the error.

How can you implement error handling in your VBA procedure? The first step is to place the On Error statement in your procedure. This statement tells VBA what to do if an error happens while your program is running. In other

Introduction to Access 2003 VBA Programming words, VBA uses the On Error statement to activate an error-handling procedure that will trap run-time errors. Depending on the type of procedure, you can exit the error trap by using one of the following statements: Exit Sub, Exit Function, Exit Property, End Sub, End Function, or End Property.

You should write an error-handling routine for each procedure. See Table 9-3 for ways in which to use the On Error statement.

Table 9-3: On Error options

On Error Statement


On Error GoTo Label

Specifies a label to jump to when an error occurs. This label marks the beginning of the error-handling routine. An error handler is a routine for trapping and responding to errors in your application. The label must appear in the same procedure as the On Error GoTo statement.

On Error Resume Next

When a run-time error occurs, Visual Basic ignores the line that caused the error and does not display an error message, but continues the procedure with the next line.

On Error GoTo 0

Turns off error trapping in a procedure. When VBA runs this statement, errors are detected but not trapped within the procedure.

Is This an Error or a Mistake?

In programming, mistakes and errors are not the same thing. A mistake — such as a misspelled or missing statement, a misplaced quotation mark or comma, or an assignment of a value of one type to a variable of a different (and incompatible) type — can be removed from your program through proper testing and debugging. But even though your code may be free of mistakes, errors can still occur. An error is a result of an event or operation that doesn't work as expected. For example, if your VBA procedure accesses a particular file on disk and someone deleted this file or moved it to another location, you'll get an error no matter what. An error prevents the procedure from carrying out a specific task.

Let's look at the example procedure in Hands-On 9-7. The OpenToRead procedure shown below demonstrates the use of the Resume Next and Error statements, as well as the Err object.

© Hands-On 9-7: Error Trapping Techniques

1. In the Visual Basic Editor window, insert a new module.

2. In the Code window, enter the following OpenToRead procedure.

^^ Note: The purpose of the OpenToRead procedure is to read the contents of the user-supplied text file character by character. When the user enters a filename, various errors can occur. For example, the filename may be wrong, the user may attempt to open a file from a diskette when there is no disk in the diskette drive, or he may try to open a file that is already open. To trap these errors, the error-handling routine at the end of the OpenToRead procedure uses the Name property of the Err object. The Err object contains information about run-time errors. If an error occurs while the procedure is running, the statement Err.Number will return the error number.

Part I

Sub OpenToRead()

Dim myFile As String Dim myChar As String Dim myText As String Dim FileExists As Boolean

FileExists = True

On Error GoTo ErrorHandler myFile = InputBox("Enter the name Open myFile For Input As #1 If FileExists Then Do While Not EOF(1)


Debug.Print myText ' Close the file - commenting ' error 52. Close #1 End If Exit Sub of file you want to open:")

' loop until the end of file ' get one character ' store in the variable myText

' print to the Immediate window out this instruction will cause


FileExists = False Select Case Err.Number Case 71

MsgBox "The diskette drive is empty." Case 53

MsgBox "This file can't be found on the specified drive." Case 75

Exit Sub Case Else

MsgBox "Error " & Err.Number & " :" & Error(Err.Number) Exit Sub End Select Resume Next End Sub

Procedure Testing

You are responsible for the code you produce. This means that before you give your procedure to others to test, you should test it yourself. After all, you understand best how it is supposed to work. Some programmers think testing their own code is some sort of degrading activity, especially when they work in an organization that has a team devoted to testing. Don't make this mistake. The testing process at the programmer level is as important as the code development itself. After you've tested the procedure yourself, you should give it to the users to test. Users will provide you with answers to questions such as: Does the procedure produce the expected results? Is it easy and fun to use? Does it follow the standard conventions? Also, it is a good idea to give the entire application to someone who knows the least about using this particular application, and ask them to play around with it and try to break it.

Introduction to Access 2003 VBA Programming

If errors 71, 53, or 75 occur, Visual Basic will display the user-friendly messages given inside the Select Case block and then proceed to the Resume Next statement, which will send it to the line of code following the one that had caused the error. If another (unexpected) error occurs, Visual Basic will return its error code (Err.Number) and error description (Error(Err.Number)).

At the beginning of the procedure, the variable FileExists is set to True. If the program doesn't encounter an error, all the instructions inside the If FileExists Then block will be executed. However, if VBA encounters an error, the value of the FileExists variable will be set to False (see the first statement in the error-handling routine just below the ErrorHandler label).

If you comment the statement Close #1, Visual Basic will encounter the error on the next attempt to open the same file. Notice the Exit Sub statement before the ErrorHandler. Put the Exit Sub statement just above the error-handling routine. You don't want Visual Basic to carry out the error handling if there are no errors.

To better understand error trapping, let's perform another hands-on.

© Hands-On 9-8: Understanding Error Trapping

This hands-on requires prior completion of Hands-On 9-7.

1. Prepare a text file named C:\Vacation.txt using Windows Notepad. Enter any text you want in this file.

2. Run the OpenToRead procedure three times in the step mode, each time supplying one of the following:

■ The filename C:\Vacation.txt

A filename that does not exist on drive C

The name of any file in drive A (when the diskette slot is empty)

3. Comment the Close #1 statement and enter C:\Vacation.txt as the filename. Run the procedure twice. The second run will generate an error because C:\Vacation.txt will be open.

0 0

Post a comment