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
- Excel Text to Columns: Split Alt+Enter Line Breaks
- Excel Text to Columns and Rows: How to Split Data
- Excel Text to Columns: How to Make Results Vertical
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Function Output: Return Values and DisplayAn Excel VBA function outputs a value by assigning that value to the function’s name. That value appears…
- VBA Find After: How Excel Chooses the Search Start CellIn Excel VBA, the After argument in Range.Find tells Excel which single cell to search after when beginning…
- Excel VBA Send Email in New Outlook: What Actually WorksExcel VBA cannot directly automate the new Outlook for Windows: the new app does not provide the classic…
- Create a Toggle Button in Excel VBA: Setup and CodeTo create a toggle button in Excel VBA on Windows, choose Developer > Insert > Toggle Button under…
- Mouse Macro to Toggle a Laser While Aiming in GamesYes—a mouse macro can usually send the keyboard or mouse input that toggles an in-game weapon laser, if…
- How to Activate a SteelSeries MacroTo activate a SteelSeries macro, open SteelSeries GG, select your supported device under Engine, assign a saved macro…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.