Excel VBA Function Arguments: Syntax and Examples

VBA & macros · Updated November 21, 2026

In Excel VBA, function arguments are the values or variables you pass to a function so it can do its work; the function declares them in parentheses after its name. For example, a function declared as Function AddNumbers(ByVal firstNumber As Double, ByVal secondNumber As Double) As Double accepts two numeric arguments, which you can pass as AddNumbers(3, 4).

What function arguments do

A function is a procedure that accepts input, performs operations, and returns a value. Its arguments are the inputs. When you call a function, the values supplied are often called arguments; in the function declaration, the named placeholders are called parameters. People commonly use the two terms interchangeably, and VBA does not use separate syntax for them.

Arguments let you reuse one procedure with different inputs instead of writing a new procedure for every value. A function that calculates a sales tax amount, for instance, can accept a price and a tax rate. The same function can calculate tax for many prices without changing its code.

VBA functions are used in two main ways: from other VBA procedures, or as user-defined functions (UDFs) entered in Excel worksheet cells. The declaration rules are largely the same, but a worksheet UDF has additional limitations, and its inputs are normally references to cells or ranges.

Function argument syntax

A function declaration names the function, lists its arguments, and can specify the type of the value it returns. Here is a complete example:

Public Function AddNumbers(ByVal firstNumber As Double, ByVal secondNumber As Double) As Double
    ' Return the sum of the two inputs.
    AddNumbers = firstNumber + secondNumber
End Function

The names inside the parentheses are parameters. Each has a data type, such as Double. The final As Double specifies the function’s return type. In VBA, the function assigns its return value to a variable with the same name as the function. Here, the returned value is the sum of the inputs.

A call from another VBA procedure can look like this:

Public Sub ShowSum()
    Dim total As Double
    ' Supply two arguments and store the returned value.
    total = AddNumbers(3, 4)
End Sub

In this example, the function receives 3 and 4, and total becomes 7. The argument order matters: the first supplied value goes to firstNumber, and the second goes to secondNumber. VBA matches positional arguments from left to right unless you use named arguments.

Choose parameter types that fit the data

Declare argument types to make a function’s expectations clear and to catch incompatible values. Common choices include String for text, Long for whole numbers, Double for decimal numbers, Boolean for True or False, and Variant when the value may vary or needs to represent certain special states. Excel row numbers and ordinary counts generally fit Long; Integer has a much smaller range and is rarely the better choice for general counting.

For example, a function that joins a first and last name could accept two String arguments. A calculation that allows fractional values should generally use a suitable decimal type rather than an integer type, which cannot preserve a fractional result. Consider both the inputs and the return value when choosing types; they are declared separately.

A function can also accept an Excel object, such as a Range, when it needs to work with cells. The function can then read a cell’s value through the range’s Value or Value2 property. Passing a range object is different from passing the cell’s displayed text or numeric value, so make that choice deliberately.

ByVal and ByRef: whether a procedure can change an input

VBA arguments are passed ByRef by default. That means the procedure can work with the caller’s variable itself, and an assignment to the parameter can change that variable. With ByVal, the procedure receives a value copy for the argument, so assigning to the parameter does not replace the caller’s variable.

For functions intended to calculate and return a result, explicitly using ByVal for ordinary numeric and text inputs often makes the behavior easier to understand. Use ByRef when changing a caller’s variable is intentional. For example, a Sub that adds one to a variable could declare its parameter ByRef; after the call, the caller’s variable would contain the changed value. This is separate from returning a function result.

There are details behind the simple copy-versus-reference description. For an object variable, ByVal copies the object reference, not the entire object; code can still change the object through that reference. Also, VBA may create a temporary value when an argument’s type does not match a parameter declared ByRef. Changes to that temporary value do not necessarily update the original expression or variable. Matching the argument type and the parameter type avoids confusing conversion behavior.

Optional arguments and default values

An argument marked Optional can be left out when calling the function. Optional parameters must follow required parameters, so a required argument cannot appear after an optional one. You can give an optional argument a default value, which the function uses when the caller omits it.

Public Function PriceAfterDiscount(ByVal price As Double, _
                                   Optional ByVal discountRate As Double = 0.1) As Double
    ' Use a 10 percent discount unless another rate is supplied.
    PriceAfterDiscount = price * (1 - discountRate)
End Function

Calling PriceAfterDiscount(100) returns 90 because the default discount rate is 0.1. Calling PriceAfterDiscount(100, 0.2) returns 80. The calculation assumes the rate is expressed as a decimal: 0.2 means 20 percent. In ordinary VBA declarations, an optional typed parameter should have a default value; an optional Variant can instead be declared without one.

For an optional Variant without a default, the function can use IsMissing to check whether the caller supplied the argument. This check applies to optional Variant arguments, not to every optional parameter type. A default value is often simpler when there is a natural fallback.

ParamArray and a variable number of arguments

Use ParamArray when a procedure should accept a variable number of arguments. It must be the last parameter, and it is declared as a Variant array. This is useful for helper functions that need to inspect an unknown number of supplied values, but it gives up some of the clarity of a fixed list of typed parameters.

Public Function SumValues(ParamArray values() As Variant) As Double
    Dim item As Variant
    Dim total As Double
    ' Add each supplied value to the running total.
    For Each item In values
        If IsNumeric(item) Then total = total + CDbl(item)
    Next item
    SumValues = total
End Function

With this function in a standard VBA module, a call such as SumValues(2, 3, 5) returns 10. The example ignores values that IsNumeric does not recognize and converts accepted values to Double. A ParamArray can be empty, so a function using one should also have sensible behavior when no values are passed; here, the result remains zero.

Calling functions and using them in worksheet cells

When a function’s returned value is part of an expression or assignment, put its arguments in parentheses, as in total = AddNumbers(3, 4). A common VBA source of confusion is calling a procedure as a standalone statement: a function call with Call uses parentheses, while a call without Call can omit them when discarding the result. For example, Call ShowResult(3) and ShowResult 3 are forms used for a Sub call. For calculations, assigning the function result is usually clearer.

To use a custom function in a worksheet, place a Public Function in a standard module in the workbook’s VBA project. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then use Insert > Module in the editor and add the function there. On Mac, the Visual Basic Editor is available through Tools > Macro > Visual Basic Editor; keyboard shortcuts can vary with macOS settings. Save the workbook in a macro-enabled format such as .xlsm if you need to retain its VBA project.

For example, with the AddNumbers function above stored in a standard module, enter =AddNumbers(3,4) in a worksheet cell to get 7. A range parameter can accept a worksheet reference too; a UDF might receive a range and calculate from its cells. Excel recalculates a UDF when its referenced inputs change, but VBA UDFs are not a replacement for worksheet formulas in every situation. In particular, a UDF called from a cell should return a result rather than attempt to edit other cells or change Excel’s environment during recalculation.

VBA code is not undone by Excel’s Ctrl+Z command. Save a copy of the workbook before running a macro or trying unfamiliar VBA code, especially if it changes cells or workbook structure. This is a useful precaution even when the immediate example is a simple calculation.

Common argument errors and how to avoid them

  • Wrong number of arguments: The call supplies too few or too many values for the required parameters. Check the declaration and remember that only optional parameters may be omitted.
  • Wrong order: Positional arguments are matched from left to right. A number supplied where the function expects a date or text may cause a type mismatch or an unintended conversion.
  • Type mismatch: The supplied value cannot be used as the declared type. Check for text in a numeric cell, empty values, error values, or a range passed where a single value is expected.
  • Unexpected change to a variable: A parameter defaults to ByRef unless specified otherwise. Add ByVal when the function should not assign a new value back to the caller’s variable.
  • Function returns the wrong value: A VBA function returns the value assigned to its own name. Check that every relevant code path assigns that name and that the assigned value is compatible with the declared return type.
  • Worksheet shows a function-name error: Confirm the function is Public, located in a standard module, and saved in a workbook where macros are enabled. A worksheet formula also needs the correct number and order of arguments.

Named arguments can make a call more readable when a function has several optional parameters. In VBA, use the parameter name followed by := and its value, such as PriceAfterDiscount(price:=100, discountRate:=0.2). This can reduce ambiguity, but the names must match the declaration, and named-argument syntax is used in VBA calls rather than typed into an ordinary worksheet formula.

How to decide which argument style to use

For most functions, use a small number of required, explicitly typed parameters and mark ordinary inputs ByVal. This makes the function contract visible to anyone reading a call. Add an optional parameter when a default is genuinely useful and predictable, rather than using optional arguments to hide many different behaviors in one function.

Use ByRef when the function or procedure is deliberately meant to update a caller’s variable, and document that side effect. Use a Range argument for a worksheet UDF that needs cell or range information. Choose ParamArray only when accepting a changing number of inputs is an important part of the design. If a function has many settings, separate functions or a clearly structured object may be easier to maintain than a long list of optional parameters.

Frequently Asked Questions

What is the difference between an argument and a parameter in VBA?
A parameter is the named placeholder in a procedure declaration; an argument is the value supplied when the procedure is called. The words are often used interchangeably in everyday VBA explanations.

Are VBA function arguments ByVal or ByRef by default?
They are ByRef by default. Declare ByVal or ByRef explicitly when the distinction matters, so readers can see whether assigning to the parameter is intended to affect the caller’s variable.

How do I make a VBA function argument optional?
Put Optional before the parameter declaration, after any required parameters. You can supply a default value, such as Optional ByVal rate As Double = 0.1, so callers can omit it.

Can a VBA function have no arguments?
Yes. Declare it with empty parentheses, such as Public Function CurrentLabel() As String. In a worksheet, a no-argument UDF can be entered with parentheses, for example =CurrentLabel().

Can Excel worksheet formulas call VBA functions?
Yes, if the function is an eligible Public function in a standard module in an available macro-enabled workbook. Enter its name and arguments as a worksheet formula, and ensure macros are enabled. Worksheet UDFs should calculate and return values rather than edit other cells.

How can one VBA function accept any number of arguments?
Declare a final parameter with ParamArray, for example ParamArray values() As Variant. The function receives the supplied values as an array and can loop through them; ParamArray must be the last parameter.

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.