Excel VBA Default Parameter Values and Optional Arguments

VBA & macros · Updated November 21, 2026

In Excel VBA, give a parameter a default by declaring it Optional and assigning a value in the procedure signature, such as Optional ByVal rate As Double = 0.1. If the caller leaves that argument out, VBA uses the declared value; arguments without a supplied default must be Variant if they are optional.

Basic syntax and rules

An optional parameter is written after all required parameters in a Function or Sub declaration. Its default value follows an equals sign. For example, the declaration below requires a price but lets the caller omit the markup rate:

Public Function AddMarkup(ByVal price As Double, Optional ByVal rate As Double = 0.1) As Double
    ' Apply a 10% markup unless the caller supplies another rate.
    AddMarkup = price * (1 + rate)
End Function

Here, calling AddMarkup(100) uses a rate of 0.1 and returns 110. Calling AddMarkup(100, 0.2) overrides the default and returns 120. These figures illustrate the arithmetic; the default is simply a value chosen for this example.

  • Put required parameters first and optional parameters after them.
  • Once a parameter is optional, every parameter after it must also be optional.
  • Use a declared default for a typed optional parameter, or declare it as Variant if you need to detect that it was omitted.

What happens when an argument is omitted

The default belongs to the procedure declaration, not to the variable or worksheet cell that calls it. VBA uses that default only when a call leaves the corresponding argument out. Supplying a value always takes precedence, including when the supplied value happens to equal the default.

Defaults are applied separately for each call. They do not store a previous answer, remember the last value entered, or change the value of an Excel cell. A declaration with rate As Double = 0.1 therefore means “use 0.1 when absent,” not “use 0.1 unless another setting exists somewhere in the workbook.”

This behavior is useful when a function has a sensible common case but still needs flexibility. A text-cleaning function might default to trimming spaces, for example, while letting a caller choose a different option. It is also a way to add a new setting to a procedure without forcing every existing call to provide another argument, provided the new parameter is optional and placed at the end.

Using a default in a worksheet function

A public VBA function in a standard module can be called from a worksheet like a user-defined function. In the VBA editor, choose Insert > Module, then place the function there. On Windows, open the editor with Alt+F11; on a Mac, use Option+F11, sometimes together with Fn depending on the keyboard. The worksheet call uses the function name and the arguments it needs.

For the AddMarkup example above, if the sale price is in cell A2, enter =AddMarkup(A2) in a worksheet cell to use the default rate. To use a different rate, enter =AddMarkup(A2,0.2). Excel evaluates the VBA function when the formula recalculates; it does not display a dialog asking the user to accept or change the default.

A worksheet UDF should return a value rather than make changes to the workbook. A VBA function called from a cell is not a substitute for a macro that edits cells, and Excel restricts some actions in worksheet functions. Store the workbook in a macro-enabled format such as .xlsm if it contains VBA. Excel for the web does not run VBA, so a workbook that depends on a VBA UDF is not portable to web-only use in the same way as a workbook built entirely from native Excel formulas.

Skipping an optional argument

Omitting the final optional argument is straightforward: leave it off the call, and VBA supplies its default (or the missing value for an optional Variant with no default). Skipping an earlier optional argument while supplying a later one takes more care. In a VBA call, leave an empty position between commas or use named arguments.

For example, if a procedure has an optional format argument followed by an optional flag, a caller can use a blank argument position to supply the flag while omitting the format. Named arguments make the intent clearer in VBA when there are several parameters: SomeFunction(requiredValue:=10, flag:=True). The names must match the parameter names in the declaration. Named-argument syntax is a VBA calling feature; it is not the syntax users type into an ordinary worksheet formula.

Leaving out a required argument is different. VBA cannot fill it from a default unless the declaration actually marks it optional, so the call will produce an argument-related error. If a procedure has many optional settings, consider whether callers would be better served by separate, clearly named procedures or a smaller set of options rather than a long run of comma placeholders.

Defaults must be fixed values

A parameter default is part of the declaration, so it must be a constant value VBA can accept there. Numbers, text literals, and Boolean values are common choices. A default is not a place to calculate a value from a worksheet cell or call a changing function such as Now. In particular, Optional ByVal stamp As Date = Now is not a way to request the current time on each call.

For a value that should be calculated when the procedure runs, use an optional Variant with no explicit default and test whether it was omitted. IsMissing works only with an optional Variant parameter. For example:

Public Function Greeting(Optional ByVal person As Variant) As String
    ' Use a fallback only when the argument was omitted.
    If IsMissing(person) Then person = "there"
    Greeting = "Hello, " & CStr(person)
End Function

Calling Greeting() returns “Hello, there”; calling Greeting("Mina") returns “Hello, Mina”. In real code, the function can instead calculate a runtime value in the If IsMissing branch. This keeps the decision inside the function, where it can use the current date, workbook state, or other logic.

There is an important distinction between “omitted” and “given a value that looks empty.” If a caller explicitly supplies an empty string or another value, the argument was supplied. Also, when an optional Variant has an explicit default, IsMissing cannot be used to tell whether the caller omitted it: the default is supplied in that case. Use a no-default optional Variant when omission itself carries meaning.

Common errors and how to fix them

Several declaration or call mistakes account for most problems with optional parameters. Check these points when VBA reports a compile error or a function behaves differently than expected:

  • Optional parameter before a required one: move required arguments to the beginning. All arguments after the first optional one must also be optional.
  • Optional typed parameter without a default: if you intentionally want to distinguish omission, change it to Variant. Otherwise, provide a valid default value.
  • Wrong parameter order: positional calls depend on the declaration order. Review the function signature, or use named arguments in VBA to make the call explicit.
  • Assuming zero means omitted: a numeric argument explicitly set to 0 is a supplied value, not an omitted argument. Use IsMissing with a Variant if the difference matters.
  • Expecting a worksheet prompt: a VBA default is applied silently. It does not create an input box, formula suggestion, or editable default in the worksheet.
  • Function not found in a cell: confirm that the function is public and in a standard module, that macros are permitted, and that the workbook is saved in a format that preserves VBA.

When editing the declaration, also check spelling and parameter types. For example, a default intended as the text “0” is not the same as the numeric value 0. A worksheet function may convert values in ways that are not obvious, so explicit types and clear input expectations make a UDF more predictable.

When to use a default, Variant, or separate function

Use a typed optional parameter with a default when one constant value is the correct ordinary choice and callers only occasionally need to override it. This is usually the simplest and clearest design. The type also documents what kind of value is accepted and helps catch accidental mismatches.

Use an optional Variant with no declared default when the function needs to know whether the user omitted an argument, or when the fallback must be computed at run time. Handle the missing case deliberately before using the parameter. A Variant can contain different kinds of values, so validate or convert it if the function requires a particular type.

Use separate functions when two modes have substantially different meanings or when a long list of optional arguments makes calls hard to understand. For instance, names such as FormatShortDate and FormatLongDate can communicate intent better than a function with several switches. For a calculation that can be written with native Excel functions, a worksheet formula may also be more portable: it avoids requiring users to enable VBA and works in Excel for the web.

Security, saving, and platform notes

VBA code is macro code, so users should enable it only in workbooks from trusted sources and follow their organization’s security settings. Before running or changing a macro-containing workbook, save a separate copy. Macro actions generally cannot be reversed with Ctrl+Z, and Excel for Mac also does not provide a general undo for VBA changes. A worksheet UDF that only calculates and returns a result is different from a macro that modifies data, but it still depends on VBA being available and permitted.

Excel for Microsoft 365 on Windows uses the Visual Basic Editor for creating and changing VBA. Excel for Mac also includes a VBA editor, though shortcut behavior can depend on the keyboard and some VBA or Windows-specific features may differ. Excel for the web does not provide VBA execution. If a workbook must work across desktop, Mac, and web environments, consider replacing a VBA UDF with a built-in Excel formula where practical, or document the desktop requirement clearly.

Frequently Asked Questions

Can a VBA function have a default parameter value?

Yes. Declare the argument as optional and assign its default in the function signature, such as Optional ByVal count As Long = 1. A call that omits it uses 1; a call that supplies a value uses that value instead.

What is the default for an optional VBA argument?

If a typed optional argument has an explicit default, VBA uses that value. An optional Variant declared without a default is treated as missing when omitted, and IsMissing can detect that case.

Can I use Now as a VBA parameter default?

No. A parameter declaration cannot use a changing runtime calculation such as Now as its default. Use an optional Variant without a default, check it with IsMissing, and assign the current time inside the procedure.

Can I omit an optional argument in the middle?

Yes. In a VBA call, leave an empty argument position with commas, or use named arguments to identify the arguments you are providing. Required arguments cannot be omitted, and worksheet formulas do not use VBA’s named-argument syntax.

Does a VBA default parameter work in Excel for the web?

No. Excel for the web does not run VBA. A worksheet formula that relies on a VBA function therefore needs Excel desktop with the workbook’s macros available; use a native worksheet formula for broader compatibility where possible.

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.