Microsoft Office Tutorials and References
In Depth Information
If the m or d argument exceeds a valid number, it rolls over into the next year or month. For
example, if you specify a month of 13, it’s interpreted as January of the next year.
The VBA code for the Extended Data functions is available on the companion CD-ROM.
The filename is extended date function.xlsm . The CD also contains some
documentation for these functions in a Word document named extended date
functions help.docx .
When you’re using a formula in a worksheet to test a Function procedure, VBA runtime errors
don’t appear in the all-too-familiar, pop-up error box. If an error occurs, the formula simply
returns an error value ( #VALUE! ). Luckily, the lack of a pop-up error message doesn’t present a
problem for debugging functions because you have several possible workarounds:
h 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.
h 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.
h 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
h 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