Microsoft Office Tutorials and References
In Depth Information
To force the code in a VBA module to be checked for syntax errors, choose De-
bug Compile xxx (where xxx is the name of your project). Executing this command
highlights the first syntax error, if any exists. Correct the error and issue the command
again until you find all the errors.
An error in code is sometimes called a bug. 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.
While you're testing and debugging a custom function, it's a good idea to use the func-
tion in only one formula in the worksheet. If you use the function in more than one for-
mula, the code is executed for each formula, which will get annoying very quickly!
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 ex-
ample 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
Dim TextLen As Long, i As Long
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 func-
tion again, with the MsgBox statement inserted within the loop:
Function REVERSETEXT(text) As String
Returns its argument, reversed
Dim TextLen As Long, i As Long
Search JabSto ::




Custom Search