Excel VBA Function Multiple Outputs: Practical Methods

VBA & macros · Updated November 21, 2026

Yes. An Excel VBA function can provide multiple outputs by returning an array, or VBA code can use ByRef arguments or a Sub procedure to place results in separate cells. For a worksheet formula, a two-dimensional Variant array is usually the most direct approach; for a procedure called from VBA, output arguments or writing to a range may be easier to control.

What “multiple outputs” means in Excel

A VBA Function has one return value: the value assigned to its own name or to its return variable. That value can be a Variant containing an array, however, so one function call can supply several related results. Alternatively, VBA procedures can pass values back through arguments or write results directly to cells. These approaches work differently, so the right choice depends on whether the function will be entered in a worksheet cell or called by another macro.

For example, a calculation might need to return a subtotal, tax amount, and total. A regular worksheet function cannot put values into three unrelated cells just because it was entered in one cell. It can return an array that occupies adjacent cells where array results are supported, or a macro can deliberately write each result to a chosen cell.

It is useful to distinguish “one return value” from “one result.” An array is still one VBA return value, but it contains multiple elements that Excel can display as a group. This distinction explains why a function can return several values without having three separate return statements.

Method 1: Return a two-dimensional array

For a worksheet user-defined function (UDF), return a Variant containing a two-dimensional array. The following function returns the input amount, an illustrative 10% tax amount, and the total. The input is assumed to be a numeric price; the tax rate is only an example, not a statement about any real tax obligation.

Public Function PriceParts(ByVal price As Double) As Variant
    Dim result(1 To 1, 1 To 3) As Variant

    ' Return one row with three related values.
    result(1, 1) = price
    result(1, 2) = price * 0.1
    result(1, 3) = price + result(1, 2)

    PriceParts = result
End Function

With the price in cell A2, enter =PriceParts(A2) in the worksheet. If A2 contains 100, the array contains 100, 10, and 110, in that order. The function returns one row and three columns, so the results are intended to appear horizontally. To return a vertical list, define the array with three rows and one column instead, then assign values at indexes such as result(1, 1), result(2, 1), and result(3, 1).

In Microsoft 365 for Windows, Excel supports spilling many array results into neighboring cells, but behavior can depend on how the UDF result is recognized by the installed Excel build and how the formula is entered. Try the function in an empty area with enough clear cells to the right. If it returns only one value or shows an array-entry issue, use the legacy array-entry method: select the intended three horizontal cells, type the formula, then press Ctrl+Shift+Enter. On Mac, the equivalent array-entry shortcut is Command+Shift+Enter. A legacy array formula must be edited as a selected group rather than as an ordinary single cell.

Spill space must be unobstructed. Values, formulas, merged cells, or other obstacles in the destination cells can prevent a dynamic array from expanding. If you need a predictable result across older Excel versions, selecting the output range before entering the formula is often the clearest approach.

Choosing the array shape and data type

Excel expects a two-dimensional array for a rectangular worksheet result: rows by columns. Even when the intended output is one row, the example uses bounds of one row by three columns. A one-dimensional VBA array can be harder to use consistently as a worksheet result, so explicitly building a 2D array avoids ambiguity about orientation.

Variant is commonly used as the function’s return type because it can hold an array. Individual elements can contain numbers, text, dates, or error values, but a result meant for arithmetic should normally use numeric values consistently. If a cell should display a worksheet error, VBA can use an Excel error value, but error handling should be designed intentionally rather than allowing an unexpected VBA error to leak into the worksheet.

Array bounds matter. In the example, the bounds are explicitly 1 to 1 and 1 to 3, which makes the shape easy to read. If an array is built dynamically with ReDim, make sure the final dimensions match the intended rows and columns. Also avoid returning a zero-element array: a worksheet needs a meaningful rectangular result to display.

Method 2: Use ByRef arguments from VBA

VBA procedures can return additional values through arguments declared ByRef. This is useful when another macro calls the function and needs several outputs. One argument can still serve as the function’s main return value, such as a success flag, while the other arguments receive calculated values.

Public Function SplitAmount(ByVal amount As Double, _
                            ByRef half As Double, _
                            ByRef remainder As Double) As Boolean
    ' Return two parts and indicate that the calculation completed.
    half = amount / 2
    remainder = amount - half
    SplitAmount = True
End Function

A calling macro can declare variables, call the function, and then use the values assigned to them. With an input of 100, half becomes 50 and remainder becomes 50; the Boolean return value is True.

Public Sub ShowSplitExample()
    Dim half As Double
    Dim remainder As Double
    Dim completed As Boolean

    completed = SplitAmount(100, half, remainder)
    If completed Then
        Debug.Print half, remainder
    End If
End Sub

ByRef is not a way for an ordinary worksheet formula to fill several output cells. A cell formula passes values into the UDF calculation; it does not provide VBA with normal writable variables corresponding to neighboring cells. Also, a worksheet UDF should calculate and return a result rather than changing other cells as a side effect. For cell-based multiple outputs, return an array or use a separate Sub.

Method 3: Use a Sub to write to cells

A Sub procedure does not return a value through a function name. It can, however, assign results to cells, which is often the most straightforward choice when a user clicks a button or runs a macro. This example reads a price from A2 and writes three results to B2:D2 on a sheet named Sheet1.

Public Sub WritePriceParts()
    Dim ws As Worksheet
    Dim price As Double
    Dim output(1 To 1, 1 To 3) As Variant

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    price = CDbl(ws.Range("A2").Value)

    ' Prepare a one-row, three-column result.
    output(1, 1) = price
    output(1, 2) = price * 0.1
    output(1, 3) = price + output(1, 2)

    ws.Range("B2:D2").Value = output
End Sub

With A2 equal to 100, the macro writes 100 to B2, 10 to C2, and 110 to D2. It requires a worksheet with the exact name Sheet1 and a numeric value in A2; adapt those references to the workbook. In a production macro, consider checking that the input is numeric and deciding what should happen if a destination cell already contains data.

To add VBA in Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, then use Insert > Module and paste the code into the module. On Mac, Option+F11 opens the editor on many keyboards; some require Fn+Option+F11. You may also open the editor from the Developer tab. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm). Save a separate copy before running a macro: changes made by VBA cannot be undone with Ctrl+Z. On Mac, the corresponding undo shortcut is Command+Z, but it does not reverse macro actions either.

How to choose the right method

Choose based on how the output will be consumed, not just on which code looks shortest:

  • Use an array-returning function when the outputs are a calculation that belongs in a worksheet formula and should update when its input changes.
  • Use ByRef arguments when one VBA routine calls another and needs several results in variables.
  • Use a Sub that writes to cells when the user expects a macro to populate a report, table, or fixed output area.
  • Use separate worksheet functions when each result needs its own formula, independent formatting, or separate recalculation behavior.

For a worksheet model, array results keep related outputs together and reduce duplicated calculations. Separate formulas can be easier to inspect or reference individually, but repeating a complex calculation in multiple formulas can create maintenance problems. A macro is appropriate for an action that changes the workbook, but a formula-based UDF is generally easier to recalculate automatically as source values change.

Common problems and limitations

A frequent issue is expecting a worksheet UDF to update neighboring cells. A function entered in one cell does not get permission to place values in adjacent cells; the results must come from its returned array and be displayed by Excel’s array behavior. If the function is returning an array but only one element appears, check the entry method, array dimensions, and Excel version before rewriting the calculation.

Another issue is a blocked spill range. Clear the cells where the results are meant to appear, and unmerge any cells in that area. Check that the returned array dimensions match the available area when using a selected legacy array range. A horizontal 1-by-3 array and a vertical 3-by-1 range are not interchangeable.

UDFs have other practical constraints. They should not be used to change other cells, define names, or perform actions that are expected to behave like a macro. A UDF may also need to be recalculated after changing VBA code; editing the code does not always cause every dependent formula to refresh immediately. Re-entering or recalculating the formula may be necessary. Avoid relying on volatile behavior merely to force constant recalculation, since it can slow a workbook.

Finally, code in a workbook is subject to macro security settings. Only enable macros in files from sources you trust. If a workbook is opened with macros disabled, its VBA UDFs and macros will not run as intended.

Formula output versus macro output

The distinction is about calculation and control. An array-returning UDF behaves like a calculation: Excel supplies inputs, the function computes a result, and the result is shown in the formula’s output area. A Sub behaves like an instruction: it can select a target range, overwrite cells, format content, or carry out other actions. That flexibility is also why a Sub needs more careful safeguards.

For example, a price breakdown that should follow a changing input is a good candidate for a worksheet array formula. A monthly report that copies values into a prepared layout after a user chooses Run is a better fit for a macro. If users need to sort or filter each output as a separate record, consider writing the results to a structured table rather than treating a single spilled result as a permanent data-entry process.

Frequently Asked Questions

Can a VBA function return two values?
Yes. It can return a Variant containing a two-dimensional array, which can represent two or more worksheet values. The function still has one return value in VBA; that value is an array.

Can a worksheet UDF use ByRef to fill two cells?
Not as a normal worksheet formula. ByRef is useful when VBA code calls the function and passes writable variables. To show multiple worksheet results, return an array or run a Sub that writes to cells.

Why does my array-returning function show only one result?
Possible reasons include the Excel version or entry behavior, an array with the wrong dimensions, or a blocked spill area. Try an empty destination range; for legacy array entry, select the full result range and use Ctrl+Shift+Enter on Windows or Command+Shift+Enter on Mac.

Can a VBA UDF write results to other cells?
A worksheet UDF should return its calculation rather than changing other cells. Use a Sub or macro for deliberate cell-writing actions, and remember that those changes cannot be undone with the normal Undo command.

Should I use a Function or a Sub for multiple outputs?
Use a Function when the result belongs in a worksheet calculation or when VBA needs a return value. Use a Sub when the goal is to perform an action, such as placing several results into a specified range.

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.