[VBA] How to Make Function Arguments Optional (The Optional Keyword)

Introduction

When creating custom VBA functions, you may want to make them more flexible by allowing certain arguments to be omitted. For example, you might have a value that is usually the same (a default), but you want the option to change it occasionally.

In VBA, you can define an “optional argument” by adding the Optional keyword when declaring the argument. Furthermore, by specifying a default value, VBA will automatically use that value if the argument is omitted.

In this article, I will explain how to create convenient functions with optional arguments using the Optional keyword.

Sample Code: Function with Optional Arguments

In this example, we will create a function called CreateGreeting that generates a greeting message. We will make the title (e.g., “Mr.”) the default, but allow it to be changed to something else (e.g., “Dr.”) if needed.

Completed Code

' --- Function with an optional argument ---
' The 2nd argument (title) is optional
Function CreateGreeting(personName As String, Optional title As String = "Mr.") As String
    ' Create and return a greeting using the passed arguments
    ' Note: We changed the order to fit English grammar (Title + Name)
    CreateGreeting = "Hello, " & title & " " & personName & "."
End Function


' --- Sub procedure to call the created Function ---
Sub Test_CreateGreeting()
    Dim message1 As String
    Dim message2 As String

    ' --- Call by omitting the 2nd argument ---
    ' Since it is omitted, the default value "Mr." is used
    message1 = CreateGreeting("Smith")
    
    ' --- Call by specifying all arguments ---
    ' The specified "Dr." is used instead of "Mr."
    message2 = CreateGreeting("Jones", "Dr.")
    
    ' --- Display the results in a message box ---
    MsgBox message1 & vbCrLf & message2, vbInformation, "Greeting Message"
    
End Sub

Execution Result

When you run Test_CreateGreeting, the following message box will appear:

Hello, Mr. Smith.
Hello, Dr. Jones.

Code Explanation

Function CreateGreeting(..., Optional title As String = "Mr.")

This is the part where the optional argument is defined.

• Optional: Indicates that the argument title can be omitted.
• = "Mr.": Specifies the default value to use if the argument is omitted. It is standard practice to set a default value when using the Optional keyword.

Important Rules:

1. Optional arguments must always be placed at the end of the argument list. You cannot place an optional argument between required arguments.
2. It is possible to use Optional without a default value, but then you must use the IsMissing function to check if the argument was passed, which makes the code more complex.

message1 = CreateGreeting("Smith")

Here, we call the CreateGreeting function with only the first argument. Since the second argument title is Optional, it can be omitted. In this case, the default value "Mr." is automatically used.

message2 = CreateGreeting("Jones", "Dr.")

Here, we call the function including the second argument. In this case, the default value is ignored, and the specified value "Dr." is used for the title.

Summary

In this article, I explained how to create functions with omittable arguments using the VBA Optional keyword.

• Adding the Optional keyword to an argument declaration makes it optional.
• It is common to specify a default value, like Optional ArgName As Type = DefaultValue.
• Optional arguments must always be placed at the far right (end) of the argument list.

By using this technique, you can design flexible procedures that are simple to use for standard tasks but capable of handling advanced requirements when needed. Many built-in Excel functions also use optional arguments. Please master this to create better custom functions.