Excel VBA Function Output: Return Values and Display

VBA & macros · Updated November 21, 2026

An Excel VBA function outputs a value by assigning that value to the function’s name. That value appears in a worksheet cell when the function is used in a formula, or is passed back to the VBA code that called it; a function does not display a result on screen automatically.

For example, a function named TaxInclusive can return a calculated price. Enter it in a worksheet formula to show the result in a cell, or call it from a procedure and decide where that procedure should send the result.

What “function output” means in Excel VBA

In VBA, a Function procedure performs a calculation or other task and returns one value to its caller. The output is the value returned, not necessarily something printed or displayed. A Sub procedure, by contrast, performs actions but does not return a value in the same way.

VBA does not use a separate Return statement for an ordinary function result. Instead, assign the result to the function’s name. The declared return type determines what kind of value Excel should expect, such as a number, text, date, or Boolean value. A function declared As Variant can return different types of values, including an Excel error value.

This distinction explains a common point of confusion: creating a function does not make its answer appear somewhere by itself. The caller determines where the returned value goes. A worksheet formula puts it in the formula cell; a VBA procedure can store it in a variable, write it to a range, print it in the Immediate Window, or show it in a message box.

How a function returns a value

Here is a small example. It accepts a price and a tax rate, calculates the tax-inclusive amount, and returns the result through the function name.

Function TaxInclusive(ByVal price As Double, ByVal rate As Double) As Double
    ' Return the price after adding tax.
    TaxInclusive = price * (1 + rate)
End Function

The assignment to TaxInclusive is the key line. If the inputs are 50 and 0.1, the calculation is 50 × 1.1, so the returned value is 55. The parameters use ByVal to make clear that the function receives copies of the supplied values rather than changing the caller’s variables.

In a function with several possible outcomes, assign the intended result on each path. For example, an If block might return one value when a condition is true and another when it is false. If execution reaches the end without assigning a result, VBA returns the default value for the function’s type—often zero for a numeric type or an empty string for a string type—which can make a missing assignment difficult to spot.

Show the output in a worksheet cell

To use a VBA function in a worksheet formula, put the function in a standard code module in the workbook, then enter its name and arguments in a cell. For the tax example, suppose A2 contains 50 and B2 contains 10%. In C2, enter =TaxInclusive(A2,B2). The result is 55; format C2 as currency if you want it displayed as a monetary amount.

To add a standard module 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 module. You can also open the editor from the Developer tab by choosing Developer > Visual Basic. If Developer is not visible, enable it through File > Options > Customize Ribbon and select Developer.

On Mac, use Developer > Visual Basic to open the editor; depending on the keyboard, Option+F11 may require the Fn key as well. VBA is not available in Excel for the web, so a workbook’s VBA function cannot be created or run there. The workbook must be opened in the desktop Excel application that supports VBA.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it needs to retain its VBA code. When opening a macro-enabled file, only enable macros if you trust the workbook and its source. Excel may otherwise block the code, leaving the custom function unavailable or showing an error such as #NAME?.

Send the result to VBA code instead

A VBA procedure can call a function and choose what to do with the returned value. This example stores the answer in a variable and prints it to the Immediate Window in the Visual Basic Editor.

Sub ShowTaxResult()
    Dim result As Double

    ' Call the function and store its returned value.
    result = TaxInclusive(50, 0.1)
    Debug.Print result
End Sub

Open the Immediate Window in the editor to see the output from Debug.Print. This is useful while developing or checking calculations, but it does not put the answer in a worksheet cell. To write a value to a cell from a procedure, assign it to a range, for example Worksheets(1).Range(A1).Value = result is not valid as written because VBA range addresses must be text. The valid form is Worksheets(1).Range("A1").Value = result.

A message box is another way a procedure can show a result temporarily, while a cell is usually more useful when the value should remain visible or be used by other formulas. These are choices made by the calling procedure; they are not automatic behaviors of the function itself.

Before running a macro that writes to a workbook, save a copy of the file. Macro actions cannot be undone with Ctrl+Z. On Mac, use Command+Z for ordinary undo, but it also cannot reverse changes made by a running macro.

Return errors and handle invalid inputs

A function can return an Excel error value when an input makes a calculation invalid. For instance, division by zero can return the familiar #DIV/0! worksheet error rather than an unhelpful numeric result.

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

The return type is Variant because the function may return either a number or an error value. For example, with numerator 12 and denominator 0, the function returns Excel’s division-by-zero error. If the function is used in a worksheet formula, Excel displays #DIV/0!; another formula can detect that error with functions such as ISERROR.

VBA can also raise a run-time error if a value cannot be converted to the declared parameter type. If a function may receive blank cells, text, or other unexpected values, decide how it should handle them instead of assuming every input is a valid number. A helpful function returns a clear error or defined fallback rather than silently producing a misleading answer.

Functions that return more than one value

A function can return an array, generally by using a Variant return type and assigning an array to the function name. This can be useful when a calculation naturally produces several related results. However, returning an array is different from writing arbitrary values to worksheet cells: the caller controls how the returned array is used, and the worksheet’s array-formula behavior can depend on the Excel version and how the formula is entered.

For a predictable procedure that fills a known block of cells, a Sub is often the clearer tool. It can calculate values and write them to a specified range, while a worksheet function is best suited to returning a value for the cell containing its formula. A worksheet function should not try to edit other cells, change formatting, or otherwise modify the workbook as a side effect of recalculation.

This is also why a function used in a cell should generally be treated as a calculation rather than a command. Excel may recalculate formulas when inputs change, and a function that tries to perform unrelated actions during recalculation can behave unexpectedly or fail.

Common output problems and how to fix them

If a custom function does not show the expected output, check the place where the code is stored, its name, its return assignment, and whether macros are available. The following checks address frequent causes:

  • #NAME? in the cell: Check the spelling of the function name, confirm the function is in a standard module in the correct workbook, and make sure the workbook’s VBA is enabled. A function stored as a private procedure or in a location unavailable to the formula may not be callable from a cell.
  • A result of zero or blank: Look for a code path that reaches the end without assigning a value to the function name. Also check that the function’s declared return type and the values passed to it are appropriate.
  • A run-time error: Check for invalid inputs, type mismatches, or operations such as dividing by zero. Handle expected invalid cases explicitly where possible.
  • The Immediate Window is empty: Confirm the calling Sub actually ran and that it contains a Debug.Print statement. The Immediate Window is separate from the worksheet.
  • The function works on one computer but not another: Check whether macros are blocked, whether the file was saved as .xlsm, and whether the other person is using desktop Excel rather than Excel for the web.

Also check whether a worksheet function is being asked to change the workbook. A UDF called by a cell formula is intended to return a result, not to write to other cells. If the task is to fill cells, update formatting, or perform a one-time action, use a Sub or another suitable Excel feature.

When to use a VBA function, a Sub, or a built-in formula

Choose a VBA Function when you need a reusable calculation that returns a value, especially if the logic is difficult to express with ordinary worksheet formulas. Choose a Sub when the task is to take action—for example, write results to several cells, format a report, or process a sheet. A built-in Excel formula is usually preferable when it can do the job clearly, because it does not require macro-enabled files or users to enable VBA.

Consider who will open the workbook before choosing VBA. Some organizations restrict macros, Excel for the web cannot run VBA, and users may be less willing to enable code in a file they did not create. For a workbook shared widely, a built-in formula or another supported approach may be easier to maintain. For a personal or controlled workflow, a well-documented VBA function can be a practical way to reuse custom logic.

Frequently Asked Questions

How do I return a value from a VBA function?
Assign the value to the function’s name, such as TaxInclusive = price * (1 + rate). The caller then receives that value.

Why does my VBA function show #NAME? in Excel?
Check that the function is spelled correctly, is in a standard module in the workbook, and is not marked Private. Also confirm that macros are enabled and that you are using desktop Excel, not Excel for the web.

Does a VBA function automatically write its answer to a cell?
No. It returns a value to its caller. When used in a worksheet formula, the formula cell displays that value; a VBA procedure must explicitly write a value to a range if that is the intended destination.

What is the difference between a VBA Function and a Sub?
A Function returns a value that can be used by a formula or another procedure. A Sub performs actions but does not return a function value. Use a Sub for tasks such as updating multiple cells.

Can a VBA function run in Excel for the web?
No. Excel for the web does not create, edit, or run VBA code. Open the workbook in a desktop version of Excel that supports VBA to use the function.

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.