Microsoft Office Tutorials and References
In Depth Information
USER = Application.UserName
End Function
Suppose that in some cases, you want the user's name to be returned in uppercase letters. The following func-
tion uses an optional argument:
Function USER(Optional UpperCase As Variant) As String
If IsMissing(UpperCase) Then UpperCase = False
If UpperCase = True Then
USER = Ucase(Application.UserName)
Else
USER = Application.UserName
End If
End Function
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.
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 converts 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).
Optional arguments also allow you to specify a default value in the declaration, rather than testing it with the
IsMissing function. The preceding function can be rewritten in this alternate syntax as
Function USER(Optional UpperCase As Boolean = False) As String
If UpperCase = True Then
USER = UCase(Application.UserName)
Else
USER = Application.UserName
End If
End Function
If no argument is supplied, UpperCase is automatically assigned a value of FALSE. This has the advantage of
allowing you type the argument appropriately instead of with the generic Variant data type. If you use this meth-
od, however, there is no way to tell whether the user omitted the argument or supplied the default argument.
All the following formulas are valid in either syntax (and the first two have the same effect):
=USER()
Search JabSto ::




Custom Search