Debugging Functions

When you're using a formula in a worksheet to test a Function procedure, VBA runtime errors do not appear in the all-too-familiar, pop-up error box. If an error occurs, the formula simply returns an error value (#VALUE!). Luckily, this does not present a problem for debugging functions because you have several possible workarounds:

• Place MsgBox functions at strategic locations to monitor the value of specific variables . Message boxes in Function procedures do pop up when the procedure is executed. But make sure that you have only one formula in the worksheet that uses your function, or message boxes will appear for each formula that is evaluated, which is a repetition that will quickly become annoying.

• Test the procedure by calling it from a Sub procedure, not from a worksheet formula. Runtime errors are displayed in the usual manner, and you can either fix the problem (if you know it) or jump right into the Debugger.

• Set a breakpoint in the function and then step through the function. You then can access all the standard VBA debugging tools. To set a breakpoint, move the cursor to the statement at which you want to pause execution and then choose Debug Toggle Breakpoint (or press F9). When the function is executing, press F8 to step through the procedure line-by-line .

• Use one or more temporary Debug.Print statements in your code to write values to the VBE Immediate window. For example, if you want to monitor a value inside of a loop, use something like the following routine:

 Function VowelCount(r) As Long     Dim Count As Long     Dim i As Long     Dim Ch As String * 1     Count = 0     For i = 1 To Len(r)         Ch = UCase(Mid(r, i, 1))         If Ch Like "[AEIOU]" Then            Count = Count + 1            Debug.Print Ch, i         End If     Next i     VowelCount = Count End Function 

In this case, the values of two variables, Ch and i , are printed to the Immediate window whenever the Debug.Print statement is encountered . Figure 10-7 shows the result when the function has an argument of Tucson Arizona .

Figure 10-7: Use the Immediate window to display results while a function is running.