Trapping Errors

No one writes bug-free programs the first time. When you create VBA procedures, you have to determine how your program will respond to errors. Many unexpected errors happen during run time. For example, your procedure may try to give a workbook the same name as an open workbook. Run-time errors are often discovered not by a programmer but by 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 prevent 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 and more comprehensive error message, perhaps advising the user how to correct the error.

How do we 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 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.

The On Error statement can be used in one of the following ways:

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 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.

Tip 13-5: 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 quote or comma, or assigning 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, that does not mean that errors will not occur. An error is a result of an event or an 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.

The Archive procedure shown below uses the error-handling routine (see the bottom of the procedure). The procedure uses the built-in SaveCopyAs method to save the copy of the current workbook to a file without modifying the open workbook in memory.

1. Insert a new module into the current project and rename it Traps.

2. Enter the Archive procedure, as shown below:

Sub Archive() Dim folderName As String Dim DriveA As String Dim BackupName As String Dim Response As Integer

Application.DisplayAlerts = False On Error GoTo DiskProblem folderName = ActiveWorkbook.Path

If folderName = "" Then

MsgBox "You can't copy this file. " & Chr(13) _ & "This file has not been saved.", _ vbInformation, "File Archive"


With ActiveWorkbook

MsgBox "Place a diskette in drive " & DriveA & _ " and click OK.", , "Copying to " & DriveA BackupName = DriveA & .Name .SaveCopyAs Filename:=BackupName MsgBox .Name & " was copied to a disk in drive " & _ DriveA, , "End of Archiving"

End With End If

GoTo ProcEnd DiskProblem:

Response = MsgBox("There is no disk in drive A " & Chr(13) _ & "or disk in drive " & DriveA & " is not formatted ", _ vbRetryCancel, "Check Disk Drive")

If Response = 4 Then Resume 0


Exit Sub End If ProcEnd:

Application.DisplayAlerts = True End Sub

After the declaration of variables, the Archive procedure's Applica-tion.DisplayAlerts = False statement ensures that Visual Basic won't display its own alerts and messages while the procedure is running. The next statement, On Error GoTo DiskProblem, specifies a label to jump to when an error occurs. The path name where the active workbook was saved is stored in the variable folderName.

If Visual Basic can't find the workbook's path, it assumes the file was not saved and displays an appropriate message. Next, Visual Basic jumps to the statement following the End If and executes the instruction GoTo ProcEnd, which directs it to the ProcEnd label located just before the End Sub keywords. Notice that the label is followed by a colon. Visual Basic executes the statement Application.DisplayAlerts = True, which restores the system's built-in alerts and messages. Because there are no more statements to execute, the procedure ends.

If the active workbook's path is not an empty string, Visual Basic checks whether the recent changes in the workbook have already been saved. If they weren't, VBA uses the If Not .Saved Then .Save statement to save the active workbook. Saved is the VBA property of the Workbook object. Next, Visual Basic stores the name of the diskette drive "A:" in the variable DriveA and displays a message that prompts the user to insert a diskette into the specified drive. The name of the disk drive is then combined with the name of the active workbook and stored in the variable named BackupName.

As you know, while copying files to a diskette, all kinds of things can go wrong. For example, a diskette drive may be empty, or a diskette may be unformatted or full. When Visual Basic detects an error, it will jump to the line of code beginning with the label DiskProblem and an appropriate message will be displayed. If the user clicks the Retry button (4) in the message box, Visual Basic will execute the statement Resume 0. This statement will send Visual Basic to the statement that caused the error (.SaveCopyAs File-Name:=BackupName), and Visual Basic will execute it again. If the user clicks the Cancel button in the message box, VBA will execute the statement Exit Sub and the procedure will end.

If there's no problem with the diskette in drive "A:", VBA will copy the active workbook to the diskette and the message will notify the user that the copy operation was successful.

3. Run the Archive procedure several times, each time responding differently to the presented options. Make sure to test as many possibilities as you can identify. Use various debugging techniques that you have learned in this chapter.

Tip 13-6: Procedure Testing

You are responsible for the code that you produce. This means that before you give your procedure to others to test, test it yourself. After all, you will understand how it is supposed to work. Some programmers think that testing their own code is some sort of demeaning 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 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 little about using this particular application, and ask them to play around with it and try to break it.

Let's look at another example procedure. The OpenToRead procedure shown below demonstrates the use of the Resume Next and Error statements, as well as the Err object.

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 of file you want to open:") Open myFile For Input As #1 If FileExists Then

Do While Not EOF(1) ' loop until the end of file myChar = Input(1, #1) ' get one character myText = myText + myChar ' store in the variable myText


Debug.Print myText ' print to the Immediate window ' Close the file -commenting out this instruction will cause ' error 52. Close #1 End If

Exit Sub ErrorHandler: 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

The purpose of the OpenToRead procedure is to read the contents of the user-supplied text file character by character (working with files is covered in Chapter 8). When the user enters a filename, various errors can occur. For example, the filename may be wrong, or the user may attempt to open a file from a diskette when there is no disk in the diskette drive or 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.

If errors 71, 53, or 75 occur, Visual Basic will display user-friendly messages stated 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 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. This way, 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). This way, Visual Basic will not cause another error while trying to read a file that caused the error on opening. 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 test the OpenToRead procedure and better understand error trapping, let's perform the following exercise:

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 four times in the step mode, each time supplying one of the following:

■ Filename that does not exist on drive C

■ Name of any file in Drive A (when the diskette slot is empty)

■ Comment the Close #1 statement and enter C:\Vacation.txt as the filename

Tip 13-7: Errors: Generating Them to Test Error Handling

You can test the ways that your saying: Run-time error 11. Division by program responds to run-time errors zero.

by causing them on purp°se: ■ To check the meaning of the gener-■ Generate any built-in error by ated error, use the following syntax: using the following syntax: Error Error(error_number). For example, to error_number. For example, to find out what error number 7 means, display the error that occurs on type the following in the Immediate the attempt to divide by zero, type window: the following in the Immediate window:

Error a When you press Enter, Visual Basic returns the error description: Out of When you press Enter, Visual Basic memory. will display the error message

0 0

Post a comment