VBA Function Arguments: Types, Defaults, and Examples

VBA function arguments are the values or variables supplied when a function is called; the function’s parameter list declares what it accepts and how those values are passed. In VBA, arguments are passed ByRef by default, but you can use ByVal, optional parameters, and named arguments to control how calls behave.

Arguments and parameters: the basic distinction

The words argument and parameter are often used interchangeably, but they describe different parts of a function call. A parameter is a named placeholder in the function definition. An argument is the actual value or variable provided at the call site. For example, in a function that declares a parameter named amount, the number 100 is an argument when you call the function with that value.

A function can accept no arguments, one argument, or several. Its parameter list also specifies each parameter’s data type and whether it is passed ByRef or ByVal. These declarations help VBA check calls and determine whether a function can change a variable supplied by its caller.

Arguments are not the same as a function’s return value. A function receives arguments, performs its work, and returns a result by assigning that result to the function’s name. A Sub procedure can accept arguments too, but it does not return a value in the same way.

How a VBA function call works

Here is a function with one required parameter and one optional parameter. It calculates a price after tax, using 8 percent if the caller does not supply a rate.

Public Function AddTax(ByVal amount As Currency, Optional ByVal rate As Double = 0.08) As Currency
    ' Return the amount after tax.
    AddTax = amount * (1 + rate)
End Function

Public Sub DemoTax()
    Dim subtotal As Currency
    Dim total As Currency

    subtotal = 100
    total = AddTax(amount:=subtotal, rate:=0.075)
End Sub

The function’s declaration names the parameters, sets their types, and gives the optional parameter a default. The call supplies a subtotal of 100 and a rate of 0.075, so the returned total is 107.50. If the call were AddTax(subtotal) in an expression, the default rate would apply and the result would be 108.00.

In VBA, a function used as part of an expression is normally written with parentheses around its argument list. When a procedure call is written as a standalone statement and its return value is not being used, VBA also permits call syntax without parentheses, or parentheses after the Call keyword. For clarity, use parentheses when capturing a function’s result.

Before running a macro that calls example procedures in a workbook, save a copy: macros cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, VBA code is edited in the Visual Basic Editor, commonly opened with Alt+F11. On Mac, use Option+F11 or open the editor from the Developer tab; keyboard behavior can depend on the keyboard and macOS settings.

ByRef and ByVal: whether the caller’s variable can change

ByRef passes a reference to the caller’s variable. If the procedure assigns a new value to that parameter, the caller’s variable changes too. Since ByRef is the default in VBA, a declaration that does not state either keyword is effectively ByRef. Making the choice explicit can make code easier to review.

ByVal passes a value to the procedure, so assigning to the parameter does not replace the caller’s variable. For ordinary numeric and text values, this is often the clearest choice when a function only needs to read its input. It helps prevent accidental changes to the original variable.

For example, a Sub declared with ByRef price As Currency could multiply price by 1.1 and change the variable supplied by its caller. If its parameter were declared ByVal price As Currency, the calculation would affect only the local parameter unless the procedure returned a result another way. A function that calculates a new value is often easier to reason about when it returns that value rather than altering an input as a side effect.

There is an important object-related nuance: a ByVal object parameter protects the caller’s object variable from being reassigned inside the procedure, but it does not make the object itself immutable. Code can still change the object’s properties or contents through that reference. ByRef is useful when a procedure intentionally needs to replace the caller’s object variable; ByVal is usually preferable when it only needs to work with the referenced object.

When passing a variable ByRef, its declared type matters. VBA may report a ByRef argument type mismatch if the argument’s type does not match the parameter as expected. If a conversion is intended, make it explicit or use a ByVal parameter where appropriate. Do not add ByVal just to silence an error without checking whether conversion changes the value or behavior.

Optional parameters and default values

An optional parameter lets a caller omit an argument. In a declaration, optional parameters must come after required parameters. You can give an optional parameter a default value, as in the tax-rate example, so calls that omit it have defined behavior. Multiple optional parameters can be useful for settings that have sensible defaults, but too many can make a function difficult to understand.

Optional defaults are part of the function declaration, not values that VBA guesses from the caller’s context. VBA requires a suitable constant default expression. If an optional Variant has no explicit default, the function can use IsMissing to check whether the argument was omitted. IsMissing applies to optional Variant arguments; it is not a general test for whether any typed argument was supplied.

Omitting optional arguments in a VBA call can require commas to mark the position of a later argument. Named arguments can be clearer when you want to supply a later optional setting while leaving an earlier one at its default. Keep a default stable and unsurprising: changing it can alter the behavior of existing calls that omit that argument.

Named arguments and argument order

Positional arguments are matched to parameters by order. If a function accepts a start date and an end date, the first supplied value goes to the start-date parameter and the second to the end-date parameter. Reversing those values may still compile, but produce the wrong result if their types allow the swap.

VBA also supports named arguments, written with the parameter name followed by :=. The AddTax call uses amount:=subtotal and rate:=0.075. This makes the intent visible and can reduce mistakes when a procedure has several optional settings. Named arguments are especially useful when calls are maintained by people who did not write the function.

Use the parameter names exactly as declared, and do not confuse the named-argument operator := with ordinary assignment =. In VBA code, named arguments can be supplied in a different order from the declaration. In an Excel worksheet formula, however, a user-defined function is called using worksheet formula syntax and positional arguments; VBA’s named-argument notation is not entered into the cell formula.

Choosing parameter types

Declare a parameter with the narrowest practical type that accurately represents valid input. For example, use Long for many whole-number counts, Double for floating-point calculations, and Currency for many fixed-decimal monetary calculations within its range. A type declaration documents the contract and lets VBA catch some incompatible calls. Choose types based on the data and required precision, not just convenience.

Use Variant when a parameter genuinely needs to accept different kinds of values or when the function must distinguish a missing optional argument with IsMissing. Variants are flexible, but they can conceal unexpected input and require checks before operations such as numeric conversion. A function that expects a date should not silently treat arbitrary text as a valid date unless that behavior is intended.

Object parameters should specify an object type when practical, such as Worksheet or Range, rather than accepting any object. That communicates what members the function expects to use. A caller passing a range should also be clear about whether the function expects one cell, a rectangular range, or a particular layout; the declared type alone does not express those details.

Arrays can also be passed to procedures, but their bounds, dimensions, and contents need attention. If a procedure changes array elements through a ByRef parameter, those changes can affect the caller’s array. Document whether the function expects a one-dimensional array, whether empty arrays are allowed, and how nonnumeric or missing elements are handled. Do not assume that every array has a lower bound of zero.

Function arguments in Excel worksheet functions

A VBA function in a standard module can sometimes be used as a user-defined function (UDF) in a worksheet formula, for example when it returns a value based on cell inputs. The function’s public visibility, parameter types, and return type influence how useful it is from the worksheet. A UDF should generally calculate and return a result rather than edit cells, change formatting, or perform other actions; worksheet recalculation can invoke functions in ways that make side effects confusing.

Worksheet UDF calls use cell references or values as arguments, such as =AddTax(A1,0.075), assuming the function is available in the workbook and accepts compatible values. Optional arguments can be omitted according to formula argument-position rules, but named arguments such as rate:= are VBA call syntax, not normal Excel formula syntax. A UDF also does not automatically behave like every built-in worksheet function: calculation, error handling, and available argument types depend on how its VBA code is written.

Excel for the web can open and edit workbooks containing VBA, but it cannot create, run, or edit VBA macros in the browser. Use the desktop Excel application to work with VBA. On Windows, macro availability is also controlled by Excel’s security settings; only enable macros in files you trust.

Common argument problems and how to diagnose them

Wrong number of arguments: Compare the call with the function declaration. Check required parameters, optional parameters, and commas between values.
Arguments in the wrong order: Check each positional value against the parameter list. Named arguments can make long calls more readable.
ByRef argument type mismatch: Verify the declared type of both the parameter and the supplied variable. Use an explicit conversion only when it is appropriate.
Unexpected change to a variable: Look for a ByRef parameter, which is the default, and inspect assignments to that parameter inside the procedure.
Unexpected default behavior: Confirm that an omitted optional argument has the default you expect, or test for omission where the declaration uses an optional Variant without a default.
Worksheet formula error: Check that the UDF is available in the workbook, the workbook permits macros, the argument count and types are valid, and the function returns a value for the supplied inputs.

When debugging, place the cursor in the procedure and use the Visual Basic Editor’s debugging tools to step through the call and inspect parameter values. A small test Sub with declared variables can help separate a VBA calling problem from a worksheet formula problem. Test boundary cases too: zero, an empty cell, an omitted optional argument, and values at the limits your function is meant to support.

Designing clear function interfaces

A useful function interface tells callers what information is required and what can be omitted. Prefer a few meaningful parameters over a long list of switches with unclear names. Give parameters descriptive names, use explicit ByVal or ByRef declarations, and document units and assumptions where a type alone is not enough. For example, a parameter named rate could mean 0.08 or 8; state which representation the function expects.

If a procedure has several optional settings or is likely to grow, consider whether one function is doing too much. Separate related calculations into smaller functions or use a clearly defined data structure when appropriate. Changing a public function’s parameter order or type can break existing calls, so check other procedures and worksheet formulas before changing its interface. In shared workbooks, keeping old calls compatible may matter more than making a declaration shorter.

Frequently Asked Questions

Are VBA function arguments ByRef by default?
Yes. Unless the declaration specifies ByVal, a VBA parameter is ByRef by default. State the intended behavior explicitly when it matters, especially if the procedure might assign a new value to the parameter.

What is the difference between an argument and a parameter in VBA?
A parameter is the named input declared in a procedure definition. An argument is the actual value or variable supplied when the procedure is called. A function parameter might be named amount; a caller could pass a variable named subtotal as its argument.

How do I make a VBA function argument optional?
Declare it with the Optional keyword after all required parameters. You can provide a suitable default value in the declaration, or use an optional Variant without a default when you need to test whether it was omitted with IsMissing.

Can VBA function arguments be named?
Yes. In VBA code, use the declared parameter name followed by :=, such as rate:=0.075. Named arguments can make calls with several optional settings easier to read. Worksheet formulas use positional arguments instead.

Can a function change an argument?
It can change the caller’s variable when the parameter is ByRef and the code assigns to that parameter. ByVal prevents reassignment of the caller’s variable for a value parameter, but for object parameters it does not prevent changes to the referenced object itself.

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.