Excel VBA Function Parameters: Defaults, ByRef, and More

VBA & macros · Updated November 21, 2026

In Excel VBA, a standard function parameter is a named input declared in the function’s parentheses, such as ByVal amount As Double. If you leave out both ByVal and ByRef, VBA uses ByRef by default; parameters are required unless you mark them Optional.

What “standard parameter” means in VBA

VBA does not have a special keyword called “standard parameter.” People usually mean an ordinary argument in a function’s declaration: a value the function receives when it is called. The declaration gives each parameter a name and, usually, a data type. For example, a function that calculates a total might receive a price and a quantity.

It helps to distinguish a parameter from an argument. A parameter is the placeholder in the function definition; an argument is the actual value supplied at a call. In CalculateTotal(price, quantity), price and quantity are parameters. In a call such as CalculateTotal(12.5, 4), 12.5 and 4 are arguments.

A function is different from a Sub procedure because a function returns a value. In VBA, you set that value by assigning it to the function’s name. The word “standard” does not imply a special default data type or a different kind of function; it generally refers to the normal, required parameter form.

How a function declaration is written

A basic declaration names the function, lists its parameters in parentheses, and specifies the type of value returned after As. For example:

Option Explicit

Public Function CalculateTotal(ByVal price As Double, ByVal quantity As Long) As Double
    CalculateTotal = price * quantity
End Function

Here, price and quantity are required parameters. The function returns a Double, and the assignment to CalculateTotal provides that return value. VBA does not use a return statement as some other languages do. Public makes the function available outside its module; worksheet functions should normally be placed in a standard module, rather than a worksheet or workbook code module.

Declaring parameter types makes the function’s expectations clearer and can prevent unintended conversions. Common choices include Long for whole numbers, Double for floating-point numbers, String for text, Boolean for True or False, and Variant when values may vary in type. If you omit As type, the parameter is a Variant; Option Explicit does not change that rule for parameters.

ByRef is the default, but ByVal is often clearer

Every VBA parameter is passed either ByRef or ByVal. With ByRef, the procedure can work with the caller’s variable itself. If the function or procedure assigns a new value to that parameter, the caller’s variable can change. Because ByRef is the default, this declaration uses it even though it does not say so:

Public Function Example(ByRef count As Long) As Long
    count = count + 1
    Example = count
End Function

With ByVal, VBA passes a value to the procedure, so assigning to the parameter does not replace the caller’s variable. That makes it a useful choice when an input is meant to be read rather than modified. For instance, the earlier CalculateTotal example uses ByVal to make its intent explicit. Many Excel worksheet functions are also easier to reason about when they treat inputs as read-only.

For object parameters, ByVal does not make the object itself immutable. It passes a copy of the object reference, so code can still change the referenced worksheet, range, or other object. It does prevent the procedure from replacing the caller’s object variable with a different reference. This distinction matters when a function accepts a Range or another object.

When calling a ByRef parameter, VBA can require the argument to match the declared type. A differently typed variable or an expression may produce a “ByRef argument type mismatch” error. If the procedure only needs a value and should not alter the caller’s variable, declaring the parameter ByVal is often the simplest way to avoid unintended changes and reduce such mismatches.

Required and optional parameters

Parameters are required by default. To allow a caller to leave one out, declare it with Optional. Optional parameters must come after all required parameters, so a caller can supply arguments in a predictable order. An optional parameter can have a default value, as in this example:

Option Explicit

Public Function NetPrice(ByVal price As Currency, _
                         Optional ByVal discountRate As Double = 0) As Currency
    NetPrice = price * (1 - discountRate)
End Function

If a caller supplies only a price, the discount rate is zero. If a discount rate is supplied, it is used in the calculation. Default values in a parameter declaration must be suitable constant values; they are not a place to run a calculation or refer to a cell. Optional parameters without a specified default have special missing-value behavior and must be declared as Variant. In that case, VBA’s IsMissing function can check whether the caller omitted the argument.

Keep optional arguments limited to genuine choices. If a function has many optional inputs, it can become hard to tell what a call means, especially when the arguments are positional. A clear function name, sensible defaults, or separate functions for distinct tasks may make the interface easier to use.

Choosing parameter types and order

Choose each type to match the data the function actually needs. For example, use Long rather than the older Integer type for most whole-number counters in modern VBA, and use Double for measurements or calculations that need decimal precision. Currency can suit many fixed-point monetary calculations, but it has its own range and precision limits; choose types based on the data and the required accuracy rather than on a label alone.

For worksheet functions, a Range parameter is useful when the function needs to inspect cells, while a value parameter such as Double is useful when it needs a number. Consider whether the function should accept a single cell, a range, or a value, and handle blank cells, text, and Excel errors deliberately. A strongly typed parameter can cause a type mismatch when the supplied value cannot be converted; a Variant gives more flexibility but means your code should check what it received.

Parameter order is significant in ordinary positional calls. Put the most consistently required inputs first, followed by optional settings. If two parameters have the same type, clear names and a logical order help prevent callers from accidentally swapping their values. In VBA code, named arguments can make calls easier to read, but Excel worksheet formulas use positional arguments.

Using a VBA function in an Excel worksheet

A user-defined function can be entered in a worksheet much like a built-in function when it is public and located in a standard module. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor, insert a standard module, and put the function there. Save the workbook in a macro-enabled format such as .xlsm; a workbook saved as .xlsx does not retain VBA code. Excel may also require macros to be enabled before the function can run.

With a value of 100 in cell A2 and the NetPrice function above in a standard module, enter this in B2:

=NetPrice(A2,0.1)

The result is 90: 100 multiplied by (1 − 0.1). If you omit the optional discount argument, the function uses its declared default of zero, so the result for the same A2 value is 100. A worksheet formula cannot use VBA’s named-argument syntax, and arguments must follow the order in the function declaration.

Excel for the web can preserve a macro-enabled workbook, but it cannot run or edit VBA macros. VBA availability and editing steps differ on Mac, and some Windows-specific VBA features are not supported there; the basic parameter rules remain VBA rules. If the function is unavailable in a workbook, check that the code is in a standard module, the function is public, the workbook still contains VBA, and macros are permitted by the current security settings.

Common parameter mistakes and debugging

  • Assuming parameters are ByVal: They are ByRef unless you specify otherwise. State the passing mode explicitly when it affects the function’s behavior.
  • Putting a required parameter after an optional one: Move required inputs to the beginning of the parameter list.
  • Leaving out a type by accident: An untyped parameter is a Variant. Add an explicit type when the function expects a specific kind of value.
  • Using a function parameter as a worksheet output: A worksheet UDF should return a result; do not design it to change the cells passed into it. Use a Sub procedure for actions that edit cells.
  • Calling a function that is not visible to the worksheet: Check that it is declared Public in a standard module and that the workbook is macro-enabled.

If a normal VBA procedure modifies a variable through ByRef, that behavior is intentional but should be obvious to its callers. For example, a procedure can increment a caller’s variable this way:

Option Explicit

Public Sub DemonstrateByRef()
    Dim number As Long
    number = 5
    AddOne number
    Debug.Print number  ' Prints 6
End Sub

Private Sub AddOne(ByRef value As Long)
    value = value + 1
End Sub

This example illustrates why the default matters: AddOne changes number because its parameter is ByRef. Before running a macro or procedure that changes a workbook, save a copy first. Macro changes cannot be undone with Ctrl+Z; on Mac, use Command+Z for ordinary undo, but it does not restore changes made by a macro either.

Frequently Asked Questions

What is the default parameter type in VBA?
If a parameter declaration omits As type, VBA treats it as a Variant. This is separate from the passing mode: an unmarked parameter is ByRef by default.

Are VBA function parameters ByRef by default?
Yes. Unless the declaration says ByVal, a VBA parameter is passed ByRef. State ByVal explicitly when the function should not change a caller’s variable.

How do I make a VBA function parameter optional?
Use the Optional keyword and place that parameter after required parameters. You can give it a default value, such as Optional ByVal rate As Double = 0.

Can a worksheet formula call a VBA function with parameters?
Yes, if the function is public in a standard module, the workbook retains its VBA code, and macros are allowed. Enter the function name and arguments in order, just as you would for a built-in function.

Why does VBA show “ByRef argument type mismatch”?
The argument supplied may not match the parameter’s declared type closely enough for a ByRef call. Check both types, or use ByVal if the procedure only needs the argument’s value and should not modify the caller’s variable.

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.