Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Naturally, testing and debugging your custom function is an important step that you must take to ensure that it carries out the calculation that you intend. This section describes some debugging techniques that you may find helpful.

VBA code that you write can contain three general types of errors:

• Syntax errors: An error in writing the statement-for example, a misspelled keyword, a missing operator, or mismatched parentheses. The VB Editor lets you know about syntax errors by displaying a pop-up error box. You can't use the function until you correct all syntax errors.

• Runtime errors: Errors that occur as the function executes. For example, attempting to perform a mathematical operation on a string variable generates a runtime error. Unless you spot it beforehand, you won't be aware of a runtime error until it occurs.

• Logical errors: Code that runs but simply returns the wrong result.

An error in code is sometimes called a bug. The process of locating and correcting such an error is debugging.

When you test a Function procedure by using a formula in a worksheet, runtime errors can be difficult to locate because (unlike syntax errors) they don't appear in a pop-up error box. If a runtime error occurs, the formula that uses the function simply returns an error value (#VALUE!). This section describes several approaches to debugging custom functions.

Using the VBA MsgBox Statement

The MsgBox statement, when used in your VBA code, displays a pop-up dialog box. You can use MsgBox statements at strategic locations within your code to monitor the value of specific variables. The following example is a Function procedure that should reverse a text string passed as its argument. For example, passing Hello as the argument should return olleH. If you try to use this function in a formula, however, you will see that it does not work-it contains a logical error:

Function REVERSETEXT(text) As String ' Returns its argument, reversed TextLen = Len(text) For i = TextLen To 1 Step -1 REVERSETEXT = Mid(text, i, 1) & REVERSETEXT Next i End Function

You can insert a temporary MsgBox statement to help you figure out the source of the problem. Here's the function again, with the MsgBox statement inserted within the loop:

Function REVERSETEXT(text) As String ' Returns its argument, reversed TextLen = Len(text) For i = TextLen To 1 Step -1 REVERSETEXT = Mid(text, i, 1) & REVERSETEXT MsgBox REVERSETEXT Next i End Function

When this function is evaluated, a pop-up message box appears, once for each time through the loop. The message box shows the current value of REVERSETEXT. In other words, this technique enables you to monitor the results as the function is executed. Figure 23-6 shows an example.

Figure 23-6: Use a MsgBox statement to monitor the value of a variable as a Function procedure executes.

The information displayed in the series of message boxes shows that the text string is being built within the loop, but the new text is being added to the beginning of the string, not the end. The corrected assignment statement is

REVERSETEXT = REVERSETEXT & Mid(text, i, 1)

When the function is working properly, make sure that you remove all the MsgBox statements. They get very annoying.

To display more than one variable in a message box, you need to concatenate the variables and insert a space character between each variable. The following statement, for example, displays the value of three variables (x, y, and z) in a message box:

MsgBox x & " " & y & " " & z

If you omit the blank space, you can't distinguish the separate values.

Alternatively, you can separate the variable with vbNewLine, which is a constant that inserts a line break. When you execute the following statement, x, y, and z each appear on a separate line in the message box.

MsgBox x & vbNewLine & y & vbNewLine & z

Using Debug.Print Statements in Your Code

If you find that using MsgBox statements is too intrusive, another option is to insert some temporary code that writes values directly to VB Editor Immediate window. (See the sidebar, "Using the Immediate Window.") You use the Debug.Print statement to write the values of selected variables.

For example, if you want to monitor a value inside a loop, use a routine like the following:

Function VOWELCOUNT(r) 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 value of two variables (Ch and i) print to the Immediate window whenever the Debug.Print statement is encountered. Figure 23-7 shows the result when the function has an argument of California.

Figure 23-7: Using the VB Editor Immediate window to display results while a function is running.

When your function is debugged, make sure that you remove the Debug.Print statements.

Calling the Function from a Sub Procedure

Another way to test a Function procedure is to call the function from a Sub procedure. To do this, simply add a temporary Sub procedure to the module and insert a statement that calls your function. This is particularly useful because runtime errors display as they occur.

The following Function procedure contains an error (a runtime error). As I noted previously, the runtime errors don't display when testing a function by using a worksheet formula. Rather, the function simply returns an error (#VALUE!).

The VB Editor Immediate window can be helpful when debugging code. To activate the Immediate window, choose View Immediate Window (or press Ctrl+G).

You can type VBA statements in the Immediate window and see the result immediately. For example, type the following code in the Immediate window and press Enter:

Print Sqr(1156)

The VB Editor prints the result of this square root operation (34). To save a few keystrokes, you can use a single question mark (?) in place of the Print keyword.

The Immediate window is particularly useful for debugging runtime errors when VBA is in break mode. For example, you can use the Immediate window to check the current value for variables, or to check the data type of a variable.

Errors often occur because data is of the wrong type. The following statement, for example, displays the data type of a variable named Counter (which you probably think is an Integer variable).

? TypeName(Counter)

If you discover that Counter is of a data type other than Integer, you may have solved your problem.

You can execute multiple statements in the Immediate window if you separate them with a colon. This line contains three statements:

x=12: y=13 : ? x+y

Most, but not all, statements can be executed in this way.

Function REVERSETEXT(text) As String ' Returns its argument, reversed TextLen = Len(text) For i = TextLen To 1 Step -1 REVERSETEXT = REVERSETEXT And Mid(text, i, 1) Next i End Function

To help identify the source of the runtime error, insert the following Sub procedure:

Sub Test() x = REVERSETEXT("Hello") MsgBox x End Sub

This Sub procedure simply calls the REVERSETEXT function and assigns the result to a variable named x. The MsgBox statement displays the result.

You can execute the Sub procedure directly from the VB Editor. Simply move the cursor anywhere within the procedure and choose Run Run Sub/UserForm (or just press F5). When you execute the Test procedure, you see the error message that is shown in Figure 23-8.

Figure 23-8: A runtime error identified by VBA.

Click the Debug button, and the VB Editor highlights the statement causing the problem (see Figure 23-9). The error message does not tell you how to correct the error, but it does narrow your choices. After you've identified the statement that's causing the error, you can examine it more closely, or you can use the Immediate window (see the sidebar, "Using the Immediate Window") to help locate the exact problem.

Figure 23-9: The highlighted statement has generated a runtime error.

In this case, the problem is the use of the And operator instead of the concatenation operator (&). The correct statement is as follows:

REVERSETEXT = REVERSETEXT & Mid(text, i, 1)

Setting a Breakpoint in the Function

Another debugging option is to set a breakpoint in your code. Execution pauses when VBA encounters a breakpoint. You can then use the Immediate window to check the values of your variables, or you can use F8 to step through your code line by line.

To set a breakpoint, move the cursor to the statement at which you want to pause execution and choose Debug Toggle Breakpoint. Alternatively, you can press F9, or you can click the vertical bar to the left of the code window. Any of these actions highlights the statement to remind you that a breakpoint is in effect (you also see a dot in the code window margin). You can set any number of breakpoints in your code. To remove a breakpoint, move the cursor to the statement and press F9. Figure 23-10 shows a Function procedure that contains a breakpoint.

Figure 23-10: The highlighted statement contains a breakpoint.