Excel VBA function syntax starts with a Function statement and ends with End Function; the function returns a value by assigning it to its own name. A typical declaration is Public Function FunctionName(argument As DataType) As ReturnType, followed by the calculation and an assignment to FunctionName.
The Basic Function Structure
A VBA function is a named procedure that can calculate and return a result. Its declaration identifies its name, any arguments it accepts, and the type of value it returns. The body contains the statements that perform the work. The general pattern is:
[Public | Private] Function FunctionName([arguments]) [As ReturnType]
' Statements that calculate a result
FunctionName = result
End Function
The square brackets in this pattern mean that an item is optional; do not type the brackets into your actual procedure. The vertical bar means “or”: choose one access level, such as Public or Private. For example, a public function can be called from a worksheet when it is placed in a standard module. A private function is restricted to its containing module.
A function declaration is not the same as a worksheet formula. The declaration and calculation are written in the VBA editor, while a worksheet formula calls the function by name. VBA keywords such as Function, As, and End Function must be spelled correctly, but capitalization is not significant.
What Each Part of the Syntax Means
PublicorPrivate: Controls where the procedure can be called from. A worksheet function normally needs to be public.FunctionName: The identifier used in VBA and, for a suitable public function, in a worksheet formula. Use a name without spaces, and avoid names that conflict with built-in functions.- Arguments: The inputs supplied by VBA code or worksheet cells. Each argument can have a declared data type and optional passing or default-value settings.
As ReturnType: Specifies the type of value the function returns, such asDouble,String, orBoolean.End Function: Marks the end of the procedure. It is required even when the function contains only one calculation.
If you omit the return type, VBA treats the function as returning a Variant. That flexibility can be useful in limited cases, but explicitly declaring a type makes the intended result clearer and can help catch mistakes. The type should match the result you expect to produce, not just the type of one input.
Returning a Value from a Function
VBA does not use a return statement like some other programming languages. Instead, assign the result to the function’s own name. The following example calculates a simple tax amount. It uses an optional rate so that a caller can either accept the default or provide a different rate.
Option Explicit
' Returns tax on an amount using the supplied rate or the default rate.
Public Function SalesTax(ByVal amount As Currency, _
Optional ByVal rate As Double = 0.08) As Double
SalesTax = amount * rate
End Function
Here, SalesTax is both the procedure name and the variable that receives the return value. The input named amount is declared as Currency, and the function’s result is declared as Double. The underscore continues the declaration onto another line; it must be preceded by a space. Option Explicit requires variables to be declared before use, reducing errors caused by misspelled variable names.
For a function with several possible paths, assign a result on every path that is meant to succeed. You can use Exit Function to leave early after assigning a result, for example when an input meets a special condition. If the function reaches End Function without assigning a value, it returns the default value for its declared type, which is often not the intended answer.
Arguments, Data Types, and Optional Inputs
Arguments make a function reusable: rather than hard-coding a number or cell address into its calculation, you pass the value in when calling it. In VBA, an argument can be passed ByVal or ByRef. ByVal passes a value to the procedure; ByRef passes a reference to the variable. If neither is written, VBA uses ByRef by default. Declaring an argument ByVal is often a clear choice when the function should use an input without changing the caller’s variable.
Use Optional when a caller may leave an argument out. In the example, the default rate is 0.08, so a call that supplies only an amount uses that rate. Optional arguments must follow required arguments. A default value must be a permitted constant value; it is not a place to put a calculation that should be evaluated later. If an optional argument has no default, code may need to check whether it was supplied before using it.
Choose data types based on the values involved. Long is a common integer type, Double supports decimal calculations, Currency is intended for fixed-point monetary values with four decimal places, and String holds text. Boolean represents True or False. A Variant can hold different kinds of values, but using it unnecessarily can make a function’s behavior less predictable. A parameter declared ParamArray can accept a varying number of arguments; it must be the final argument and is handled as an array of Variants.
Writing and Adding a Function in Excel
In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, or use Developer > Visual Basic if the Developer tab is visible. In the editor, choose Insert > Module, then place a public worksheet function in that standard module. A function stored in a worksheet or workbook object module is not the normal location for a user-defined worksheet function.
- Open a copy of the workbook and insert a standard module.
- Type the function declaration, its calculation, and
End Function. - Return to the worksheet and enter a formula that calls the function.
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) so its VBA project is retained.
With the example function above, assume cell A2 contains the taxable amount 100 and the function’s default rate is 0.08. Enter this formula in another cell:
=SalesTax(A2)
The result is 8, because 100 multiplied by 0.08 equals 8. If the function is not recognized, check that the code is in a standard module, the function is public, the workbook is saved in a macro-enabled format, and macros are permitted under your organization’s security settings. Excel for the web can open an .xlsm workbook, but it cannot create, edit, or run VBA macros. On Mac, the VBA editor is available in desktop Excel; Option+F11 is a common shortcut, though some keyboards require the Fn key as well.
Calling a VBA Function from a Worksheet
A public function in a standard module can be called in a worksheet much like a built-in function: type an equals sign, the function name, and its arguments in parentheses. Separate multiple arguments with commas in the formula bar. If an argument is optional, you can omit it when it is at the end of the argument list, as in the tax example, or provide a value explicitly. The value passed from a cell is the cell’s value, not a way for the function to edit that cell.
Worksheet functions should calculate and return results rather than change the workbook. A VBA user-defined function called from a cell is not intended to write to other cells, change formatting, or perform actions such as displaying a dialog. Use a Sub procedure for actions that change workbook contents or interact with the user. A function can still be useful for a custom calculation, text transformation, or decision that returns a value to its calling formula.
Excel recalculates a custom function when its referenced input cells change in ordinary cases. If a function depends on information Excel cannot track as a cell argument, its result may not refresh when expected. Application.Volatile can make a VBA function recalculate whenever calculation occurs, but doing so broadly can slow a workbook. Prefer passing dependencies as arguments so Excel can track them.
Function Versus Sub: Which One Should You Use?
Both Function and Sub procedures contain VBA statements, but they serve different purposes. A function returns a value to its caller. A Sub performs actions and does not return a value in the same way. Use a Function when the goal is to calculate something that another procedure or worksheet formula can use. Use a Sub for tasks such as formatting a report, copying data, or responding to a button click.
For example, a custom worksheet function can return a calculated shipping charge for a row. A Sub could then be used to format an entire report or export a range. Although a function can call other procedures in VBA, a function used as a worksheet formula should be designed to return a dependable result without side effects. Keeping calculations and workbook-changing actions separate makes the code easier to understand and maintain.
A worksheet formula also does not automatically run a Sub. A macro must be started through an allowed method, such as the Macro dialog or a button assigned to it. This distinction helps avoid a common misconception: adding the word Function to a procedure does not make any VBA routine callable from a worksheet. It must have a suitable declaration and return a value.
Common Syntax Problems and Safe Use
Small declaration mistakes can prevent a function from compiling or appearing in a worksheet. When Excel reports an error or displays #NAME?, inspect the declaration and workbook setup before rewriting the calculation. Common issues include:
- Leaving out
End Functionor misspelling the function name in the worksheet formula. - Putting a worksheet function in a sheet or workbook object module instead of a standard module.
- Declaring the function
Privatewhen it needs to be called from a worksheet. - Using an argument without declaring it, or assigning a value to a different name from the function name.
- Saving the workbook as
.xlsx, a format that does not preserve its VBA project. - Expecting an Excel for the web session to run VBA, or having macros blocked by security settings.
VBA macros can make substantial changes to a workbook, and Excel’s macro security controls are there for a reason. Run code only when you trust its source and understand what it does; do not enable all macros simply to make an unfamiliar file work. Before running a macro that changes workbook data, save a separate copy. Macro actions generally cannot be reversed with Ctrl+Z. On Mac, use Command+Z for ordinary undo, but it likewise does not provide a dependable undo for a VBA macro.
Frequently Asked Questions
What is the basic syntax for a VBA function?
A function begins with a declaration such as Public Function Name(argument As DataType) As ReturnType, contains its calculation, assigns the result to Name, and ends with End Function.
How does a VBA function return a value?
Assign the result to the function’s own name inside its procedure. VBA does not use a separate return keyword for an ordinary function result.
Can I use a VBA function in an Excel cell?
Yes, if it is a suitable public function in a standard module in a workbook with its VBA project preserved. Call it from a cell using an equals sign, the function name, and the required arguments.
Why does my VBA function show #NAME? in Excel?
Check the spelling of the worksheet call, the function’s public access, its module location, and whether macros are available in the desktop workbook. Also confirm that the workbook was not saved in a format that removed the code.
Can I use VBA functions in Excel for the web?
No. Excel for the web does not run VBA or provide the VBA editor. Use desktop Excel for Windows or Mac to work with VBA code and execute it.
Choosing the Right Function Design
A well-designed function has a clear name, explicit argument and return types, and one understandable purpose. Give it all the inputs it needs rather than relying on hidden worksheet state, and return a value instead of changing unrelated cells. When the result may be invalid, decide how the function should handle that situation and document the behavior in a short comment. For more involved workbook automation, keep the calculation in a function and place the actions in a separate Sub. This structure makes the syntax easier to follow and helps readers distinguish a formula they can reuse from a macro that changes the workbook.
Related
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Assign Range: Set, Select, and Reference CellsTo assign a cell range to a VBA variable, declare the variable as a Range and use Set,…
- VBA Code for UserForm: Build an Excel Data Entry FormTo create a VBA UserForm in Excel, add a UserForm in the Visual Basic Editor, place controls on…
- 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…
- Excel VBA: Create an Outlook Email Draft in WindowsYes. In Excel for Microsoft 365 on Windows, VBA can use classic Outlook’s object model to create an…
- Excel VBA Create Named Range: Code, Scope, and TipsTo create a named range with Excel VBA, add a name to the workbook’s Names collection and set…
- Excel UserForm Images: Add, Size, and Fix Picture IssuesTo add an image to an Excel VBA UserForm, open the form in the Visual Basic Editor, place…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.