A VBA function call runs a function and, in the usual case, uses the value it returns. In Excel, you can call a function from another macro, use a public VBA function in a worksheet formula, or call an Excel worksheet function from VBA; the correct syntax depends on which kind of function you mean.
What a VBA function call means
A function is a VBA procedure designed to produce a value. A call is the instruction that runs it, supplying any inputs it needs, called arguments. For example, a function might receive a price and a tax rate, calculate a total, and return that total to the code that called it.
This differs from a Sub procedure. A Sub performs actions, such as formatting cells or copying data, but does not return a value to its caller. A Function can also perform actions, but its defining feature is that it can return a value. You cannot use a Sub as a worksheet formula, because a cell formula needs a result.
The word function can also mean a built-in VBA function such as Len or LCase, an Excel worksheet function such as SUM, or a custom function written in VBA. These are related but not interchangeable: the first two are supplied by the software, while a custom function exists only in a workbook or add-in where its VBA code is available.
Basic syntax and returned values
Define a function with the Function keyword, give its parameters and return type, and assign the result to the function’s own name. VBA does not use a separate return statement. This example calculates a total after applying a rate:
Option Explicit
Public Function AddTax(ByVal amount As Double, ByVal rate As Double) As Double
' Return the amount including the rate.
AddTax = amount * (1 + rate)
End Function
Here, amount and rate are the inputs, and As Double declares the type of the returned value. The line AddTax = amount * (1 + rate) places the answer in the function’s return slot. If that assignment never happens, a function can return a default value, such as zero for a numeric type, which may conceal a logic error.
When another procedure needs the answer, call the function as part of an expression and assign the result to a variable:
Option Explicit
Public Sub ShowTotal()
Dim subtotal As Double
Dim taxRate As Double
Dim total As Double
' Supply example inputs, then store the returned value.
subtotal = 100
taxRate = 0.08
total = AddTax(subtotal, taxRate)
Debug.Print total
End Sub
The result is 108. The parentheses group the arguments passed to the function. The function can be called from a different procedure in the same VBA project, provided its accessibility and location allow that call.
Calling a function from a Sub
The standard pattern is result = FunctionName(arguments). It makes clear that the function’s result is being used. If the result is irrelevant, VBA also permits a function call as a statement using the Call keyword and parentheses. In that case, the returned value is discarded, so this is usually less useful than assigning it.
Option Explicit
Public Sub DemonstrateCallSyntax()
Dim result As Double
' Use the result in an expression.
result = AddTax(100, 0.08)
' Run the function but discard its returned value.
Call AddTax(100, 0.08)
End Sub
Do not confuse that syntax with calling a Sub. A Sub can normally be called without Call and without parentheses around its arguments, as in UpdateReport 2026. With the Call keyword, put the arguments in parentheses. For functions, using an expression and capturing the result is the clearest approach.
Arguments, optional inputs, and ByVal
Arguments are the actual values supplied at the call site; parameters are the named inputs in the function definition. Keep their order and types in mind. If a function expects a number followed by a rate, reversing the two arguments may produce a valid calculation with the wrong meaning rather than an obvious error.
VBA parameters are ByRef by default, meaning the procedure can change the caller’s variable. Use ByVal when the function should receive a value without changing the original variable. This is often a sensible default for numeric inputs and makes the function’s behavior easier to reason about. For objects and more complex types, the details of what is copied or referenced differ, so choose the passing mode deliberately.
A function can also have an optional argument. The following function uses a zero discount when the caller leaves that argument out:
Option Explicit
Public Function PriceAfterDiscount(ByVal price As Double, Optional ByVal discountRate As Double = 0) As Double
' Apply the supplied discount rate.
PriceAfterDiscount = price * (1 - discountRate)
End Function
You can call it with just a price or provide both values. In the first case, the default rate is used. Optional parameters need to be declared after required parameters, and a default value in a VBA procedure declaration must be a constant expression. For a more complex choice, use a separate function or test an explicitly supplied value rather than relying on a complicated default.
Using a VBA function in an Excel worksheet
A custom VBA function can be called from a cell formula if it is available to the workbook. Put a Public Function in a standard module, rather than placing it in a worksheet’s code module or the ThisWorkbook module, when you want to use it like an ordinary worksheet function. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and add the function code there.
With the AddTax function above in a standard module, and 100 in A2 and 0.08 in B2, enter =AddTax(A2,B2) in another cell. The result is 108. Excel recalculates the formula when its referenced inputs change, subject to the workbook’s calculation settings. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to preserve the code.
Worksheet functions should generally calculate and return a value rather than change other cells, formatting, or workbook settings. A function called from a cell formula is not a substitute for a macro that performs those actions. Also, a function can be unavailable if its code is in another workbook that is closed, if macros are disabled, or if Excel cannot resolve the function name. Excel for the web can display a workbook with VBA, but it cannot create, run, or edit VBA macros; use the desktop application for those tasks.
Calling Excel worksheet functions from VBA
VBA code can call many Excel worksheet functions through the WorksheetFunction object. For example, this procedure sums the values in cells A1 through A5 on the active sheet:
Option Explicit
Public Sub PrintRangeSum()
Dim total As Double
' Call Excel worksheet function SUM from VBA.
total = Application.WorksheetFunction.Sum(ActiveSheet.Range("A1:A5"))
Debug.Print total
End Sub
This is a call from VBA to Excel’s SUM, not a call to a custom VBA function named Sum. The method is useful when a worksheet function already provides the behavior you need. However, some worksheet-function calls raise a VBA run-time error when Excel would show an error value in a cell, such as a lookup with no match. If you need to handle such outcomes, consider checking inputs first or using an approach that returns a value you can test, such as the corresponding member of Application where available.
Common errors and how to fix them
Function-call errors often come from a small set of causes. Check the function definition and the call together, not just the line highlighted by the editor:
- Wrong number of arguments: Compare the call with the function’s required parameters. Include every required argument and remove extra ones, or declare an input as optional if omitting it is part of the intended design.
- Type mismatch: Check whether a cell contains text, an error value, or an empty value where the function expects a number or date. Declare parameter and return types, and validate inputs when a worksheet may contain unexpected data.
- Sub or Function not defined: Check spelling, scope, and which workbook contains the code. A procedure declared
Privatecannot be called from outside its module. A worksheet formula also needs a suitable public function in a standard module. - Wrong parentheses: A function used in an expression takes parentheses around its arguments. A Sub called without
Callnormally does not. If you useCall, include parentheses. - Unexpected zero or blank result: Confirm that every execution path assigns a value to the function name. Check conditional branches and whether an error-handling path exits before the assignment.
- Worksheet shows an error or the function name: Confirm that macros are enabled where permitted, the workbook containing the code is open or the code is in the current workbook, and the function is public and correctly named. A misspelled function in a formula may appear as
#NAME?.
Use Option Explicit at the top of a module and declare variables with Dim. It makes VBA report many misspelled or undeclared variable names at compile time instead of silently treating a typo as a new variable. To investigate a call, step through the code in the Visual Basic Editor and inspect the argument values and returned value.
Running VBA on Windows, Mac, and the web
On Windows, the Visual Basic Editor shortcut is Alt+F11; the Developer tab also has a Visual Basic command. On Mac, use Option+F11 where the keyboard exposes that function key, sometimes with Fn, or open the editor from Excel’s Developer tab. Excel for Mac supports VBA, but some Windows-specific features and automation interfaces are not available or behave differently. If a macro depends on Windows APIs, test or redesign it for Mac rather than assuming it will work unchanged.
Before running a macro that changes a workbook, save a separate copy. VBA macro actions cannot be undone with Ctrl+Z in Excel; on Mac the corresponding undo shortcut is Command+Z, but it does not restore changes made by a macro. This warning concerns macros that alter data; simply evaluating a worksheet function is different, but keeping a backup is still prudent when editing code or testing on important files.
Frequently Asked Questions
How do I call a VBA function in Excel?
From another VBA procedure, use the function name with its arguments, usually assigning the returned value to a variable. To use a custom function in a worksheet, put a public function in a standard module and enter its name in a cell formula.
What is the difference between a VBA Function and a Sub?
A Function can return a value and can be used in an expression or, when suitable, in a worksheet formula. A Sub performs a procedure but does not return a value to its caller, so it cannot be entered as a normal cell formula.
Do VBA function arguments need parentheses?
Yes when the function is used as part of an expression, as in result = AddTax(100, 0.08). A procedure call using Call also uses parentheses. A Sub call without Call generally omits them.
Can I call a VBA function from a worksheet cell?
Yes, if it is a suitable public function in a standard module and the workbook containing the code is available. Use a macro-enabled file to retain VBA, and remember that Excel for the web does not run VBA.
Why does Excel show #NAME? for my VBA function?
Check the spelling in the cell formula, whether the function is public and in a standard module, and whether the workbook containing it is open. Also check that the code is saved and that macro security settings allow the workbook’s VBA to be used.
Related
- Opposite of Text to Columns in Excel: Combine Cells
- Group Rows in Excel: How to Collapse and Expand Data
- Conditional Formatting Multiple Cells in Excel: Rules
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA InputBox With Two Fields: Practical OptionsVBA’s standard InputBox accepts one response field, not two side-by-side fields. To collect two values at once, create…
- Excel VBA Active Pivot Table: How to Find and ControlIn Excel VBA, get the PivotTable associated with the active cell by using ActiveCell.PivotTable; Excel does not provide…
- Input Box in Excel VBA: Use InputBox and Get DataIn Excel VBA, use InputBox to ask for a text value, or Application.InputBox when you want to restrict…
- Running Macros in Google Sheets: How to Run and Manage ThemTo run a macro in Google Sheets, open the spreadsheet in a web browser, choose Extensions > Macros,…
- Excel VBA: Insert a Row and Shift Cells DownTo insert a worksheet row in Excel VBA and push the existing rows down, use Rows(rowNumber).Insert Shift:=xlShiftDown on…
- Set Image Height in Excel VBA Using InchesTo set an image to a height measured in inches, assign its VBA Height property the result of…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.