VBA Optional Parameter in Excel — Defaults, IsMissing, Named Arguments and ParamArray

TL;DR — Optional lets the caller leave an argument out: Sub Export(ByVal path As String, Optional ByVal addDate As Boolean = True) can be called as Export "C:\Out" or Export "C:\Out", False. Give every typed Optional a default and you are done. The one rule to know: a typed Optional cannot tell "not passed" from "passed the default", and IsMissing only works on a Variant with no default. Use a default when leaving the argument out means the same as passing that value; use a Variant and IsMissing only when leaving it out means something no value can say. And when a procedure needs a new input, add it as an Optional at the end, with the old behavior as its default, so every existing caller keeps working.

Sub ExportReport(ByVal folder As String, Optional ByVal addDate As Boolean = True)
    Dim fileName As String
    fileName = "Report"
    If addDate Then fileName = fileName & " " & Format(Date, "yyyy-mm-dd")
    Debug.Print folder & "\" & fileName & ".pdf"
End Sub

' ExportReport "C:\Out"             -> C:\Out\Report 2026-10-01.pdf
' ExportReport "C:\Out", False      -> C:\Out\Report.pdf

This closes a three-part cluster on where a value lives. A global variable is shared by the whole project until it resets; a Static variable is one procedure's private memory. An Optional parameter is the narrowest of all: the caller hands the value in for one call, or lets the default stand in. It is also the easiest to trust, because you can see where the value came from by reading the call.

What you'll learn

The mental model: an Optional is a blank on a form with a default printed in it
The declaration rules, and why the default must be a constant
The one rule: a typed Optional cannot tell omitted from default
IsMissing with a Variant, and when it is the right choice
Skipping arguments, and why named arguments make parameter names permanent
ParamArray for any number of values, and the forwarding trap
Optional as the safe way to change a procedure everyone already calls

The mental model: a form field with a default printed in it

A required parameter is a blank the caller must fill in. An Optional parameter is a blank with a value already printed in it: the caller can write over it or leave it alone. The procedure always receives a value either way. That picture explains every rule that follows: the printed value must be known before anyone fills in the form, it must come after the fields that are required, and once it is printed, the procedure can no longer tell whether the caller left it alone or wrote the same thing over it.

Declaring Optional parameters: the rules

Function Discount(ByVal amount As Double, _
                  Optional ByVal rate As Double = 0.1, _
                  Optional ByVal roundTo As Long = 2) As Double
    Discount = Round(amount * (1 - rate), roundTo)
End Function

Optional goes before ByVal or ByRef, and the default goes after the type: = 0.1.
Every Optional comes after every required parameter. Once one parameter is Optional, all the parameters after it must be Optional too.
The default must be a constant expression: a number, a string, True, a constant you declared. Optional asOf As Date = Date is a compile error, because Date is a function that runs later. Leave the default out and fill it in at the top of the procedure instead:

Sub Snapshot(Optional ByVal asOf As Date)
    If asOf = 0 Then asOf = Date     ' 0 means not given - today's date then
    Debug.Print Format(asOf, "yyyy-mm-dd")
End Sub

An object parameter without a default arrives as Nothing, which makes "use the active sheet unless told otherwise" easy: Optional ws As Worksheet, then If ws Is Nothing Then Set ws = ActiveSheet.

In a worksheet function, Optional parameters behave the same way: =Discount(A2) uses both defaults, and =Discount(A2, 0.2) overrides one. The Function guide covers the rest of UDF design.

The one rule: a typed Optional cannot tell omitted from default

Look at Snapshot again. If the caller passes nothing, asOf is 0. If the caller passes 0 on purpose, asOf is also 0. The procedure cannot tell them apart, and that is true of every typed Optional: a Long left out is 0, a String left out is "", a Boolean left out is False, or the default you declared.

Most of the time that is exactly what you want. "Leave it out" and "pass the default" should mean the same thing, and a default makes the procedure's behavior obvious from its first line. The problem comes when the caller leaving it out has to mean something that no value of the type can express. A typical case is a filter that defaults to "no filter", where 0 and "" are real values someone might want to filter on.

IsMissing: when leaving it out must mean something

For that case, declare the parameter as a Variant with no default, and ask IsMissing:

Function CountWhere(rng As Range, Optional ByVal match As Variant) As Long
    Dim c As Range, n As Long
    For Each c In rng
        If IsMissing(match) Then
            If Not IsEmpty(c.Value) Then n = n + 1   ' no filter: count filled cells
        ElseIf c.Value = match Then
            n = n + 1                                ' filter, even on 0 or ""
        End If
    Next c
    CountWhere = n
End Function

IsMissing has two conditions, and breaking either one makes it quietly return False for ever:

The parameter must be a Variant. On a Long or a String, IsMissing is always False.
The parameter must have no default. Optional match As Variant = "" is never missing, because the default fills it in.

My judgment: reach for IsMissing only when absence is a real third state. If you are using it just to fill in a default at the top of the procedure, declare the typed default instead; the signature then documents the behavior and the compiler checks the type.

Skipping arguments, and named arguments

To skip an Optional in the middle, leave its slot empty, or name the argument you do pass:

Debug.Print Discount(200, , 0)            ' default rate, round to 0 places
Debug.Print Discount(200, roundTo:=0)     ' the same, and readable
Debug.Print Discount(amount:=200, rate:=0.25)

Named arguments use := and the parameter's name, in any order. They are the readable choice once a procedure has more than one Optional, because , , 0 says nothing about which value is which. They have one consequence people miss: once any caller uses roundTo:=, the parameter name is part of the interface. Renaming roundTo to decimals compiles fine in the procedure and breaks every caller that named it, with "Named argument not found". Choose Optional names as carefully as procedure names.

ParamArray: any number of values

When the caller should pass as many values as they like, use ParamArray. It must be the last parameter, it must be an array of Variant, and it cannot be combined with Optional in the same procedure:

Function SumAll(ParamArray values() As Variant) As Double
    Dim i As Long, total As Double
    For i = LBound(values) To UBound(values)
        total = total + values(i)
    Next i
    SumAll = total
End Function

' SumAll(1, 2, 3)  -> 6
' SumAll()         -> 0   (UBound is -1, so the loop never runs)

The trap is forwarding. If SumAll wants to hand its values to a second ParamArray procedure, Other values does not spread them out: Other receives one argument, which is the whole array. Write the helper to take an ordinary Variant holding an array instead, and loop over it there. The UBound guide covers the bounds that make empty arrays safe.

The judgment call: Optional is how a procedure grows without breaking

The most useful thing Optional does is not convenience. It is compatibility. Suppose ExportReport folder is called from twelve buttons and three other macros, and now one report needs to skip the date. Changing the signature to a new required parameter breaks fifteen callers. Adding Optional ByVal addDate As Boolean = True breaks none of them, because the default is exactly what they got before, and the one new caller passes False.

My rules:

New inputs go at the end, as Optional, with the old behavior as the default.
Prefer a typed default to IsMissing, unless "not given" is a real third state.
Past two or three Optionals, callers should use named arguments, and it is worth asking whether the procedure is doing two jobs that should be two procedures, in the spirit of the Sub guide.
Never use a global variable to pass a setting into a procedure that could take it as an Optional. The argument is visible in the call; the global is visible nowhere.

How ExcelMaster helps

Optional-parameter bugs hide in the calls: a filter that never applies because IsMissing was asked about a String, a rename that breaks every caller using named arguments, a procedure that grew a required parameter and broke the buttons that call it.

ExcelMaster lets you describe the change, such as "let this export skip the date for the monthly report". It adds the input as an Optional with the old behavior as its default, so existing callers keep working, and checks every call site in the project.

Frequently asked questions

How do I make a parameter optional in VBA?

Put Optional before it in the declaration and give it a default: Optional ByVal rate As Double = 0.1. It must come after all required parameters, and any parameter after it must be Optional too. Callers can then leave it out, and the procedure receives the default.

Why does IsMissing always return False?

IsMissing only works when the Optional parameter is a Variant with no default value. For a typed parameter such as Long or String, or a Variant with a default, VBA fills in a value when the argument is left out, so it is never missing.

Can an Optional parameter default to today's date?

Not directly, because a default must be a constant and Date is a function. Declare Optional asOf As Date with no default, then write If asOf = 0 Then asOf = Date at the top of the procedure.

How do I skip an optional argument in the middle?

Leave its position empty with a comma, as in Discount(200, , 0), or use named arguments, as in Discount(200, roundTo:=0). Named arguments are clearer and can be given in any order.

What is ParamArray in VBA?

ParamArray lets a procedure accept any number of arguments as one array of Variants, such as Function SumAll(ParamArray values() As Variant). It must be the last parameter and cannot be used together with Optional parameters. Loop over it with LBound and UBound; when no arguments are passed, UBound is -1.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-10-01.