VBA Functions With Parameters: Syntax and Examples

VBA & macros · Updated November 11, 2026

A VBA function with parameters accepts values from its caller, uses them in its code, and returns a result. Define the parameters in parentheses after the function name, specify their data types, and assign the result to the function’s name before it ends.

What a VBA Function Does

A function is a reusable procedure that calculates or retrieves a value. Its parameters are the inputs the function expects. For example, a function that adds tax needs an amount and a tax rate; a function that formats a name might need a first name and a last name.

Here is a basic example for Excel VBA:

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

The words inside the parentheses declare two parameters: amount and rate. Each has a data type, and As Currency after the closing parenthesis declares the function’s return type. The line AddTax = ... supplies the value the function returns. A VBA function does not use a separate Return statement.

For example, a call that supplies 100 and 0.08 returns 108. The rate is expressed as a decimal: 0.08 represents 8 percent. Supplying 8 instead would mean a rate of 800 percent in this calculation.

Function Syntax and Parameter Order

The general structure is Public Function FunctionName(parameter As Type) As ReturnType, followed by the function’s statements and End Function. A function can have no parameters, one parameter, or several. Separate multiple parameters with commas, and pass values in the same order as their declarations unless you use named arguments.

For example, this signature has three inputs:

Public Function DiscountedPrice(ByVal price As Currency, ByVal discount As Double, ByVal shipping As Currency) As Currency

The return type after the parameter list describes the result, not another input. If the return type is omitted, VBA treats the function’s return value as a Variant. Explicit types are usually clearer: they document the intended data and can help catch mistakes. Common parameter and return types include String for text, Long for whole numbers, Double for decimal calculations, Currency for many monetary calculations, Boolean for True or False, and Variant when the value may vary in type.

Writing and Calling a Function in Excel

To create a function available to worksheet formulas in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then choose Insert > Module. Put a public function in that standard module, rather than in a worksheet’s code module or the ThisWorkbook module. The function must be public to be available as a user-defined worksheet function.

  1. In the Visual Basic Editor, choose Insert > Module.
  2. Paste or type the function code into the module.
  3. Return to Excel and enter the function name and arguments in a cell.
  4. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA code.

With the amount in A2 and the tax rate in B2, enter =AddTax(A2,B2) in a worksheet cell. If A2 contains 100 and B2 contains 8%, the result is 108. Excel passes the values from those cells to the function’s parameters in order. The function can also be called by other VBA procedures; for example, an assignment can use result = AddTax(100, 0.08) if result has been declared with a suitable type.

On a Mac, the Visual Basic Editor is available from the Developer tab’s Visual Basic button; keyboard shortcuts can vary with the keyboard and function-key settings. Excel for the web can preserve a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use the desktop Excel application to work with the code.

Positional and Named Arguments

In a normal call, arguments are positional: the first supplied value goes to the first parameter, the second to the second, and so on. This makes the order important. If a function takes a start date followed by an end date, reversing the values may produce a wrong result even when both values are valid dates.

VBA also allows named arguments, which identify each input by its parameter name. The assignment symbol in a named argument is :=, not an ordinary equals sign:

result = DiscountedPrice(price:=120, discount:=0.15, shipping:=8)

Named arguments can make calls easier to read, especially when a function has several inputs of similar types. They can also reduce errors caused by remembering the order. Use the exact parameter names declared in the function. When calling a worksheet function from a cell, enter its arguments in the function’s declared order; VBA’s named-argument syntax is for VBA calls, not ordinary worksheet formula syntax.

ByVal and ByRef: Whether a Call Can Change an Input

Each parameter can be passed ByVal or ByRef. With ByVal, the function receives a copy of the value, so assigning a new value to the parameter does not change the caller’s variable. With ByRef, the function can work with the caller’s variable itself and may change it. If neither keyword appears, VBA uses ByRef by default.

For ordinary calculation functions, ByVal is often a helpful choice because it makes the function less likely to alter a value unexpectedly. Choose ByRef when changing the caller’s variable is intentional and documented. A function used in a worksheet formula should be designed to return a result rather than to change its input cells; changing cells or other workbook state from a worksheet function is not an appropriate way to produce a formula result.

For example, a VBA procedure could pass a variable to a ByRef parameter and have the function alter that variable. That behavior is different from returning a calculated answer. If a routine’s main purpose is to perform actions rather than return a value, a Sub procedure is usually a clearer choice.

Optional Parameters and Default Values

A parameter can be optional when a useful default makes sense. Optional parameters go after required parameters, and a typed optional parameter can be given a default value. In this example, callers can omit the number of decimal places:

Public Function FormatAmount(ByVal amount As Currency, Optional ByVal decimalPlaces As Long = 2) As String
    ' Return a formatted amount using the requested decimal places.
    FormatAmount = Format$(amount, "0." & String$(decimalPlaces, "0"))
End Function

A call with an amount of 12.5 and no second argument uses the default of 2; a call can supply a different number of decimal places. The return type is String because Format$ returns formatted text. Formatting is not the same as rounding a numeric value for later calculations, so retain a numeric return type if callers need to do more arithmetic with the result.

Optional parameters are useful when an input is genuinely optional, not simply to avoid deciding what a function should do. A default should be meaningful and documented. VBA’s IsMissing check applies to optional Variant parameters; for a typed parameter with a declared default, use that default rather than relying on IsMissing.

Handling a Variable Number of Inputs

If callers need to supply a varying number of arguments, VBA provides ParamArray. It collects the supplied arguments into an array of Variants. The ParamArray parameter must be last in the parameter list, and it cannot be declared ByVal or ByRef.

Public Function SumValues(ParamArray values() As Variant) As Double
    Dim item As Variant

    ' Add each supplied numeric value.
    For Each item In values
        SumValues = SumValues + CDbl(item)
    Next item
End Function

A caller can pass one value or several. For example, in a worksheet, =SumValues(3,4,5) returns 12. This simple example assumes every argument can be converted to a number. If text, empty values, or error values may be supplied, add validation and decide how the function should handle them; otherwise conversion can fail. For a fixed set of inputs, explicitly named parameters are generally easier to understand and validate than a ParamArray.

Function Versus Sub, and Worksheet Limitations

A Function returns a value, so it can be used in an expression or assigned to a variable. A Sub performs actions and does not return a value. Use a function to calculate a commission, test a condition, or produce a text result. Use a Sub to format cells, copy data, or carry out a sequence of workbook actions.

Worksheet user-defined functions have practical limits. They are intended to calculate and return values, not to edit other cells, display dialog boxes, or change Excel settings as a side effect of recalculation. Excel may recalculate a formula more than once, so side effects can be surprising. If the goal is to change the workbook, run a Sub from a button, the Macro dialog, or another appropriate VBA procedure instead.

Also consider whether VBA is necessary. A built-in Excel function is usually easier to share and works without enabling macros. VBA can be worthwhile when a calculation is specialized, needs to be reused, or depends on logic that is awkward to express with worksheet formulas. A custom function is only available to users who have the code in a suitable workbook or add-in and can run its macros.

Common Errors and Troubleshooting

  • Wrong number of arguments: Check how many parameters the function declares and how many values the call supplies. Required parameters cannot be omitted.
  • Arguments in the wrong order: Compare the call with the function signature. Named arguments in VBA can make multi-parameter calls clearer.
  • Unexpected result: Check the units and types. A percentage may need to be passed as 0.08 rather than 8, and a date or number stored as text may not behave as expected.
  • Function name not recognized in a cell: Confirm that the function is public and stored in a standard module, the workbook containing it is open, and macros are permitted by the organization’s security settings.
  • Code disappears after saving: Save the workbook in a macro-enabled format such as .xlsm. A standard .xlsx workbook does not retain VBA project code.
  • Changes do not appear in the cell: Check for errors in the function and confirm that its inputs have changed. If the function depends on information outside its arguments, Excel may not know that it should recalculate when that information changes.

Macro security matters because VBA code can perform actions in a workbook or on a computer. Enable macros only when you trust the file and its source, and follow your organization’s security rules. Before running VBA that changes workbook data, save a separate copy: macro actions cannot be undone with Ctrl+Z. On Mac, the same general caution applies even though keyboard shortcuts differ.

Frequently Asked Questions

How do I pass parameters to a VBA function?
Declare them between the function name’s parentheses, then supply matching arguments in the call. For example, Function AddTax(amount As Currency, rate As Double) takes two parameters, which can be supplied as AddTax(100, 0.08) in VBA.

How does a VBA function return a value?
Assign the result to the function’s own name. In AddTax = amount * (1 + rate), the assignment sets the value returned when execution reaches End Function.

Can a VBA function have no parameters?
Yes. Put empty parentheses after the function name, such as Public Function CurrentLabel() As String. A worksheet call uses the function name with parentheses, subject to how Excel displays the formula.

Can I use a VBA function in an Excel cell?
Yes, if it is a public function in a standard module in an open workbook or available add-in, and macros are allowed. Save the code in a macro-enabled file. Excel for the web does not run VBA.

What is the difference between ByVal and ByRef?
ByVal passes a value so changes to the parameter do not alter the caller’s variable. ByRef can let the function change that variable; it is VBA’s default when neither keyword is specified.

Choosing a Clear Function Design

A good parameterized function makes its purpose and assumptions easy to see. Choose descriptive names, declare suitable types, keep the number of inputs manageable, and document unusual units or defaults. Use ByVal for calculation inputs unless changing the caller’s variable is a deliberate part of the design. Return a value of a type that suits how callers will use it, and validate inputs when invalid data is plausible.

Finally, test a function with ordinary values and boundary cases: zero, a negative value if relevant, a blank input, or an unexpected type. For a worksheet function, test it in cells as well as from VBA if both calling methods matter. That small check helps distinguish a correct calculation from a function that merely compiles.

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.