Giving a Function a Data Type

Implementing Error Handling

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:

• The On Error Goto HandleErr statement tells VBA that if any error occurs, execution should continue at the point marked HandleErr .
  
  
• ExitHere and HandleErr are labels , which are places within a procedure where execution can continue.
  
  
• The Exit Function statement tells VBA to quit running the function without executing any more code.
  
  
• Err.Description is a special variable that contains the description of the most recent error.
  
  
• Resume ExitHere is an instruction to clear the error and resume executing code at the indicated label.
  
  

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.

There are two other statements that you might use after an error has occurred:

• The Resume statement tells VBA to begin executing the code again, starting at the statement that caused the error. You can use this if a transient error occurs that you can correct in your error-handling code.

• The Resume Next statement tells VBA to begin executing the code again, starting at the statement after the statement that caused the error. You can use this if you decide that a particular error is harmless enough to ignore.