A function with optional arguments

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


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

The custom functions that you develop in VBA also can have optional arguments. You specify an optional argument by preceding the argument's name with the keyword Optional. In the argument list, optional arguments must appear after any required arguments.

Following is a simple function example that returns the user's name. The function's argument is optional.

Function User(Optional UpperCase As Variant)

If IsMissing(UpperCase) Then UpperCase = False User = Application.UserName If UpperCase Then User = UCase(User) End Function

If the argument is False or omitted, the user's name is returned without any changes. If the argument is True, the user's name is converted to uppercase (using the VBA UCase function) before it is returned. Notice that the first statement in the procedure uses the VBA IsMissing function to determine whether the argument was supplied. If the argument is missing, the statement sets the UpperCase variable to False (the default value).

All the following formulas are valid, and the first two produce the same result:



Note If you need to determine whether an optional argument was passed to a function, you must declare the optional argument as a Variant data type. Then you can use the IsMissing function within the procedure, as demonstrated in this example.

The following is another example of a custom function that uses an optional argument. This function randomly chooses one cell from an input range and returns that cell's contents. If the second argument is True, the selected value changes whenever the worksheet is recalculated (that is, the function is made volatile). If the second argument is False (or omitted), the function is not recalculated unless one of the cells in the input range is modified.

Function DrawOne(Rng As Variant, Optional Recalc As Variant = False) ' Chooses one cell at random from a range

' Make function volatile if Recalc is True Application.Volatile Recalc

' Determine a random cell

DrawOne = Rng(Int((Rng.Count) * Rnd + 1)) End Function

Notice that the second argument for DrawOne includes the Optional keyword, along with a default value.

All the following formulas are valid, and the first two have the same effect:

=DrawOne(A1 =DrawOne(A1 =DrawOne(A1

:A100,False) :A100,True)

This function might be useful for choosing lottery numbers, picking a winner from a list of names, and so on.

CD- This function is available on the companion CD-ROM. The filename is ® draw. xlsm. ROM

0 0

Post a comment