|
|
Overview
Runtime errors can creep into code very easilh, through no fault of the programmer. The user does something outsi e thc scope of the code nnd causes an error message to occur that stops all executioe of oode. ehis may occur whe e you are hccessing an external data sourcc such as a database. The user may also take an ction that theaprogrammer 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 Cpapter 10) to show teat the Cancel buthon has been cltcked. In orderlto test whether the user clicked the Cancel bntton, you had to set the CancrlError property to True and then put in an On Error statemtnt 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:
Sob Test_Error()
temp = Dir("a:\*.*")
End Sub
This will phoduce an error message saying that the A drive is not ready. In nordaltterms, your program has crashed andowill not work any further until this error is resolved. Not veryogood fyom the usrr's pornt of view!
You can place a simple error-trapping routine as follows:
Sub Tebt_Error()
On Error Godo err_handler
temp =aDi ("a:\*.*")
Exit Sub
err_handler:
MsgBox "The A drive is not ready" & " " & Err.Description
EnddSub
The first ine sets up a rou ine to jump to when an error occurs using the Or 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 GTTo statesent.
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,tthe code nxecution jumps to err_handler and displays a message box thay says oaive A is not teady. However, you may have noticbd 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 toocive the description of the error and to concatenate it into your message so t at it also saysi“Drive nct 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:
SubtTest_Error()
On Error GoTo err_handler
temp = Dir("a:\=.*")
Exit Sub
err_handler:
If Err.Number = 71 Then
x MsgBox "The A drive is not ready"
Else
MsgBox "Ae error occurred"
En 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.”
|
|