A function with an optional argument

Many Excel built-in worksheet functions use optional arguments. An example is the LEFT function, which returns characters from the left side of a string. Its official syntax follows:


The first argument is required, but the second is optional. If you omit the optional argument, Excel assumes a value of 1. Therefore, the following formulas return the same result:

The custom functions you develop in VBA also can have optional arguments. You specify an optional argument by preceding the argument's name with the keyword Optional, followed by an equal sign and the default value. If the optional argument is missing, the code uses the default value.

Debugging custom functions

Debugging a Function procedure can be a bit more challenging than debugging a Sub procedure. If you develop a function for use in worksheet formulas, you find that an error in the Function procedure simply results in an error display in the formula cell (usually #VALUE!). In other words, you don't receive the normal runtime error message that helps you locate the offending statement.

You can choose among three methods for debugging custom functions:

I Place MsgBox functions at strategic locations to monitor the value of specific variables. Fortunately, message boxes in Function procedures pop up when you execute the procedure. Make sure that only one formula in the worksheet uses your function, or the message boxes appear for each formula that's evaluated — which could get very annoying.

I Test the procedure by calling it from a Sub procedure. Run-time errors appear normally in a pop-up window, and you can either correct the problem (if you know it) or jump right into the debugger.

I Set a breakpoint in the function and then use the Excel debugger to step through the function. You can then access all of the usual debugging tools. Refer to Chapter 13 to find out about the debugger.

The following example shows a custom function using an optional argument:

Function DrawOne(InRange, Optional Recalc =


' Chooses one cell at random from a range

' Make function volatile if Recalc is 1

If Recalc = 1 Then Application.Volatile


' Determine a random cell

DrawOne = InRange(Int((InRange.Count) *

Rnd + 1))

End Function

This function randomly chooses one cell from an input range. The range passed as an argument is actually an array, and the function selects one item from the array at random. If the second argument is 1, the selected value changes whenever the worksheet is recalculated. (The function is made volatile.) If the second argument is 0 (or is omitted), the function is not recalculated unless one of the cells in the input range is modified.

You can use this function for choosing lottery numbers, selecting a winner from a list of names, and so on.

0 0

Post a comment