Microsoft Office Tutorials and References
In Depth Information
Function Examples
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’s 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:
=User()
=User(False)
=User(True)
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. In other
words, the argument for the IsMissing function must always be a Variant data type.
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 isn’t
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.
 
Search JabSto ::




Custom Search