VBA Optional Parameters: Syntax and Examples

Yes. In VBA, add the Optional keyword to a procedure parameter and, for most typed parameters, provide a default value; callers can then leave that argument out. Optional parameters must come after required parameters, and every parameter after the first optional one must also be optional.

Basic syntax and a working example

The general declaration pattern is Optional [ByVal|ByRef] name As type = defaultValue. For example, this function uses a 10% discount when the caller supplies only a price, but lets the caller specify a different rate:

Public Function PriceAfterDiscount(ByVal Price As Currency, Optional ByVal DiscountRate As Double = 0.1) As Currency
    ' Apply the supplied rate or the default 10% rate.
    PriceAfterDiscount = Price * (1 - DiscountRate)
End Function

In a standard VBA call, PriceAfterDiscount(100) returns 90, while PriceAfterDiscount(100, 0.2) returns 80. The first argument is required; the second is optional. The percent is represented as a decimal, so pass 0.2 for 20%, not 20.

VBA permits an optional parameter to be declared ByVal or ByRef, just like other parameters. Choose the passing mode based on whether the procedure should be able to change the caller’s variable; making a parameter optional does not itself determine that behavior.

How default values work

For a typed optional argument, the default is used whenever the caller omits that argument. The default should be a constant value suitable for the declared type, such as a number, a string literal, or a Boolean value. A default is part of the procedure declaration, not an instruction to calculate a fresh value at call time. You cannot use a changing expression such as Now as a default; instead, declare the argument optional and choose a value inside the procedure.

For example, a greeting can use a fixed default salutation:

Public Function MakeGreeting(ByVal Person As String, Optional ByVal Salutation As String = "Hello") As String
    ' Use the supplied salutation or the declared default.
    MakeGreeting = Salutation & ", " & Person
End Function

MakeGreeting("Mina") returns Hello, Mina; MakeGreeting("Mina", "Welcome") returns Welcome, Mina. The declared default is easy to see when reading the function, which makes the behavior of a one-argument call predictable.

Be deliberate when choosing a default. If zero, an empty string, or False is a meaningful value supplied by the caller, it should not also be used to mean “not supplied” unless that ambiguity is acceptable. A default can hide a caller’s omission by design, but typed parameters generally do not preserve a separate indication that the argument was omitted.

Optional Variant arguments and IsMissing

A Variant optional parameter can be declared without an explicit default. This is useful when the procedure must distinguish an omitted argument from an argument that was supplied with a value such as Empty. Use VBA’s IsMissing function for that test:

Public Function GreetingWithChoice(ByVal Person As String, Optional ByVal Salutation As Variant) As String
    ' IsMissing detects omission for an optional Variant argument.
    If IsMissing(Salutation) Then
        GreetingWithChoice = "Hello, " & Person
    Else
        GreetingWithChoice = CStr(Salutation) & " " & Person
    End If
End Function

GreetingWithChoice("Mina") takes the first branch. If the caller explicitly supplies a value, the second branch runs. In particular, passing Empty is not the same as leaving the argument out: IsMissing reports whether the argument was omitted, not whether its value looks blank.

IsMissing is specifically useful with an optional Variant; it cannot reveal omission for a normal typed optional parameter such as Double or String. With a typed parameter, VBA supplies its declared default, or the type’s default when applicable, and the procedure cannot reliably tell whether the caller omitted the argument or explicitly passed that same value. If the difference matters, use an optional Variant and test for omission, or choose a separate explicit flag design.

Calling procedures and skipping optional arguments

VBA callers can pass optional values positionally or use named arguments. Position-based calls are concise when supplying arguments in order. Named arguments make longer calls easier to understand and allow a caller to omit an optional argument without having to supply every earlier optional one.

For the discount function, these are equivalent ways to supply a custom rate:

Dim total As Currency

'the second argument is DiscountRate
total = PriceAfterDiscount(100, 0.2)

'use names to make the argument roles explicit
total = PriceAfterDiscount(Price:=100, DiscountRate:=0.2)

In VBA, named arguments use the parameter name followed by :=. The names must match the procedure declaration. They are particularly helpful when several optional parameters have similar types, because a call such as Configure(True, False) may be hard to interpret later.

If a procedure has multiple optional parameters and you want to provide a later positional argument while leaving an earlier one out, an empty position can be represented by a comma placeholder. That syntax is less readable than using named arguments, so use names when skipping an argument or when the call would otherwise be ambiguous. Remember that ordinary positional arguments cannot be rearranged: their order is determined by the declaration.

Using optional parameters in an Excel worksheet function

A VBA function in a standard module can also serve as a user-defined function (UDF) in an Excel formula, provided the workbook and macro settings allow VBA code to run. For example, the discount function can be used in a cell as =PriceAfterDiscount(A2) to apply its default rate, or as =PriceAfterDiscount(A2,0.2) to use 20%. This is useful when most rows share one assumption but some formulas need a different value.

To create the function in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and place the function in that standard module. On a Mac, the Visual Basic Editor can be opened with Option+F11 on keyboards that support the function-key shortcut; otherwise use the Developer tab’s Visual Basic command. Ribbon availability can depend on whether the Developer tab is enabled.

Save a workbook containing VBA in a macro-enabled format such as .xlsm, and allow macros only when you trust the file and its source. Excel for the web cannot create, run, or edit VBA macros, so a VBA UDF is not a web-based substitute for a built-in worksheet function. A UDF used in a cell returns a value; it should not be designed to change other cells as a side effect.

If you put a UDF into a worksheet formula, keep its optional arguments understandable to spreadsheet users. A clear default can make formulas shorter, but it may also conceal an assumption. If a rate or other setting is important to review, placing it in a worksheet cell and passing that cell as an argument can be more transparent than relying on a value embedded in VBA.

Choosing between a default and detecting omission

Use a typed optional argument with a default when omitted input should have one clear, ordinary meaning. This is usually the simplest design for settings such as a rounding precision, a display label, or a standard rate. The procedure declaration documents the behavior, and callers do not need to know about a special missing-value check.

Use an optional Variant with IsMissing when omission itself carries information. For instance, a report routine might use one behavior when a title is not supplied and a different behavior when the caller explicitly supplies an empty title. Before choosing this pattern, consider whether separate procedures or a clearly named Boolean switch would make the interface easier to use.

Choose a default when most callers should receive the same fallback behavior.
Use IsMissing when “not provided” must differ from a supplied value.
Prefer named arguments when a call skips optional settings or has several arguments.
Make defaults visible and documented when they affect a calculation or business rule.

A default is also a compatibility decision. If existing calls omit a parameter, changing its default changes what those calls do, even though their code remains unchanged. If the procedure is used widely in a workbook or project, review callers before changing a default and consider whether the new behavior should instead be introduced through a new procedure or an explicit argument.

Common mistakes and limitations

The most common declaration error is putting a required parameter after an optional one. This is not valid:

'Not valid: a required argument follows an optional argument.
'Public Function BadExample(Optional ByVal Mode As String = "Basic", ByVal Count As Long) As Long

Move required inputs to the front, or make every parameter after the first optional parameter optional. Do not confuse an optional argument with a nullable value: omission, Empty, Null, zero, False, and an empty string have different meanings in VBA. The appropriate check depends on the argument’s type and the behavior the procedure promises.

Another frequent misunderstanding is that every omitted optional argument can be detected. That is not true for a typed parameter with a default: after VBA supplies the default, the procedure sees the value, not the caller’s intention. Nor can an optional parameter be used to create overloaded functions in the way some programming languages allow. VBA does not select between same-named procedures based on different argument lists; use distinct procedure names or one procedure with a carefully designed optional interface.

When passing a possibly omitted argument through a wrapper procedure, take care not to lose the distinction between omitted and explicitly supplied. If the wrapper’s own optional Variant is missing, test it with IsMissing and call the downstream procedure appropriately. Simply forwarding a value can turn the situation into a supplied argument, so the receiving procedure may no longer be able to tell that the original caller omitted it.

Finally, saving changes to code and running code are different risks. Save a copy of the workbook before running a macro that changes workbook content: VBA macros cannot be undone with Ctrl+Z (or Command+Z on Mac). The example functions above only calculate and return values, but the same caution matters when testing procedures that edit cells, sheets, or files.

Frequently Asked Questions

Can a VBA function have more than one optional parameter?

Yes. Declare each optional parameter after all required parameters. Every parameter following the first optional parameter must also be optional, and each typed optional argument needs an appropriate default.

Can I use Optional without a default value in VBA?

Yes, an optional Variant can be declared without an explicit default. Use IsMissing to check whether the caller omitted it. For most non-Variant parameter types, provide a default value in the declaration.

How do I check whether an optional argument was omitted?

Declare it as an optional Variant without a default and test it with IsMissing(argumentName). That check identifies omission; it does not treat an explicitly supplied Empty value as missing.

Can I skip an optional argument and provide the next one?

Yes. In VBA, use a comma placeholder in a positional call, or use named arguments with :=. Named arguments are usually clearer because they identify the value being supplied.

Do VBA optional parameters work in Excel cell formulas?

They can be used in formulas that call a VBA UDF, such as =PriceAfterDiscount(A2) or =PriceAfterDiscount(A2,0.2). The workbook must support and permit VBA, and Excel for the web does not run VBA UDFs.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.