VBA Optional Arguments and Default Values
September 04, 2021
When creating your VBA subs and functions, you may come across a time where you want to have an optional parameter - something that the calling procedure doesn’t have to specify, but can if they like. You may also want to have that optional argument have a default value in case nothing is supplied. So how do we do this? It’s easier than you think.
Here’s an example of a function that takes a name, a salutation, and outputs a greeting:
Public Sub Greeting()
Debug.Print CreateGreeting("Jane", "Hey") ' prints: Hey, Jane!
Debug.Print CreateGreeting("Jack", "Hi") ' prints: Hi, Jack!
End Sub
Public Function CreateGreeting(ByRef name As String, ByRef salutation As String) As String
CreateGreeting = salutation & ", " & name & "!"
End Function
Let’s make the salutation
part optional:
Public Function CreateGreeting(ByRef name As String, Optional ByRef salutation As String) As String
CreateGreeting = salutation & ", " & name & "!"
End Function
All we did was add the Optional
keyword at the beginning of the parameter definition.
So what happens if we don’t pass anything to it?
Public Sub Greeting()
Debug.Print CreateGreeting("Jill") ' prints: , Jill!
End Sub
Since we didn’t specify a value for salutation
, the default value for a string was used
(which is a blank string).
Let’s now add a default value for salutation
:
Public Function CreateGreeting( _
ByRef name As String, _
Optional ByRef salutation As String = "Hello") As String
CreateGreeting = salutation & ", " & name & "!"
End Function
Now let’s re-run the function:
Public Sub Greeting()
Debug.Print CreateGreeting("Jill") ' prints: Hello, Jill!
End Sub
Great, it works as expected.
Here’s our full code sample so far:
Public Sub Greeting()
Debug.Print CreateGreeting("Jane", "Hey") ' prints: Hey, Jane!
Debug.Print CreateGreeting("Jack", "Hi") ' prints: Hi, Jack!
Debug.Print CreateGreeting("Jill") ' prints: Hello, Jill!
End Sub
Public Function CreateGreeting( _
ByRef name As String, _
Optional ByRef salutation As String = "Hello") As String
CreateGreeting = salutation & ", " & name & "!"
End Function
List Optional Parameters at the End of the Procedure Definition
Optional parameters must be listed at the end of the procedure definition. This means that the procedure definition will have a set of required parameters, followed by a set of optional ones (or, you can have only optional parameters).
For example, say you have a procedure with 2 required parameters and 2 optional ones:
Public Function MyFunction( _
ByVal required1 As Long, _
ByVal required2 As String, _
Optional myOptionalArg1 As Variant, _
Optional myOptionalArg2 As Variant) As Boolean
' ...
End Function
You cannot have the optional arguments in any position except the end of the definition. For example, this wouldn’t work:
Public Function MyFunction( _
ByVal required1 As Long, _
Optional myOptionalArg1 As Variant, _
ByVal required2 As String, _
Optional myOptionalArg2 As Variant) As Boolean
' ...
End Function
Even though there is an optional argument at the end (myOptionalArg2
),
there is an optional argument before a required one (myOptionalArg1
is before required2
) and that syntax is invalid.
Optional Arguments can be any Type Except a UDT
Optional parameters can be any type except a User Defined Type. If you specify a user defined type, when the code compiles, you will receive this error:
Compile error:
Invalid optional parameter type
Detecting a Missing Parameter
If you want to know if a parameter was passed in by the caller or not,
you can use the IsMissing
function. This will return True
if the parameter was not supplied
to the procedure, but only under certain conditions:
- There cannot be a default value for the parameter in the procedure definition.
- The type for the parameter must be
Variant
. Simple types likeLong
,Boolean
,String
will always haveIsMissing
returnFalse
, this is because for simple types, a default value is already supplied (forLong
, it’s0
,Boolean
isFalse
,String
is""
).
Because we defined our salutation
parameter as a string and we gave it a
default value, our current code will not work:
Public Function CreateGreeting( _
ByRef name As String, _
Optional ByRef salutation As String = "Hello") As String
If IsMissing(salutation) Then
Debug.Print "parameter 'salutation' was not included" ' does not print
End If
CreateGreeting = salutation & ", " & name & "!"
End Function
In order for the IsMissing
function to return True
, we need to
remove the default value and change the type to Variant
:
Public Function CreateGreeting( _
ByRef name As String, _
Optional ByRef salutation As Variant) As String
If IsMissing(salutation) Then
Debug.Print "parameter 'salutation' was not included" ' this will print
End If
CreateGreeting = salutation & ", " & name & "!" ' However, this will cause an error
End Function
Now, salutation
is an undefined Variant
object and IsMissing(salutation)
will
return True
. However, because it’s missing, VBA doesn’t have anything
to refer to when we try to access it, so when we
try to return salutation & ", " & name & "!"
, it will throw
an error.
One way to fix this is to avoid using the salutation
variable when it’s
not supplied:
Public Function CreateGreeting( _
ByRef name As String, _
Optional ByRef salutation As Variant) As String
If IsMissing(salutation) Then
CreateGreeting = name & "!"
Exit Function
End If
CreateGreeting = salutation & ", " & name & "!"
End Function
Public Sub Greeting()
Debug.Print CreateGreeting("Jane", "Hey") ' prints: Hey, Jane!
Debug.Print CreateGreeting("Jack", "Hi") ' prints: Hi, Jack!
Debug.Print CreateGreeting("Jill") ' prints: Jill!
End Sub
Another way to fix it would be to supply a default value later in the code:
Public Function CreateGreeting( _
ByRef name As String, _
Optional ByRef salutation As Variant) As String
If IsMissing(salutation) Then
salutation = "Hello"
End If
CreateGreeting = salutation & ", " & name & "!"
End Function
Public Sub Greeting()
Debug.Print CreateGreeting("Jane", "Hey") ' prints: Hey, Jane!
Debug.Print CreateGreeting("Jack", "Hi") ' prints: Hi, Jack!
Debug.Print CreateGreeting("Jill") ' prints: Hello, Jill!
End Sub
For our example, it would be better to change salutation
to be a String
and supply a default value for it. However, you may have a specific
scenario where you need to know if a parameter was intentionally
left out, in which case, you can use the IsMissing
function
as we did above.
How do I supply one optional parameter out of two?
If you have multiple optional parameters and only want to specify some of them, you can do so using a feature called named arguments. We’ll have another blog post on this topic in more depth, but for now, we’ll explain its usage briefly.
To use named arguments, when calling the procedure, you specify the variable you want to supply a value to by using a colon followed by an equal sign in this format:
Call MyProcedure(myArgumentVariable:=myValue)
Here’s a quick example:
Public Function CreateGreeting( _
ByRef name As String, _
Optional ByRef salutationStart As Variant, _
Optional ByRef salutationEnd As Variant) As String
If IsMissing(salutationStart) And IsMissing(salutationEnd) Then
CreateGreeting = name & "!"
ElseIf IsMissing(salutationStart) Then
CreateGreeting = name & ", " & salutationEnd
ElseIf IsMissing(salutationEnd) Then
CreateGreeting = salutationStart & ", " & name
Else
CreateGreeting = salutationStart & ", " & name & ", " & salutationEnd
End If
End Function
Public Sub Greeting()
Debug.Print CreateGreeting("Jane", "Hey", "and welcome!") ' prints: Hey, Jane, and welcome!
Debug.Print CreateGreeting("Jack", salutationEnd:="greetings!") ' prints: Jack, greetings!
Debug.Print CreateGreeting("Jill") ' prints: Jill!
End Sub
Here, we have two arguments: salutationStart
and salutationEnd
. We made the
type Variant
and left out a default value, allowing us
to make use of the IsMissing
function.
When we call CreateGreeting
with just the name
and salutationEnd
:
CreateGreeting("Jack", salutationEnd:="greetings!")
We are telling VBA that the second parameter we listed is specifically
for the salutationEnd
argument in the procedure definition of CreateGreeting
.
This means that the salutationStart
variable is not supplied
and when we call IsMissing(salutationStart)
it will return True
.
Again, we’ll dive into named arguments in a later post, but for now, this is how you can specify optional arguments when you don’t want to (or can’t) supply them all.