To create an Excel VBA function with multiple parameters, declare each argument in the function’s parentheses, separated by commas, then call the function from a worksheet cell with matching arguments. For example, a public function named DiscountedTotal can accept a unit price, quantity, and discount rate, and return one calculated value.
What a VBA function with multiple parameters does
A VBA function is a procedure that calculates and returns a value. Its parameters are the inputs it expects. A function can have one parameter, several parameters, or—in some designs—optional parameters. When Excel recalculates a worksheet formula that calls the function, it supplies the argument values to the VBA code.
This differs from a Sub procedure, which performs actions but does not return a value to a worksheet formula. If the goal is to use a custom calculation in a cell, write a Function, not a Sub. The worksheet formula and VBA function must use the same function name, and the arguments should be supplied in the order declared unless you are calling the function from VBA using named arguments.
A function can still be useful when its calculation is simple. The benefit is that a repeated business rule can be given a descriptive name and reused across many rows. For a calculation already covered by a built-in Excel function, though, the built-in option is often easier to share and maintain.
Write a function with three required parameters
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module to add a standard module, then place a public function there. The following example takes a price, a quantity, and a discount rate. The calculation is illustrative; it applies the discount after multiplying price by quantity.
Option Explicit
Public Function DiscountedTotal(ByVal UnitPrice As Double, _
ByVal Quantity As Long, _
ByVal DiscountRate As Double) As Double
' Return the discounted extended price.
DiscountedTotal = UnitPrice * Quantity * (1 - DiscountRate)
End Function
The declaration tells Excel and VBA what each input represents and what type of value it expects. As Double is appropriate for decimal numbers such as prices and rates; As Long is appropriate for a whole-number quantity. The function returns its answer by assigning that answer to DiscountedTotal, the function’s own name.
With a unit price of 25 in A2, quantity of 4 in B2, and discount rate of 10% in C2, enter this formula in D2: =DiscountedTotal(A2,B2,C2). It returns 90, because 25 × 4 × (1 − 0.10) = 90. The cell layout and argument order matter: the first cell supplies the price, the second the quantity, and the third the rate.
Call the function from a worksheet
Once the code is in a standard module, return to the worksheet and enter the function like an ordinary formula. Type an equals sign, the function name, and the input references inside parentheses. Separate multiple arguments with commas in the examples here. Excel’s list separator can depend on regional settings, so an installation configured for another separator may require semicolons in the worksheet formula.
Arguments can be cell references, constants, or expressions. For example, if the three inputs are stored in A2:C2, reference those cells so the result updates when an input changes. You can also enter constants directly, but cell references are usually easier to audit and reuse. The order is positional: swapping the quantity and price would pass the values to the wrong parameters even though the formula might still be accepted.
The function name needs to be available to Excel. For a worksheet-callable UDF, use a Public Function in a standard module. A function placed in a worksheet’s code module or marked Private is not generally available in the same way as a public function in a standard module. Avoid naming a custom function after a built-in Excel function, since the name can be confusing and may cause conflicts.
Choose argument types and ByVal or ByRef
Declaring argument types helps document the function and catch incompatible inputs. Common choices include Double for decimal numbers, Long for whole numbers, String for text, and Boolean for True/False values. Dates can use Date. An argument declared as a specific type may fail if a worksheet supplies text, an Excel error value, or another incompatible value. For inputs whose contents may vary, Variant can be useful, but the function should then check what it received before calculating.
VBA passes arguments ByRef by default, which allows a procedure to change the variable passed to it. For worksheet functions, explicitly using ByVal is often a clear choice: it makes the function’s intent to read the supplied value rather than alter a caller’s variable explicit. Specify the passing method for each parameter, and do not assume that a worksheet UDF can or should modify its input cells.
Worksheet cells can contain blanks, text, or errors even when a function expects numbers. Decide what behavior is appropriate: return a meaningful result, handle a case deliberately, or allow an error to make invalid input visible. Avoid silently treating every unexpected input as zero unless that is genuinely the intended rule, because it can hide bad data.
Make some parameters optional
Not every input has to be mandatory. In VBA, an optional argument can have a default value. This lets a caller omit it when the common case is wanted and provide it when a variation is needed. Optional parameters must come after required parameters in the argument list.
Public Function ShippingCost(ByVal Weight As Double, _
Optional ByVal Express As Boolean = False) As Double
' Illustrative rate: 2 per weight unit, plus 8 for express service.
Dim BaseCost As Double
BaseCost = Weight * 2
If Express Then BaseCost = BaseCost + 8
ShippingCost = BaseCost
End Function
This deliberately simple example uses an illustrative rate, not a real shipping quote. With a weight of 3 in A2, =ShippingCost(A2) returns 6 because the omitted Express argument defaults to False. With the same weight in A2, =ShippingCost(A2,TRUE) returns 14: the base amount is 6 and the express addition is 8. The second formula supplies the optional argument explicitly.
Optional arguments are most helpful when there is a sensible default. If users must always make a meaningful choice, requiring the argument can prevent accidental assumptions. When designing a function with several optional choices, consider whether a set of clearly named alternatives or separate functions would be easier for colleagues to understand.
Understand worksheet UDF behavior
A worksheet UDF is not identical to a built-in Excel function. Excel can use a UDF in a cell formula, but a conventional UDF is intended to calculate and return a value. It should not change other cells, rearrange sheets, or perform other workbook actions as a side effect. Use a macro or another suitable procedure for actions such as writing results into a range.
Excel determines when to recalculate based on the function’s inputs and calculation settings. A normal UDF that refers only to its supplied arguments generally recalculates when those inputs change. Functions that depend on changing information outside their arguments may need special handling, but avoid forcing recalculation unless the function genuinely requires it. Unnecessary recalculation can slow a workbook.
VBA functions work in desktop Excel for Windows and desktop Excel for Mac, though keyboard shortcuts and some editor details can differ. On Mac, use Option+F11 to open the Visual Basic Editor on many keyboards; the exact shortcut can depend on the keyboard and system settings. Excel for the web does not create or run VBA macros, so a workbook’s VBA UDF cannot be relied on there. Users who need web compatibility should consider a formula-based alternative.
Fix common errors
If a cell displays #NAME?, Excel may not recognize the function. Check that the spelling in the formula matches the function declaration, that the function is public, and that it is in a standard module. Also check whether the file is saved in a macro-enabled format such as .xlsm, and whether macros are enabled under the organization’s security policy.
A #VALUE! result or a VBA type mismatch can point to an input that does not match a declared type. Confirm that numeric parameters contain numbers rather than numeric-looking text, and check for blank cells or error values. A wrong result without an error often means the arguments are in the wrong order or the formula references the wrong cells. Compare each argument in the worksheet call with the parameter list from left to right.
For a compile error, open the Visual Basic Editor and use Debug > Compile VBAProject if that command is available for the project. Check commas, parentheses, line-continuation characters, and the spelling of variable names. Option Explicit at the top of a module helps reveal misspelled or undeclared variables. If you edit code while Excel is open, return to the sheet and recalculate the formula if the displayed value has not updated.
Compare VBA with formulas and LAMBDA
VBA is a practical option when the calculation needs procedural logic, must work with existing VBA tools, or is already part of a macro-enabled workbook. However, a VBA UDF depends on macros being available and cannot run in Excel for the web. It may also be less convenient to distribute because recipients must trust and enable the workbook’s code.
For simpler calculations, ordinary worksheet formulas are easier to inspect and are generally more portable. In Microsoft 365 or Excel 2021, LET can give names to intermediate values inside a formula. In Microsoft 365 or Excel 2024, LAMBDA can define reusable custom calculations without VBA. These options do not use VBA parameter declarations, but they can be a good fit when the goal is a reusable calculation rather than automation.
Choose based on where the workbook will be used, who needs to maintain it, and whether the logic needs to act on the workbook or only return a value. A custom VBA function is not automatically better just because it can accept many parameters. Keep the interface small enough that users can tell what each input means.
Save and share VBA functions safely
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain its code. The regular .xlsx format does not store VBA macros; saving a macro-enabled workbook in that format can remove the VBA project. When sharing, explain what the function does and provide a non-macro alternative if recipients cannot enable macros or use desktop Excel.
Only enable macros in files from sources you trust, and follow your organization’s security rules. Before running any macro that changes workbook data, save a separate copy: macro actions cannot be undone with Ctrl+Z. A calculation-only UDF such as the examples above returns a value and does not intentionally edit cells, but it is still VBA code, so the workbook should be treated with appropriate care.
Frequently Asked Questions
Can an Excel VBA function have more than two parameters? Yes. Declare as many required parameters as the calculation needs, separating them with commas. Keep the order and meaning clear, and remember that the worksheet call must supply required arguments in that same order.
Can a worksheet call a VBA function without every argument? Only if the omitted arguments are declared Optional and have suitable defaults. Put optional arguments after required ones. Otherwise, Excel requires the call to provide the full set of required inputs.
Why does my VBA function show #NAME? in Excel? Common causes are a misspelled function name, a function that is not Public, code stored somewhere other than a standard module, or macros being unavailable. Confirm the workbook format and macro settings as well as the formula spelling.
Can I use a VBA function in Excel for the web? No. Excel for the web does not run VBA macros, including worksheet UDFs. Use desktop Excel to calculate the VBA function, or replace it with a compatible worksheet formula or another supported approach.
Should I use ByVal for function parameters? Often, yes, when the function only needs to read its inputs. Declaring ByVal makes that intent explicit and avoids relying on VBA’s default ByRef behavior. Choose types that match expected values and handle unexpected worksheet inputs deliberately.
Related
- Excel Table Data Sorting: Steps, Options, and Fixes
- Excel Spreadsheet Not Responding? Causes and Fixes
- Excel Suddenly Not Responding? Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Copy the End Part of a String with RightTo copy the last characters of a string in Excel VBA, use the Right$ function: assign Right$(text, numberOfCharacters)…
- Excel VBA: How to Check Whether Cell Colors Match ExactlyYes. In Excel VBA, compare the cells’ Interior.Color values; they are numeric color values, so an equality test…
- VBA AutoFilter Mode: How to Check and Control ItIn Excel VBA, Worksheet.AutoFilterMode tells you whether AutoFilter dropdown arrows are displayed on a worksheet; it does not…
- Sudoku Solver in Excel: Setup, Methods, and VBA CodeYes—Excel can solve Sudoku. For a practical solver, enter the puzzle in cells B2:J10 and run a short…
- Excel UserForm Initialize: How It Works and How to Use ItIn Excel VBA, UserForm_Initialize is the event procedure that runs when a UserForm instance is loaded, before the…
- VBA Macro to Find the Last Row in Excel: Methods That WorkTo find the last nonblank row in column A, use lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, where ws is the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.