Optional Arguments

In VBA, the arguments to a procedure may be specified as optional, using the Optional keyword. (It makes no sense to say that a parameter is optional; it is the value that is optional.) To illustrate, consider the procedure in Example 6-3, which simply changes the font name and font size of the current selection:

Example 6-3. Using an Optional Argument

Sub ChangeFormatting(FontName As String,

Optional FontSize As Variant)

' Change font name Selection.Font.Name = FontName

' Change font size if argument is supplied If Not IsMissing(FontSize) Then

Selection.Font.Size = Clnt(FontSize) End If End Sub

The second parameter is declared with the Optional keyword. Because of this, we may call the procedure with or without an argument for this parameter, as in:

ChangeFormatting("Arial Narrow", 24) and:

ChangeFormatting("Arial Narrow")

Note that the IsMissing function is used in the body of the procedure to test whether the argument is present. If the argument is present, then the font size is changed. Note also that we declared the FontSize parameter as type Variant because IsMissing works only with parameters of type Variant (unfortunately). Thus, we converted the Variant to type Integer using the CInt function.

A procedure may have any number of optional arguments, but they must all come at the end of the parameter list. Thus, for instance, the following declaration is not legal:

Sub ChangeFormatting(Optional FontName As String, FontSize As Single)

If we omit an optional argument in the middle of a list, we must include an empty space when calling that procedure. For instance, if a procedure is declared as follows:

Sub ChangeFormatting(Optional FontName As String,

Optional FontSize As Single, Optional FontBold as Boolean)

then a call to this procedure to set the font name to Arial and the boldfacing to True would look like:

ChangeFormat "Arial", , True

To avoid confusion, we should point out that some built-in Excel procedures have optional arguments and others do not. Of course, we can't leave out an argument unless the documentation or declaration for the procedure specifically states that it's optional.

0 0


Post a comment