Writing your own error handlers

When writing your own code and your own error handlers, it's not realistic to assume that you can anticipate every possible environmental condition that might cause the procedure to crash. For starters, you can just write the basic code to trap the error, display the error number and description in a simple message box, and then exit the procedure gracefully.

Assume that you've already written a procedure, and now you want to add some error-handling to that procedure. Exactly what the procedure does is irrelevant, so rather than show a bunch of VBA statements here, I'll just refer to the existing statements as the main body of code below. First, you need to add an On Error... statement at or near the top of the procedure so that any runtime error that occurs during execution branches control to some label.

Next, you need to define the label to which the On Error statement refers. Typically, you can add an Exit Sub or Exit Function statement just above the End Sub or End Function procedure that's currently in the code. That will ensure that if the code runs without generating an error, code execution won't fall through to the error handler and make you think that there's an error when there isn't.

Finally, just above the Exit Sub or Exit Function statement at the bottom of the procedure, add whatever label you specified in your On Error statement. It's rarely possible to anticipate every conceivable error message. For starters, you can just have the error handler display the error number and description in a standard message box. Here's the basic skeleton of what the starting error-handling code might look like in a Sub procedure (where the main body of code represents any number of VBA statements that define what the procedure does normally):

Sub anySub()

On Error GoTo MyErrorHandler

Main body of code Main body of code Main body of code

Exit Sub MyErrorHandler:

Dim Msg As String

Msg = Err.Number & ":" & Err.Description MsgBox Msg

End Sub

The same skeleton structure works in a function. You just have to replace the Exit Sub with Exit Function, as follows:

Function anyFunction()

On Error GoTo MyErrorHandler

Main body of code Main body of code

Main body of code

Exit Function MyErrorHandler:

Dim Msg As String

Msg = Err.Number & ":" & Err.Description MsgBox Msg

End Function

Take a look now at how even a simple generic handler like the preceding example can be adapted to deal with unexpected runtime errors. Suppose that somewhere in the main body of code is a statement that attempts to write some data from a query to an HTML file on a floppy disk. For instance, the following VBA statement copies data from a query named OrderSummary to a file named Order Summary.html on a floppy disk:

DoCmd.OutputTo acOutputQuery, "OrderSummary", _ acFormatHTML, "A:\Order Summary.html"

Figure 12-15 shows that code added to the main body of the basic skeletal structure for error handling. Once again, ...Main body of code... refers to any other numbers of statements in the same procedure.

Figure 12-15:

Statement writes a file to a floppy.

Figure 12-15 shows that code added to the main body of the basic skeletal structure for error handling. Once again, ...Main body of code... refers to any other numbers of statements in the same procedure.

Suppose you run the procedure without a floppy disk in the floppy drive. Naturally, when VBA tries to execute the statement that tries to write to a floppy disk, the empty floppy drive is going to cause a major environmental problem. That problem, in turn, is going to raise an error message. However, by the time the DoCmd.OutputTo... statement executes, the On Error GoTo MyErrorHandler code has already been executed. So rather than just crash at the DoCmd.OutputTo... statement, execution gets passed down to the MyErrorHandler label.

The MyErrorHandler code then creates a little message string from the Number and Description properties of the Err object. That message is then displayed onscreen, as in Figure 12-16.

As a programmer, you've just learned something very useful about your procedure. When a user tries to run this procedure without a floppy disk in the drive, Access raises Err.Number 2302 (the number at the start of the message). As a programmer, you also don't care about anything else, other than finding some graceful way of handling this situation that doesn't leave the poor user at a complete loss as to what to do next. As a programmer, you click OK and get back to doing what programmers do — writing code.

Figure 12-16:

Custom error message for the missing floppy (error 2302).

Microsoft Office Access £J

2302: Microsoft Office Access ca

nt save the ou Oh

:put data to the file youVe selected. 1

You also know that when any error occurs in your code, execution will always transfer to the MyErrorHandler label. To trap that 2302 error, you can place an If...End If statement right there under the label that reads, If the error that got us here was error number 2302, then... (handle it this way). Here I'll start (as I always do in real life) just by typing the If...End If lines for trapping error 2302. These need to be inserted just under the label MyErrorHandler:, as shown in boldface in the following. (I've also added a couple of comments to the code.)

'Everything below here is Error

handler stuff.

MyErrorHandler:

'Trap "missing floppy" error

(2302).

If Err.Number = 2302 Then

'Deal with missing floppy

problem.

End If

'Just show error number and

description, then end Sub.

Dim Msg As String

Msg = Err.Number & ":" & Err

.Description

MsgBox Msg

End Sub

So now you have an If...End If block of code in your handler that can deal specifically with error 2302 when it arises. I suppose the smart thing to do would be to show a message that tells the user to put a floppy in the floppy drive, click an OK button, and let the code take another shot at copying to the floppy. The following code sample shows the appropriate code added between the If...End If statements for error 2302:

'Everything below here is Error handler stuff. MyErrorHandler:

'Trap "missing floppy" error (2302). If Err.Number = 2302 Then

'Deal with missing floppy problem. 'Tell user what to do.

Dim ErrMsg As String

ErrMsg = "Please put a floppy disk in drive A:." ErrMsg = ErrMsg + " Then click OK. " MsgBox ErrMsg

'Re-execute line that copies to floppy. Resume End If

'Just show error number and description, then end Sub. Dim Msg As String

Msg = Err.Number & ":" & Err.Description MsgBox Msg

End Sub

Notice how there's now a block of code that's executed if (and only if) error number 2302 is raised (If Err.Number = 2302 Then...End If). Within that block of code is more code written specifically to handle that error. If the user runs the procedure without a floppy in the drive, the user first sees the message box defined in the code, which looks like Figure 12-17 on the user's screen.

For starters, your custom error-handler has replaced the generic error message

Microsoft Office Access can't save the output data to the file you've selected to a very specific instruction telling the user exactly what to do. That's because the message being displayed now is the one defined by these lines of code within the If Err.Number = 2302 Then...End If block:

ErrMsg = "Please put a floppy disk in drive A:." ErrMsg = ErrMsg + " Then click OK. " MsgBox ErrMsg

When the MsgBox ErrMsg statement executes and displays the message onscreen, code execution halts until the user clicks OK. (Not because of anything special I did but rather because that's how MsgBox... statements always execute.) When the user does as instructed and clicks OK, the next statement executed is

Resume

That statement forces execution to try the error-generating line again (DoCmd. OutputTo...). This time, because there is a floppy disk in the drive, the statement will run just fine. Code execution will then resume normally under that line, and everything will be just as though the error never occurred.

And that's what handling runtime errors gracefully is all about. By adding a general error-handler to the code, you're able to trap — and take a look at — whatever runtime errors that particular procedure might generate. When you find a specific runtime error, find some way of handling it that allows the code to keep running and keep working rather than just leaving the user staring dumbfounded at the screen.

Part V

0 0

Post a comment