VBA Function Syntax: How to Write and Call Functions

In VBA, a function starts with a Function declaration, contains statements that calculate a result, and ends with End Function. Assign the return value to the function’s name, then call it from VBA or, when it is a public function in a standard module, from an Excel worksheet formula.

The Basic VBA Function Structure

A function declaration specifies the function’s name, any arguments it accepts, and optionally the data type it returns. Its general pattern is [Public or Private] Function Name(arguments) As ReturnType, followed by the function body and End Function. The brackets in that pattern mean an item is optional; they are not typed into the declaration.

For example, a function might accept two numbers and return a result as a Double. The lines between the declaration and End Function are its body. When the calculation is complete, assigning a value to the function’s name sets the result. VBA does not use a separate return statement as some other programming languages do, although Exit Function can stop execution early.

The access keyword controls where code can call the function. Public makes it available outside its module, while Private limits it to the module where it is declared. If neither keyword appears, a function in a standard module is public by default. Functions can also use the Static keyword, which preserves local variable values between calls; this is a specialized behavior, not a requirement for an ordinary function.

A Complete Function Example

This example calculates a sale total after adding tax. The tax rate is optional, so callers can use the default rate or supply another one. The arguments and local variable are declared, and the final result is assigned to SalesTax, the function’s name.

Public Function SalesTax(ByVal subtotal As Currency, Optional ByVal rate As Double = 0.08) As Currency
    ' Add tax to the supplied subtotal.
    Dim tax As Currency
    tax = subtotal * rate
    SalesTax = subtotal + tax
End Function

With a subtotal of 100 and the default rate of 0.08, the result is 108. If the caller supplies a rate of 0.1, the result is 110. The Currency type is useful for many ordinary monetary calculations because it stores a fixed number of decimal places, though the appropriate type depends on the calculation and its required precision. This illustrative example is not tax guidance.

Return Values and Data Types

The return type appears after As in the function declaration. Common choices include Long for whole-number counts, Double for floating-point numbers, String for text, Boolean for True or False, and Currency for fixed-point monetary values. A function can also return an object type, such as a Range, when the code is designed to return an object rather than a simple value.

Choose a type that fits the possible result, not merely the first value in a test. For example, an integer type is not appropriate if a calculation may produce a fraction. VBA may convert values between types in some situations, but implicit conversion can produce errors or unexpected rounding. Explicit types make a function’s purpose clearer and help VBA identify incompatible assignments.

If you omit the return type, VBA treats the function’s return value as a Variant. A Variant can hold several kinds of values, which may be useful when a function genuinely needs to return different kinds of results. Otherwise, specifying a type documents what callers should expect and can make type-related mistakes easier to spot. Also remember that declaring a function as As Boolean does not automatically make its result True or False; the function body still needs to assign the intended value.

Arguments, ByVal, ByRef, and Optional Inputs

Arguments are the inputs listed in the function declaration. Each can have a name and a data type, such as ByVal subtotal As Currency. VBA passes arguments ByRef by default, meaning the procedure can work with the caller’s variable itself. With ByVal, it receives a value instead, so assigning a new value to that parameter does not change the caller’s variable. Using ByVal is often a clear choice when a function only needs to read an input.

An argument declared with Optional may be left out when the function is called. You can provide a default value, as in Optional ByVal rate As Double = 0.08. Optional arguments must come after required arguments. If you need to tell whether an optional argument was supplied, use a suitable declaration and check its presence; do not assume that a supplied value of zero is the same as an omitted argument.

A parameter declared with ParamArray lets a procedure accept a variable number of arguments. It must be the last argument in the list and is handled as an array of Variants. It cannot be combined with Optional, ByVal, or ByRef on that parameter. This is useful for functions designed to process a changing number of inputs, but a fixed list of clearly typed arguments is easier to read when the number of inputs is known.

Calling a Function from VBA or a Worksheet

In VBA, you can assign a function’s result to a variable or use it directly in an expression. For instance, code can assign the result of SalesTax(100) to a variable. When calling a function as a standalone statement and discarding its result, VBA’s parentheses rules differ: use Call SalesTax(100) with the Call keyword, or call it without parentheses as SalesTax 100. When capturing the return value, use parentheses around the arguments in the expression.

To use a custom function in a worksheet, put a public function in a standard module. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then choose Insert > Module in the editor and place the function there. On Mac, use Option+F11 to open the editor; some keyboards require Fn as well. The editor’s menus may differ slightly between platforms.

With the example function in a standard module and a subtotal in cell A2, enter =SalesTax(A2) in another cell. If A2 contains 100, the returned value is 108. The formula uses the function’s default rate; to provide a different rate, a worksheet formula can pass a second argument, for example =SalesTax(A2,0.1). Worksheet argument separators can depend on regional settings, so some installations display semicolons instead of commas in formulas.

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) or another format that preserves macros, such as .xlsb. A standard .xlsx file does not preserve VBA code. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Macro security settings also affect whether code runs; only enable macros in files from sources you trust.

Function Versus Sub: Which Should You Use?

Use a Function when the procedure needs to return a value that another expression or worksheet formula can use. A Sub performs actions but has no return value. For example, a function might calculate a discount, while a Sub might format a report or copy information to another location. A function can also perform actions when called from VBA, but a calculation-focused function is usually easier to reuse and understand.

There is an important distinction for worksheet functions. A user-defined function called from a cell is intended to return a result to that cell; it cannot use the worksheet calculation call to change other cells or alter Excel’s environment. If the task is to write values, apply formatting, or automate workbook steps, use a Sub or another appropriate macro instead. Avoid designing a worksheet function around side effects, because spreadsheet recalculation may call functions more than once or at times that are not obvious to the user.

Common Errors and Practical Checks

A function that will not compile often has a mistake in its declaration, argument list, type, or closing statements. VBA requires a matching End Function, and each variable must be declared correctly when the module uses Option Explicit. That setting requires variables to be declared before use and is a helpful safeguard against misspelled variable names.

If Excel displays #NAME? for a custom worksheet function, check that the function name is spelled correctly, the code is in a standard module, and the function is public. Also confirm that the workbook still contains the VBA project, that macros are allowed by the workbook’s security settings, and that you are using desktop Excel rather than expecting the function to calculate in Excel for the web. If a function returns the wrong result, test it with a small known input and inspect the values and types passed to each argument.

For debugging, open the Visual Basic Editor, click inside the function, and use the editor’s debugging tools to step through the code or inspect variables. The VBA editor can identify compile errors before the function runs. A worksheet error such as #VALUE! may instead indicate an invalid input or a runtime problem in the function. Handle errors deliberately: returning a meaningful error value can be more useful than silently substituting zero, which might make an incorrect calculation appear valid.

Before running a macro that changes workbook data, save a separate copy of the workbook. VBA changes cannot be undone with Ctrl+Z; the same warning applies to macro-driven changes on Mac, even though the keyboard shortcut for undo may differ. Keeping a backup is particularly important when trying unfamiliar code or running a procedure over many sheets or rows.

Frequently Asked Questions

What is the simplest VBA Function syntax?

Use Function Name() As Type, put the calculation inside the procedure, assign the result to Name, and finish with End Function. Include arguments in the parentheses when the function needs inputs.

How do I return a value from a VBA function?

Assign the value to the function’s own name, such as SalesTax = subtotal + tax. The declared return type determines the kind of value the function is expected to return.

Can I call a VBA function from an Excel cell?

Yes. Put a public function in a standard module in a workbook that preserves macros, then enter its name in a worksheet formula. Excel for the web does not run VBA functions.

Why does my custom function show #NAME?

Check the spelling, visibility, module location, and whether the workbook still contains its VBA code. Also check macro security and use desktop Excel, since the web version does not calculate VBA functions.

Should I use Function or Sub in VBA?

Use a Function when code needs to return a value. Use a Sub when the goal is to carry out an action, such as formatting cells or updating workbook content, without returning a result to an expression.

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.