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

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.

f" U««-> ■rt-.l hxu

JJ Urn idn VnK Inuit H*


M«1 P- li«* A"»" Hi«*™

■r , m r.

& 1 - A ft ■»

■ t < i' f - Lull til»



.Wrfl J 1»-T—I

J i

Opts.» Exp j ¡.-n.".

■ jfi .tVll-h IhiM]

JTH-^.-i - 41 b v!

Eh - W»»«<H:.cMr, i, 1»! If Ch LUrn ■■fcEJCTi' Tl»a Ccusx. ■ r.:_ ;- ■ 3<bs4>rcixE Cb«. i IT

Mnrl i,

^Ml&fatT - ilrif iu liiilLiii

x ■ Vcvti'Zc-air hJUncau" i iaS jx



• ¿J_| ¿j


fr d i

i «

e i



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


0 0

Post a comment