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 like Long, Boolean, String will always have IsMissing return False, this is because for simple types, a default value is already supplied (for Long, it’s 0, Boolean is False, 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.

Does this article help you? If so, please consider supporting me with a coffee ☕️

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.