Yes. In Excel VBA, declare an optional parameter with the Optional keyword, usually followed by a default value, such as Optional ByVal greeting As String = "Hello". A caller can then leave that argument out, and the procedure uses its default; for a Variant parameter without a default, you can check whether it was omitted with IsMissing.
What Optional Parameters Do
An optional parameter lets one VBA procedure handle calls that provide different amounts of information. Instead of writing separate procedures for every variation, you can require the essential inputs and allow callers to supply extra settings only when needed. This is useful for helper functions, formatting routines, report macros, and Excel user-defined functions (UDFs).
For example, a greeting function might always need a person’s name but not always need a custom salutation. If the caller leaves out the salutation, the function can use “Hello.” The argument is still part of the procedure’s declared interface; Optional simply means a call is allowed to omit it.
Optional parameters do not make a function’s return value optional, and they do not automatically make a missing value meaningful. You decide what omission means by choosing a default or testing for an omitted argument.
Declaration Syntax and Parameter Order
In a VBA procedure declaration, place Optional before the parameter name. A typical function signature looks like this:
Public Function Greeting(ByVal person As String, _
Optional ByVal salutation As String = "Hello") As String
' Return a greeting using the supplied or default salutation.
Greeting = salutation & ", " & person & "!"
End Function
The required person parameter comes first, and the optional salutation parameter follows it. In VBA, required parameters must come before optional parameters. Once an optional parameter appears in a parameter list, every parameter after it must also be optional. You cannot put a required parameter after an optional one.
When declaring an optional parameter, you can provide a default value after an equals sign. The default must be a constant value of a compatible type. Common examples include 0 for a number, False for a Boolean, and "" for an empty string. A date default can be written as a VBA date literal, such as #1/1/2025#. Choose a default that produces a sensible result when the caller supplies nothing.
VBA parameters are ByRef by default, meaning a procedure can potentially change the caller’s variable. The examples here use ByVal to make clear that the procedure receives a value and should not modify the caller’s variable through that argument. You can omit ByVal when you intentionally need the default ByRef behavior, but make that choice deliberately.
How Defaults Work When an Argument Is Omitted
When a caller leaves out an optional parameter that has a default, VBA supplies that default to the procedure. The procedure generally does not need to know whether the caller explicitly supplied the same value or omitted the argument. For instance, both a call that leaves out a salutation and a call that passes "Hello" result in the same salutation being used.
This is convenient for simple settings, such as a starting row, a separator character, or whether a feature is enabled. It also makes procedure calls easier to read because common cases can stay short. However, a default should not be a value that could also be a legitimate user choice if your code needs to distinguish “not supplied” from “supplied as this value.” In that situation, use an optional Variant without an explicit default and test for omission, or redesign the interface to represent the distinction clearly.
A default is fixed in the declaration; it is not a way to calculate a value from another argument. If the fallback depends on the workbook, the current date, or another parameter, use a sentinel value or an omitted Variant and set the actual value inside the procedure. Document that rule so callers know what will happen.
Calling a Procedure and Skipping an Argument
If the optional argument is last, the simplest call is to leave it out. In VBA code, the function above can be called with just a name, and the declared default will apply. If you want a non-default salutation, pass it as the second argument.
Skipping an optional argument in the middle of a call takes more care. With positional arguments, leave an empty position between commas. For example, if a procedure accepts a required category, an optional label, and an optional uppercase flag, a caller can omit the label while providing the flag:
Public Function BuildTag(ByVal category As String, _
Optional ByVal label As String = "item", _
Optional ByVal uppercase As Boolean = False) As String
' Build a tag and optionally convert it to uppercase.
BuildTag = category & ":" & label
If uppercase Then BuildTag = UCase$(BuildTag)
End Function
Sub ExampleCall()
Dim tagText As String
' Leave label at its default and set uppercase to True.
tagText = BuildTag("sales", , True)
End Sub
In this example, tagText receives SALES:item. The blank position between the two commas tells VBA to use the default label, while True is supplied for the third parameter. A long sequence of blank positions can be hard to maintain, so named arguments are often clearer when a call skips an earlier option. In VBA, a named call can use syntax such as BuildTag(category:="sales", uppercase:=True); the parameter names must match the declaration.
Using IsMissing With an Optional Variant
Use IsMissing when it matters whether a caller omitted an argument rather than passed a particular value. Its intended use is an optional Variant parameter declared without an explicit default. For other parameter types, IsMissing cannot tell you that the argument was omitted; those parameters receive their declared default value.
Public Function DescribeValue(Optional ByVal value As Variant) As String
' Distinguish an omitted argument from supplied values.
If IsMissing(value) Then
DescribeValue = "No argument supplied"
ElseIf IsEmpty(value) Then
DescribeValue = "An Empty value was supplied"
ElseIf IsNull(value) Then
DescribeValue = "A Null value was supplied"
Else
DescribeValue = CStr(value)
End If
End Function
These checks address different states: IsMissing checks whether the optional Variant argument was omitted, IsEmpty checks for an uninitialized or Empty Variant, and IsNull checks for the special Null value. They are not interchangeable with testing whether a string is blank or whether a number is zero. For example, a caller can explicitly pass an empty string, and that is not the same as omitting the argument.
Keep the distinction only if it is important to the function’s behavior. If omission and an empty value should mean the same thing, a default such as "" can make the declaration simpler.
Using Optional Parameters in an Excel Worksheet Function
A public VBA function in a standard module can be used as a worksheet UDF when the workbook is saved in a macro-enabled format and macros are enabled. For example, insert the Greeting function into a standard module, put Maya in cell A2, and enter this formula in another cell: =Greeting(A2). The result is Hello, Maya!. The formula can also pass a second argument, such as =Greeting(A2,"Welcome"), to replace the default greeting.
In a worksheet formula, commas separate function arguments. To leave an optional argument blank and provide a later argument, use consecutive separators for the empty position, just as you would when supplying arguments to a VBA procedure. Check that the function’s parameter order matches the formula’s positions; named-argument syntax used in VBA calls is not the usual way to enter worksheet formulas.
There are practical limits to worksheet UDFs. A VBA function used in a cell should return a value rather than change other cells, format cells, or perform actions such as displaying a message box. Excel for the web does not run VBA macros, so a VBA UDF will not calculate there as it does in desktop Excel. Excel for Mac supports VBA, though availability and behavior of particular VBA features can differ from Windows; avoid relying on Windows-only automation or controls if the workbook must work on both platforms.
Optional Parameters Compared With Other Designs
An optional parameter is usually a good fit when one procedure has a clear required input and a small number of sensible, independent options. It helps keep everyday calls short without creating a second procedure for every variation. But optional arguments are not always the clearest design.
- Use a default value when omission should reliably mean one simple value, such as a standard delimiter or
False. - Use an optional Variant with
IsMissingwhen the code must distinguish omission from every valid supplied value. - Use a separate procedure when two operations have substantially different purposes or behavior. VBA does not support defining multiple procedures with the same name and different parameter lists as conventional overloads.
- Use a settings object or clearly named arguments when a procedure is accumulating many options. A long list of optional parameters can make calls difficult to understand and maintain.
Named arguments can improve readability, especially when optional settings appear in the middle of a signature. They also reduce the risk of accidentally assigning a value to the wrong position. If you change a parameter’s name, however, code that calls it by that name must also be updated.
Common Errors and How to Avoid Them
A frequent declaration error is placing a required argument after an optional one. Reorder the parameters so all required inputs come first, or make the later parameter optional too if that matches the intended design. Another common problem is declaring a typed optional parameter without a default. If a parameter needs omission detection instead, declare it as a Variant without a default and use IsMissing.
Calls can also fail when a blank argument is omitted incorrectly. To skip a middle positional argument, preserve its comma-delimited position; simply deleting the argument may shift later values into the wrong parameters. Named arguments are often easier to audit in long calls. In worksheet formulas, verify that each comma still corresponds to the intended argument position.
If a worksheet cell shows #NAME? for a VBA function, confirm that the function is public and stored in a standard module, that the workbook is macro-enabled, and that macros are permitted by the workbook’s security settings. Also remember that a workbook opened in Excel for the web cannot execute VBA. If a function returns an unexpected result, check whether the caller passed an empty string, zero, or another explicit value rather than omitting the argument.
Testing and Macro Safety
Test both the ordinary call and each meaningful optional case: omitted argument, explicit default, a different value, and any special values your code supports. For a worksheet UDF, test the actual formulas users will enter and confirm what happens when a formula leaves an optional position blank. Clear names and comments help future maintainers understand whether the default is merely convenient or has important business meaning.
Before running a macro that calls or changes workbook data, save a copy of the workbook. VBA macro actions generally cannot be reversed with Ctrl+Z, and running code can change or delete data. Store macro-enabled work in a suitable macro-enabled workbook format, such as .xlsm, and only enable macros in files from sources you trust.
Frequently Asked Questions
Can a VBA function have more than one optional parameter?
Yes. It can have several, as long as required parameters come first and every parameter after the first optional one is also optional. Give each option an appropriate default or use a Variant without a default when omission itself matters.
Can an optional parameter come before a required parameter?
No. VBA requires required parameters to appear before optional parameters. Rearrange the signature or redesign the procedure so that later inputs are also optional.
How do I check whether an optional argument was omitted?
Declare it as an optional Variant with no explicit default, then test it with IsMissing. A typed optional parameter with a default cannot use IsMissing to distinguish omission from a caller explicitly passing that default.
Can I skip an optional argument in the middle?
Yes. In a positional VBA call, leave its position empty between commas, or use named arguments to specify the later option by name. In a worksheet formula, keep the empty argument position between the comma separators.
Do optional VBA parameters work in Excel for the web?
The optional-parameter feature is part of VBA, but Excel for the web does not run VBA macros or VBA UDFs. Use desktop Excel to run the VBA code; a formula-based or other non-VBA solution is needed for web-only use.
Related
- Excel Text to Columns: How to Undo and Restore Data
- Excel Pasting: Stop Tab-Delimited Text Splitting on Paste
- Excel Unable to Merge Cells? Causes, Fixes, and Alternatives
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range Is Empty? How to Check Cells CorrectlyTo check whether a VBA range contains no entered values or formulas, use Application.CountA(target) = 0. First make…
- VBA Code for UserForm: Build an Excel Data Entry FormTo create a VBA UserForm in Excel, add a UserForm in the Visual Basic Editor, place controls on…
- VBA Code to Display a Graph on a UserFormUse an Excel chart on a worksheet as the graph source, export it as a JPEG, then load…
- Excel VBA: How to Exit a For Next Loop Early in Your MacroYes. In Excel VBA, put Exit For inside a For...Next loop to leave it as soon as a…
- Excel VBA: Get the First Item in a DictionaryTo get the first key from a VBA Scripting.Dictionary, copy its keys into a Variant array and read…
- Macro Recorder Setup in Excel: A Step-by-Step GuideTo set up the Macro Recorder in Excel for Microsoft 365 on Windows, show the Developer tab, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.