VBA Function Statement: Syntax, Scope, and Examples

The VBA Function statement declares a procedure that performs a task and returns a value. You can call a function from other VBA code or, when it is a suitable public function in a standard module, use it in an Excel worksheet formula.

What the Function statement does

A function is a reusable block of VBA code with a name, optional input arguments, and a return value. The Function statement begins that procedure; End Function marks its end. Between them, the code can validate inputs, calculate a result, and assign that result to the function’s own name.

For example, a function named CalculateTotal might accept a price and a tax rate, then return the total. Once defined, its calculation can be reused from several places instead of being copied into multiple macros. A function can also be called by another procedure, which makes it useful for separating a larger program into smaller, understandable pieces.

In Excel, “function” can refer either to a built-in worksheet function such as SUM or to a VBA procedure. A VBA function that is made available to worksheet formulas is often called a user-defined function, or UDF. The VBA statement is the code declaration; it is not itself a worksheet formula.

Basic syntax and required structure

A typical Function procedure has this structure. The brackets in the syntax description mean that an item is optional; they are not typed literally into the VBA editor.

[Public | Private | Friend] [Static] Function name [(arguments)] [As dataType]
    ' Statements that perform the task
    name = returnValue
End Function

Function, a valid procedure name, and End Function form the essential structure. The visibility keyword and Static are optional. An argument list goes in parentheses, and As dataType specifies what kind of value the function returns. For example, As Double declares a numeric return type that can hold decimal values.

Public makes a procedure available outside its module, subject to VBA and Excel rules. It is the usual visibility for a worksheet UDF.
Private limits use to the module where the function is declared.
Friend is for procedures in class modules and is not used for an ordinary worksheet UDF in a standard module.
Static affects variables declared inside the function: their values are retained between calls. It is not needed for most calculations.

If you omit the return type, VBA uses Variant. Declaring a specific type makes the intended result clearer and can help catch incompatible values, so explicit types are generally preferable when the result type is known.

How a function returns a value

VBA does not use a separate return statement to supply a function’s result. Instead, assign the value to the function’s name. The assignment can appear before the end of the procedure, and the procedure returns that value when it finishes.

For a numeric function, the pattern is FunctionName = calculation. For a function that returns an object, use Set, as in Set FindSheet = ThisWorkbook.Worksheets("Summary"). Forgetting to assign the function name is a frequent source of unexpected results: the function still ends, but it returns the default value for its declared type, such as zero for a numeric type or an uninitialized value for an object reference.

Exit Function ends a function early. It is useful when an input fails a check or a special case has already been handled. Before exiting, decide what value the function should return; an early exit does not automatically create a meaningful result.

A complete calculation example

This function calculates a price including a tax rate. The arguments use ByVal, meaning the function receives their values without changing the caller’s variables. The return type is Double because the result can include decimal places.

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

To add it in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, then choose Insert > Module and paste the function into the new standard module. You can also open the editor from Developer > Visual Basic. If Developer is not visible, enable it through File > Options > Customize Ribbon. On a Mac, use Developer > Visual Basic; Option+F11 may also open the editor, depending on the keyboard and function-key settings.

With the amount in A2 as 100 and the rate in B2 as 0.08, enter =AddTax(A2,B2) in C2. The result is 108: 100 multiplied by 1.08. The function can also be called from VBA. For example, a Sub procedure could assign AddTax(100, 0.08) to a declared Double variable and then use that result. A worksheet formula is often the simpler option when the answer belongs in a cell.

Arguments, optional inputs, and data types

Arguments pass information into a function. Give each one a name and, where practical, a type, such as ByVal rate As Double. VBA uses ByRef by default if neither ByVal nor ByRef is specified. With ByRef, a procedure can change the variable supplied by its caller; ByVal avoids that kind of change for ordinary values. Choosing deliberately makes a function’s behavior easier to predict.

An Optional argument lets a caller omit an input. Optional arguments must come after required arguments. If you use a default value, the default must be compatible with the declared type. A ParamArray can accept a variable number of arguments, but it must be the last argument and has particular declaration restrictions. These features are useful when the calling pattern genuinely needs flexibility; a short, explicit argument list is usually easier to maintain.

Choose return and argument types to match the data. Long is suitable for many whole-number counts, Double for general decimal calculations, String for text, and Boolean for True/False results. Use Variant when inputs or outputs legitimately need to hold different kinds of values, rather than using it by default. Excel worksheet values can include errors and blanks, so a UDF that accepts arbitrary cell contents may also need to check for those cases.

Where to put a function and how to use it

For a function intended for worksheet cells, a public procedure in a standard module is the usual arrangement. In the Visual Basic Editor, choose Insert > Module to create one. A function marked Private, placed in an unsuitable module, or unavailable in the workbook may not appear as an ordinary worksheet function. Avoid duplicate public function names in the same VBA project because they can create name conflicts.

A workbook containing VBA should be saved in a macro-enabled format such as .xlsm; saving it as a macro-free workbook can discard the VBA project. Excel’s macro security settings also affect whether code is allowed to run. Only enable macros in files you trust, and do not lower security settings simply to make an unfamiliar workbook work.

Functions are called differently depending on their purpose. In a worksheet, type the function name after an equals sign and supply cell references or values in parentheses. In VBA, call it in an expression or assign its returned value to a variable. A function intended only for internal code can be declared Private; a public UDF is needed when worksheet cells must be able to call it.

Worksheet UDFs and their limitations

A worksheet UDF is best used to calculate and return a value. It should not be designed to format cells, change other cell values, insert rows, or perform other workbook actions as a side effect of recalculation. Excel may reject such actions when a function is being evaluated by a worksheet formula, and side effects make recalculation unpredictable. Use a Sub macro for actions that change the workbook.

Pass worksheet inputs as arguments rather than having the UDF quietly read unrelated cells. Excel can track the references supplied in the formula and recalculate when those inputs change. If a function depends on other information that is not represented by its arguments, Excel may not know that the result needs updating. A UDF can be useful for a custom calculation, but a built-in worksheet function is usually preferable when it already does the job: built-ins are easier to share and do not require a macro-enabled workbook.

On Excel for the web, VBA macros cannot be created, edited, or run. A workbook with a VBA UDF therefore needs a desktop version of Excel to calculate that function through VBA. Excel for Mac includes VBA support, though editor shortcuts and some platform-specific behavior can differ from Windows.

Function versus Sub

A Function returns a value; a Sub performs actions and does not return a value in the same way. Choose a function when a caller needs a result to use in a calculation, display in a cell, or store in a variable. Choose a Sub when the purpose is to do something, such as copy a range, create a report, or update workbook content.

This distinction is a design guide, not a claim that functions can never be called from a Sub. VBA code can call a function and use its result. Conversely, a worksheet formula is not a way to run an ordinary Sub. If a procedure’s main purpose is changing the workbook rather than calculating a result, make it a Sub and run it as a macro instead of trying to make it a worksheet function.

Common problems and practical checks

If Excel displays #NAME? for a UDF, check that the function name is spelled correctly, the code is in the intended workbook, and the function is public and available to the worksheet. Also confirm that the workbook still contains the VBA code, that macros are permitted by the applicable security settings, and that you are using desktop Excel rather than Excel for the web.

If the result is wrong, check the argument order, cell contents, and declared types. A rate of 8 entered as a number is not the same input as 8%, which is stored as 0.08 in Excel. If the function returns zero unexpectedly, confirm that every relevant code path assigns a value to the function name. For an object return, verify that the assignment uses Set.

When troubleshooting, test with simple known inputs, then add complexity one step at a time. Use the Visual Basic Editor’s Debug menu to step through code or inspect variables. Before running a macro that changes a workbook, save a separate copy: VBA macro actions generally cannot be undone with Ctrl+Z. On Mac, use the equivalent Undo shortcut, Command+Z, but do not rely on Undo to reverse VBA changes either.

Frequently Asked Questions

What is the purpose of the Function statement in VBA?
The Function statement declares a procedure that can receive arguments, perform instructions, and return a value. It is used for reusable calculations and for creating custom worksheet functions.

How do you return a value from a VBA function?
Assign the result to the function’s own name, such as DoubleIt = value * 2. For an object result, use Set when assigning the object reference.

Can a VBA function be used in an Excel cell?
Yes. A suitable public UDF in a standard module can be called in a worksheet formula, for example =AddTax(A2,B2). The workbook must retain its VBA code, and desktop Excel must be able to run the macro code.

What is the difference between Function and Sub in VBA?
A Function returns a value that code or a worksheet formula can use. A Sub runs instructions without returning a value as its result, so it is the usual choice for a macro that changes a workbook.

Why does my VBA function return zero?
Check whether the code assigns a value to the function name on every path. If it does not, VBA returns a default value for the function’s return type; zero is a common default for numeric types.

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.