Excel VBA Optional Arguments: How They Work

VBA & macros · Updated November 21, 2026

Yes—Excel VBA procedures and functions can have optional arguments. Declare one with the Optional keyword; give typed arguments a default value, or use an optional Variant without a default when you need to tell whether the caller left it out.

What an Optional Argument Means

An argument is a value supplied to a procedure or function when it is called. Marking an argument Optional means callers are allowed to leave it out. This is useful when a procedure has a sensible standard behavior but occasionally needs extra input—for example, applying a default tax rate unless the caller specifies another one.

Optional arguments are part of a procedure’s declaration, not a separate kind of function. The same principle applies to a Sub and a Function, whether the procedure is called by another VBA routine or the function is used in an Excel worksheet formula. An optional parameter does not make the procedure itself optional; it only makes that particular input unnecessary.

In VBA, optional arguments must come after all required arguments. Once an argument is marked optional, every argument after it must also be optional. That ordering lets VBA determine which supplied value belongs to which parameter.

Basic Function Syntax and Example

Here is a small function that adds a tax rate to an amount. Its second argument is optional and defaults to 8%:

Public Function AddTax(ByVal Amount As Double, Optional ByVal Rate As Double = 0.08) As Double
    ' Add the supplied rate, or the default rate, to the amount.
    AddTax = Amount * (1 + Rate)
End Function

With the declaration above, a call that supplies only the amount uses the default rate. A call that supplies both values uses the rate provided by the caller. The following procedure demonstrates both cases:

Public Sub ShowTaxExamples()
    Dim standardPrice As Double
    Dim customPrice As Double

    ' Make a copy of the workbook before running a macro that changes data.
    standardPrice = AddTax(100)
    customPrice = AddTax(100, 0.1)

    Debug.Print standardPrice  ' 108
    Debug.Print customPrice    ' 110
End Sub

The arithmetic is straightforward: 100 multiplied by 1.08 is 108, and 100 multiplied by 1.10 is 110. The ByVal keyword means VBA passes the argument value rather than allowing the procedure to change the caller’s variable through that parameter. Optionality and ByVal are separate choices; an optional parameter can also be declared ByRef, which is the default passing mode if neither keyword is written.

VBA macros cannot be reversed with Ctrl+Z. Save a copy of the workbook before running a macro that may edit or delete worksheet data. The sample above only prints results in the Immediate window, but the precaution matters for procedures that make changes.

Choosing a Type and a Default Value

For a typed optional argument such as Double, Long, or String, supply a default value in the declaration. The default should match the argument’s intended role: use a numeric default for a numeric option, for instance, rather than using an arbitrary value that could be mistaken for real input.

A useful default makes the common call concise and predictable. If a formatting function normally uses two decimal places, a default of 2 can be clearer than requiring every caller to specify it. If no single behavior is appropriate, consider whether an optional argument is the right design; making the caller supply a required value may prevent accidental assumptions.

An optional Variant may be declared without a default value. When the caller omits it, VBA treats the argument as missing; in that case, the Variant has the special value Empty. This form is useful when the procedure needs to distinguish “not supplied” from a supplied value such as zero, an empty string, or an explicitly passed Empty.

For most ordinary parameters, prefer a specific type and an explicit default. Use a Variant only when its flexibility is needed, particularly when detecting omission. Variants can hold different kinds of values, so code using them may need extra checks before performing calculations or string operations.

Detecting Whether the Caller Omitted an Argument

A defaulted typed argument normally gives the procedure its default whether the caller omitted the value or explicitly supplied that same value. If the distinction matters, use an optional Variant and the VBA function IsMissing. IsMissing is intended for optional Variant arguments; it cannot reliably identify an omitted optional parameter declared as a specific data type.

Public Function MakeLabel(ByVal Item As String, Optional ByVal Suffix As Variant) As String
    ' Use a standard suffix only when the argument was omitted.
    If IsMissing(Suffix) Then
        MakeLabel = Item & " - standard"
    Else
        MakeLabel = Item & " - " & CStr(Suffix)
    End If
End Function

With this function, MakeLabel("Report") returns Report - standard, while MakeLabel("Report", "draft") returns Report - draft. If a caller supplies an empty string as the second argument, that is still a supplied argument; it is not the same condition as omitting the argument. Add a check for IsEmpty or for a zero-length string if those supplied values need special treatment.

Do not confuse “missing” with “blank” in every context. A caller might explicitly pass Empty, a blank worksheet cell, zero, or an empty string. Those values have different meanings in VBA. Decide which cases your procedure should treat alike, then test for them deliberately rather than assuming IsMissing catches all of them.

Argument Order, Skipping, and Named Arguments

Since optional arguments must follow required ones, callers can usually omit optional arguments at the end of the list without ambiguity. For example, AddTax(100) leaves out the final optional rate. If there are several optional arguments and a caller wants to provide a later one while skipping an earlier one, the skipped position must remain clear. VBA allows omitted positional arguments to be represented by an empty slot in applicable calls, but that syntax can be hard to read.

Named arguments are often easier to understand when a procedure has several options. Suppose a function has optional arguments for a rate and a rounding precision. A caller can identify the value by parameter name using VBA’s named-argument syntax, such as Rate:=0.1, rather than relying on its position. This is especially helpful when reading a call months later or when optional parameters have similar data types.

  • Use positional arguments for short calls where the meaning of each value is obvious.
  • Use named arguments when skipping an earlier option or when a call has several settings.
  • Keep the declaration’s required arguments first and optional arguments afterward.
  • When changing a procedure’s parameter list, check every call site in the workbook, including calls in other modules.

Named arguments do not change the default; they simply make it explicit which parameter receives a supplied value. They can also reduce errors caused by swapping two adjacent values of the same type, such as a minimum and maximum.

Using Optional Arguments in Worksheet Functions

A public VBA function in a standard module can be used as a user-defined function in an Excel worksheet, subject to Excel’s UDF rules. For example, if the code containing AddTax is in a standard module in a macro-enabled workbook, a worksheet formula can call it with the default rate as =AddTax(A2), or supply a rate in another cell with =AddTax(A2,B2). If A2 contains 100, the first formula returns 108 using the example’s 8% default.

Optional arguments are handy for worksheet functions because a formula can remain short when the usual behavior is wanted. Be clear in the function’s name or documentation about what the default means; a rate, unit, or rounding rule that is invisible in the formula can surprise someone maintaining the workbook. Consider putting frequently adjusted assumptions in worksheet cells and passing them explicitly instead of burying them in VBA code.

To use VBA, save the workbook in a macro-enabled format such as .xlsm, and make sure macros are enabled according to your organization’s security rules. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. Excel for Microsoft 365 on Windows supports the VBA editor; Excel for Mac also supports VBA, although the editor interface and some VBA capabilities can differ. A worksheet formula that depends on VBA will not calculate through VBA in Excel for the web.

Common Errors and Misconceptions

A frequent compile-time problem is putting a required argument after an optional one. For example, a declaration conceptually arranged as required value, optional setting, required name is invalid. Move all required inputs before the optional inputs, or redesign the procedure so the call is unambiguous.

Another common error is writing an optional typed parameter without a default. A declaration such as Optional ByVal Count As Long is not the usual valid pattern for a typed optional argument; provide a default such as zero, or change the parameter to a Variant if you specifically need omission detection. A Variant is not automatically the best choice merely because it permits a missing argument.

Also remember that omitting an argument is different from passing a value that happens to equal the default. With Optional Rate As Double = 0.08, the procedure receives 0.08 whether the caller omits Rate or explicitly passes 0.08. If the procedure must respond differently to those two cases, use an optional Variant and test for missingness.

Finally, optional arguments do not make a function usable in every Excel context. A UDF still has to follow the limitations of worksheet functions, and macro security settings still apply. If a function is intended for other people’s workbooks, explain its inputs, default behavior, and file requirements rather than assuming users can inspect the VBA code.

When to Use a Default, a Variant, or Another Design

Use a defaulted optional argument when one value is genuinely the normal choice and callers occasionally need to override it. This keeps common calls compact without hiding a complicated decision. Examples include a default separator, a standard precision, or a typical tax rate in a clearly illustrative calculation.

Use a Variant without a default when the procedure must know whether the caller omitted the parameter, and when the extra type handling is acceptable. An alternative is to use a separate Boolean flag, such as a required setting that explicitly says whether to apply a custom value. That can be more verbose, but sometimes makes the intent clearer than treating a particular value—such as zero or an empty string—as a signal.

For a procedure with many optional settings, consider whether a smaller set of focused procedures or a configuration object would be easier to maintain. Long lists of optional parameters can make calls difficult to understand and can complicate future changes. Choose the simplest interface that communicates the important choices clearly.

Frequently Asked Questions

Can a VBA function have an optional argument?
Yes. Declare it with Optional in the function’s parameter list. Put it after all required parameters, and provide a default for a typed argument unless it is a Variant declared without one.

Can an optional argument be in the middle of a VBA parameter list?
It can be the first optional argument after required parameters, but no required argument can follow it. Every parameter after the first optional one must also be optional.

How do I check whether an optional VBA argument was supplied?
Declare it as an optional Variant without a default and use IsMissing. This check is meant for optional Variant arguments; for a typed parameter with a default, omission and explicitly passing that default are not distinguishable.

Can an Excel worksheet formula omit a VBA function argument?
Yes, a worksheet can call a VBA UDF without a trailing optional argument, so the declared default applies. For example, =AddTax(A2) uses the default rate in the sample. The workbook must be macro-enabled, and VBA does not run in Excel for the web.

Do optional arguments need to be at the end?
Yes. VBA requires optional parameters to follow required parameters. Once the first optional parameter appears in a declaration, all following parameters must also be optional.

Related

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.