Excel VBA Function Return Value: Syntax and Examples

VBA & macros · Updated November 21, 2026

An Excel VBA function returns a value by assigning that value to the function’s own name before the function ends. For example, AddNumbers = firstNumber + secondNumber sets the result; unlike some languages, VBA does not use a Return statement.

What a VBA function returns

A function is a VBA procedure designed to produce a result. That result might be a number, text, a date, a Boolean value such as True or False, an Excel error value, or—in certain VBA contexts—an object reference. A function can be called by another VBA procedure, and a public function in a standard module can also be used in an Excel worksheet formula.

This is different from a Sub procedure. A Sub performs instructions but does not return a value that can be assigned to a variable or displayed as a formula result. Use a Function when the caller needs a result; use a Sub when the main purpose is an action such as formatting cells or opening a dialog.

One point can be confusing: the function name is also the name of a special return-value variable inside the function. Assigning to that name stores the result that VBA sends back when execution reaches End Function or exits with Exit Function.

Basic function syntax

A VBA function starts with a declaration and ends with End Function. The As clause states the type of value returned. Arguments in parentheses provide values to the function. For example, a function that adds two numbers can be written as follows:

Public Function AddNumbers(ByVal firstNumber As Double, ByVal secondNumber As Double) As Double
    ' Store the sum in the function name to return it.
    AddNumbers = firstNumber + secondNumber
End Function

Here, Public makes the function available outside its module, ByVal passes each argument by value, and Double is the declared data type for both inputs and the result. The line assigning the sum is the crucial part. If the function reaches its end without that assignment, it returns the default value for its type—in this example, zero.

A function can contain several assignments to its name. The last value assigned before it exits is the value returned. This allows a function to choose among results using conditions or loops:

Public Function LabelForScore(ByVal score As Double) As String
    ' Choose a label based on the score.
    If score >= 60 Then
        LabelForScore = "Pass"
    Else
        LabelForScore = "Review"
    End If
End Function

Returning a value to another VBA procedure

In VBA code, assign a function call to a variable when you need to keep or use its result. For instance, a procedure can store the result of AddNumbers in a variable declared as Double, then display it or use it in further calculations. The declared variable should be compatible with the value the function returns.

A function may also be called when its result is not needed, but that can make the code harder to understand. If the procedure is meant to carry out an action and callers do not need a result, a Sub is usually clearer. If you use the Call statement with a function, its return value is ignored; this is rarely useful when designing new code.

Within a function, Exit Function stops execution immediately and returns the value currently assigned to the function name. It does not assign a value by itself. Therefore, set the return value before using Exit Function if the caller needs a particular result.

Choosing the return data type

Choose a return type that fits the result and declare it after the argument list. Common choices include Long for whole numbers, Double for numbers that may have decimals, String for text, Date for dates and times, and Boolean for True or False. Using a clear type helps VBA detect incompatible assignments and makes the function’s purpose easier to read.

If the return type is omitted, VBA treats the function’s return value as a Variant. A Variant can hold different kinds of values, but it may make behavior less explicit. Use it when the function genuinely needs to return different kinds of results, such as either a normal result or an Excel error value, rather than as a default for every function.

For an object return value, the function must return an object reference rather than a normal value. In VBA, assign that reference with Set. Object-returning functions are useful in VBA automation, but they are not the normal way to create a worksheet custom function: worksheet formulas generally return values such as numbers, text, or errors.

Using a VBA function in an Excel cell

To make a custom function available in a worksheet formula, put a Public Function in a standard module. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer. In the editor, use Insert > Module, then paste the function into that module.

With the function in a standard module, enter its name and arguments in a worksheet cell just as you would for a built-in function. For example, with the two numbers in A1 and B1, enter this in C1: =AddNumbers(A1,B1). If A1 contains 12 and B1 contains 8, C1 displays 20. The formula passes the cell values to the VBA function, and Excel displays the value assigned to the function name.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain its VBA code. Excel for the web can open and edit many workbook contents, but it cannot create, run, or edit VBA macros; use the desktop Excel app for this function. On Mac, VBA is available in desktop Excel, although the ribbon layout and some editor interactions can differ from Windows.

A worksheet custom function is often called a UDF, short for user-defined function. The function should be public and stored in a regular module for ordinary worksheet use. A function declared Private, or one placed in a worksheet or workbook object module, may not be available in the same way as a standard-module UDF.

Errors, arrays, and function limitations

A function can return an Excel error value instead of an ordinary result. In VBA, CVErr creates an error value, and constants such as xlErrValue or xlErrNum identify error types. A function declared As Variant can return such a value, which Excel then displays as a worksheet error. Choose an error that meaningfully describes the problem; do not silently substitute zero if that could be mistaken for a valid calculation.

VBA functions can also return arrays in some situations. A worksheet UDF that returns an array may provide multiple results, but how those results appear depends on Excel’s array-formula behavior and the function’s implementation. For ordinary scalar results, a single number, text value, date, Boolean, or error is simpler to maintain.

Worksheet UDFs have important limits: they should calculate and return a result, not act like macros that change the workbook. A UDF called from a cell cannot reliably edit other cells, change formatting, or perform many actions that alter Excel’s environment. Use a Sub or another macro for those tasks. Also, functions that rely on external state or volatile information may not recalculate exactly when expected unless their inputs or calculation settings prompt a recalculation.

Common return-value problems

  • The cell shows #NAME? Check the spelling of the function name, confirm the function is public and in a standard module, and make sure the workbook containing it is open. Also confirm that macros are permitted for this trusted workbook.
  • The result is always zero or blank. Look for a missing assignment to the function name or a code branch that exits before assigning it. A numeric function that never sets its return value defaults to zero; a string function defaults to an empty string.
  • The result has the wrong type. Check the declared return type and any implicit conversions. For example, assigning text to a numeric return type can cause a type mismatch. Declare the intended types and validate inputs where necessary.
  • Changing an input does not update the result. A worksheet UDF normally recalculates when Excel detects a change to one of its referenced arguments. If the function reads cells indirectly in VBA rather than receiving them as arguments, Excel may not know those dependencies. Pass relevant values as arguments where practical.
  • The function is missing after reopening. Make sure the file was saved in a macro-enabled format rather than as a macro-free .xlsx file, which does not preserve VBA project code.

Only enable macros in workbooks from sources you trust. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z, even when the code is packaged as a function.

Frequently Asked Questions

How do I return a value from a VBA function?
Assign the result to the function’s name, such as MyFunction = result. VBA returns that assigned value when the function ends.

Does VBA use a Return statement?
No. Standard VBA functions return values by assigning to the function name. Use Exit Function to leave early, but assign the intended result before exiting.

What happens if I do not assign a return value?
The function returns its type’s default value: for example, zero for a numeric type or an empty string for String. A Variant is initially Empty. This can look like a valid result, so check every code path.

Can I use a VBA function in an Excel formula?
Yes. Put a public function in a standard module in a macro-enabled workbook, then call it in a worksheet formula such as =AddNumbers(A1,B1). VBA functions do not run in Excel for the web.

What is the difference between a VBA Function and a Sub?
A Function returns a value that code or a worksheet formula can use. A Sub performs instructions and does not return a value. Choose based on whether the caller needs a result.

Related

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.