VBA Function Return Values: How to Return a Result

VBA & macros · Updated November 8, 2026

In VBA, a function returns a value by assigning that value to the function’s own name; VBA does not use a separate return statement. For example, inside Function DoubleIt(number As Long) As Long, the line DoubleIt = number * 2 sets the result that the function gives back to its caller.

What a VBA function returns

A VBA Function is a procedure designed to produce a result. The result might be a number, text, a Boolean value, a date, an object, an array, or a Variant, depending on the function’s declared return type. The code that calls the function can store that result, use it in a calculation, display it, or pass it to another procedure.

The function’s name serves two purposes: it identifies the procedure, and inside the procedure it acts like a variable that holds the return value. This is different from languages that use a keyword such as return followed by an expression. In VBA, writing Return 10 is not the way to return a value. Exit Function is valid, but it only leaves the function; it does not itself specify the result.

A Sub procedure, by contrast, performs actions but does not return a value to its caller. If a task needs to calculate and provide a result, use a Function. If it only needs to carry out an action, such as formatting a range, a Sub is usually the appropriate procedure type.

The basic syntax

Declare the function’s return type after its parameter list, using As. Then assign the result to the function name. This example returns a whole-number result:

Public Function DoubleIt(ByVal number As Long) As Long
    ' Return twice the supplied number.
    DoubleIt = number * 2
End Function

When another procedure calls DoubleIt with 6, the result is 12. A caller can store the returned value in a variable, as shown here:

Public Sub ShowDoubledValue()
    Dim result As Long

    ' Save a copy of the workbook before running VBA code.
    ' Macros cannot be undone with Ctrl+Z.
    result = DoubleIt(6)
    MsgBox result
End Sub

In this example, the assignment on the right calls the function, and the function’s result is placed in result. The message box displays 12. Save a copy of a workbook before running a macro that changes it: macro actions cannot be undone with Ctrl+Z.

Choose and declare a return type

The return type tells VBA what kind of value the function is expected to provide. Common choices include Long for whole numbers, Double for numbers that can include decimals, String for text, Boolean for True or False, and Currency for currency-style arithmetic. The type should match the result the function is meant to represent, not just the type of its first input.

For example, a calculation involving a fractional rate should not return a Long if the fractional part matters: converting to a whole-number type can discard the decimal portion. Similarly, a function intended to return text should declare As String. Declaring types makes the procedure’s purpose clearer and helps VBA detect incompatible assignments.

If a function has no explicit return type, VBA treats its return value as a Variant. A Variant can hold different kinds of values, but that flexibility can make results less predictable and introduce avoidable type conversions. Use a specific type when the function has a well-defined result. A Variant is useful when the function genuinely needs to return different kinds of values, such as either a normal result or an Excel error value.

If the code reaches End Function without assigning a result, VBA returns the type’s default value. Depending on the type, that may be zero, an empty string, False, Empty for a Variant, or Nothing for an object. Those defaults can look like legitimate results, so explicitly assign a value on every intended path.

Return values from different paths

A function can assign its return value in an If block or other control structure. For example, a Boolean function can express a yes-or-no test:

Public Function IsEven(ByVal number As Long) As Boolean
    ' Return True for even numbers and False otherwise.
    IsEven = (number Mod 2 = 0)
End Function

The comparison in parentheses evaluates to True or False, which is assigned to IsEven. A caller can use the result in an If statement, or a worksheet can use the function as a custom formula if it is set up as a worksheet function.

Some functions need to stop early when they encounter a special case. Assign the result first, then use Exit Function to leave immediately:

Public Function SafeDivide(ByVal numerator As Double, ByVal denominator As Double) As Variant
    ' Return an Excel division error when the denominator is zero.
    If denominator = 0 Then
        SafeDivide = CVErr(xlErrDiv0)
        Exit Function
    End If

    SafeDivide = numerator / denominator
End Function

This function returns an Excel error value for a zero denominator and a numeric result otherwise. Its return type is Variant because it needs to accommodate both a number and an error value. Exit Function ends the procedure after the error result has been assigned; without that assignment, exiting would leave the function’s default result instead.

Returning an object

A function can return an object, such as a worksheet, range, or workbook. The declaration names the object type, and the assignment must use Set. For example:

Public Function FirstInputCell(ByVal target As Range) As Range
    ' Return the first cell in the supplied range.
    Set FirstInputCell = target.Cells(1, 1)
End Function

Here the function returns a Range object, not the cell’s displayed value. The distinction matters: without Set, VBA treats the assignment as a value assignment rather than an object reference. If the caller wants the cell’s contents, it can read the returned range’s Value property. If it wants to store the Range object itself in an object variable, the caller also uses Set.

For instance, when assigning the result to a Range variable, the calling code follows the form Set selectedCell = FirstInputCell(Worksheets("Sheet1").Range("A1:B2")). The exact worksheet and range should be changed to match the workbook. An object-returning function should also account for cases where no suitable object exists; returning Nothing may be appropriate, but the caller then needs to check for Nothing before using the result.

Use a VBA function in a worksheet

A public function in a standard VBA module can often be called from a worksheet cell like a built-in function. For example, after adding DoubleIt to a standard module in a macro-enabled workbook, enter =DoubleIt(6) in a cell. The cell displays 12. A worksheet function’s declared arguments become the inputs users provide in the formula, and its returned value becomes the cell result.

For a custom function to be available in this way, it generally needs to be a Public Function in a standard module, and its arguments and return value need to be suitable for worksheet use. A function declared Private, or one placed in a worksheet or workbook object module, will not be available in the usual way from a cell formula. Save the workbook in a macro-enabled format such as .xlsm if it needs to retain its VBA project.

Worksheet functions should calculate and return results rather than change the workbook. A function called from a cell is not a substitute for a macro that edits other cells, changes formatting, or performs interactive actions. Use a Sub for those tasks. Also remember that Excel recalculates formulas, so a worksheet function may run more often than expected; avoid unnecessary side effects and design it to return a result consistently from its inputs.

Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros. Use the desktop Excel application to work with VBA. Excel for Mac also supports VBA, though the Visual Basic Editor and some keyboard shortcuts differ from Windows; the function syntax and the assignment-to-function-name rule are the same.

How to call a function and keep its result

In VBA code, use the function call as part of an expression when you need its result. Assign it to a variable, compare it, or use it in a larger calculation. For example, result = DoubleIt(6) preserves the result for later use. A function can also be nested in an expression, but assigning the result to a clearly named variable can make complicated code easier to read and troubleshoot.

If a function is called as a standalone statement, its returned value may be discarded. That is usually a sign to check the code: perhaps the caller should save the result, or perhaps the procedure should be a Sub because its purpose is an action rather than a calculation. VBA also permits a call using the Call statement, but when using Call, put the arguments in parentheses; the function’s returned value is not captured by that form.

Keep in mind that a function’s return value is distinct from a parameter passed with ByRef. A ByRef argument lets a procedure change the variable supplied by its caller, while a function result is the value assigned to the function name. Unless changing an input is intentional, ByVal is often a clearer choice for function parameters because the function receives a value rather than a variable it is expected to modify.

Common return-value mistakes

  • Using a nonexistent return statement: Assign the value to the function name. Use Exit Function only to leave early after setting any needed result.
  • Forgetting an assignment on one branch: Review every path through the function. An unassigned path returns a default value, which may conceal a logic error.
  • Declaring the wrong type: Choose a return type that can represent the full result. Use Variant only when its flexibility is needed, not simply because the result type was left undecided.
  • Omitting Set for an object: Use Set FunctionName = objectReference when returning a Range or another object.
  • Expecting a worksheet function to act like a macro: A formula-oriented function should calculate a value. Use a Sub for changes to workbook contents or other actions.
  • Confusing the function result with a changed argument: A return value is assigned to the function name; a ByRef parameter is a separate mechanism that can modify a caller’s variable.

When a result seems wrong, check the declared type, the line that assigns the function name, and every conditional branch. Then check how the caller uses the result. A value may be returned correctly but converted, ignored, or displayed differently by the calling code.

Frequently Asked Questions

Does VBA have a return statement?
No. Return a value by assigning it to the function’s name. Exit Function is available to end a function early, but it does not supply the value.

What happens if I do not assign a function’s name?
The function returns the default value for its declared type, such as zero for a numeric type, False for Boolean, or Nothing for an object. Assign a result on every intended execution path to avoid confusing defaults.

Can a VBA function return more than one value?
A function has one return value, but that value can be a structure such as an array or an object that contains multiple pieces of information. Another option is to use additional ByRef parameters, though that is different from returning multiple function values.

How do I return a Range from a function?
Declare the function As Range and assign the object with Set, such as Set FunctionName = someRange. Check for Nothing where the function might not find a valid range.

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 macros enabled. Excel for the web does not run VBA, so use desktop Excel to calculate formulas that depend on VBA functions.

Choosing the right pattern

For most calculations, declare a specific return type, assign the function name, and make sure every branch produces a meaningful result. Use Exit Function when an early result is needed, Set when returning an object, and Variant when the function genuinely needs to return different kinds of values. Finally, decide whether the procedure is meant to calculate a result or perform an action: use a Function for the former and a Sub for the latter. That distinction makes VBA code easier to call, test, and maintain.

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.