|< Day Day Up >|
There's a potential problem with the procedure that you just saw: because it uses integers for both input and output, it's possible for it to calculate a value greater than an integer will hold. For example, Figure 4.6 shows the result of trying to call Procedure22 with an input value of 20,000. Because this calculates an output value of 200,000, which is larger than an Integer variable can hold, a VBA overflow error occurs.
Figure 4.6. A procedure with an overflow error.
Errors in your VBA code can cause major problems with your applications. That's because VBA stops running code when it encounters an error. But that's only the default behavior of VBA. VBA also supplies several ways for you to monitor your running code for errors, and to recover from those errors.
Using On Error Resume Next
The simplest way to handle an error in VBA is to tell VBA that you don't want to be bothered. The mechanism for doing this is the On Error Resume Next statement:
Function Procedure23(intInput As Integer) As Integer ' Multiple the input by ten and return it On Error Resume Next Procedure23 = intInput * 10 End Function
If you call Procedure23 with an input that will lead to an overflow (such as 20000), you won't see an error onscreen. Instead, the value zero is returned. That's because the line that calculates the return value still raises an overflow error, but the On Error Resume Next statement tells VBA to ignore the error and proceed to the next line of code in this case, the End Function statement. The function returns zero because that's the default value for an integer that doesn't have anything else assigned to it.
On Error Resume Next takes effect as soon as it is executed, and remains in effect until you tell VBA to remove or change the error handling.
Using On Error Goto
Although On Error Resume Next is easy to use, it might not be the safest way to handle errors. In many situations, you'll find On Error Goto to be a better choice. On Error Goto lets you execute a special section of code when something goes wrong. Here's how you might use it:
Function Procedure24(intInput As Integer) As Integer ' Multiple the input by ten and return it On Error GoTo HandleErr Procedure24 = intInput * 10 ExitHere: Exit Function HandleErr: Debug.Print Err.Description Resume ExitHere End Function
There are several new things to note in this procedure:
If all goes well in this function, it prints 10 times its input and then exits at the Exit Function line. If anything goes wrong, it jumps to the HandleErr label, prints the description of the error, and then exits from the function. Figure 4.7 shows several calls to this function in the Immediate window.
Figure 4.7. Calling a procedure with error trapping.
There are two other statements that you might use after an error has occurred:
|< Day Day Up >|