Microsoft Office Tutorials and References
In Depth Information
6.3.2 Named Arguments
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)
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
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.
6.3.2 Named Arguments
Some VBA procedures can contain a large number of parameters. For example, one form of the
Excel SaveAs function has the declaration:
SaveAs (Filename As string, FileFormat As VARIANT, Password As VARIANT, _
WriteResPassword As VARIANT, ReadOnlyRecommended As VARIANT, _
CreateBackup As VARIANT, AddToMru As VARIANT, TextCodepage As _
VARIANT, TextVisualLayout As VARIANT)
where all of the parameters are optional. Here is an example of a call to this procedure:
SaveAs "c:\temp\test.xls", , , , , True , , , True
Not very readable, is it?
The arguments shown in the previous call are said to be positional arguments because it is their
position that tells VBA which parameters they are intended to replace. This is why we need to
include space for missing arguments.
Search JabSto ::

Custom Search