VBA Function Return Multiple Values: What Works

Yes. A VBA function can provide multiple results, but it has only one formal return value. To deliver several pieces of data, return one container—such as an array, a user-defined type, or a class—or pass extra results back through arguments declared ByRef.

The best method depends on what the caller needs: an array is quick for a small, ordered set of values; ByRef parameters are useful when the function also needs to report success or failure; and a custom type or class makes related results easier to name and maintain.

What a VBA function can return

In VBA, a function returns its result by assigning a value to the function’s own name. For example, a function declared as Function DoubleIt(ByVal n As Long) As Long returns one Long value when its code assigns to DoubleIt. VBA does not let you declare a function with two independent return types, such as a number and a string side by side.

That limitation does not mean a function can only communicate one useful fact. Its single return value can be a structure that holds several facts, or its arguments can be used as output locations. It is helpful to distinguish the function’s formal return value from the total information the caller receives.

A Sub procedure, by contrast, does not return a value in the function-call sense. A Sub can still change variables passed ByRef, but it cannot be used as an expression that supplies a result. Choose a Function when the caller needs a value to assign or use in an expression; choose a Sub when the main purpose is to perform an action.

How function results work

VBA arguments are ByRef by default, meaning the procedure can work with the original variable supplied by the caller. An argument marked ByVal is passed as a value, so assigning to that parameter does not replace the caller’s variable. Writing ByRef or ByVal explicitly makes the interface clearer and avoids relying on the default.

For the function’s own return value, VBA uses an assignment to the function name. A function can set that result more than once; the value assigned most recently is the one returned when execution reaches the end of the function or a suitable Exit Function statement. This is separate from assigning to one or more ByRef parameters.

For example, a function may return a Boolean that indicates whether an operation succeeded, while also filling two ByRef arguments with calculated outputs. Alternatively, it can return one Variant that contains an array. The caller then decides how to access the data in that container.

Return an array for a small collection

An array is often the simplest choice when results form an ordered group and callers can agree on what each position means. The following standard-module example returns two coordinates in a Variant containing an array. Array creates a zero-based array, so the first item is at index 0 and the second is at index 1.

Save a copy of the workbook before running a macro: VBA macros cannot be undone with Ctrl+Z. Put this code in a standard module, then run ShowCoordinates from the macro dialog or the VBA editor.

Option Explicit

Public Function GetCoordinates() As Variant
    ' Return two related values in one array.
    GetCoordinates = Array(12.5, 8.25)
End Function

Public Sub ShowCoordinates()
    Dim coordinates As Variant

    coordinates = GetCoordinates()
    Debug.Print coordinates(0)
    Debug.Print coordinates(1)
End Sub

In this example, the Immediate window prints 12.5 and 8.25 on separate lines. A Variant is used because the function returns an array created by Array. If a caller needs to accept arrays of different sizes or types, Variant is also convenient, though it provides less compile-time type checking than a more specific design.

The main drawback is that array positions are not self-explanatory. A caller must know that index 0 means latitude, for example, rather than longitude. Document the order, use named constants for indexes when that helps, or choose a named structure if the results will be used widely. Also check the array’s bounds before reading indexes when its size is not guaranteed.

Use ByRef arguments for separate outputs

ByRef outputs are a good fit when the function needs to return a status as well as several results, or when a project already uses a procedure interface with output parameters. Here, the Boolean return says the call completed, while two arguments receive the ordered values. The caller declares variables of the expected types before the call.

Save a copy of the workbook before running the macro, because Ctrl+Z does not reverse VBA changes. This standard-module example is self-contained:

Option Explicit

Public Function GetOrderedPair(ByVal firstValue As Double, _
                               ByVal secondValue As Double, _
                               ByRef smaller As Double, _
                               ByRef larger As Double) As Boolean
    ' Place the inputs in ascending order.
    If firstValue <= secondValue Then
        smaller = firstValue
        larger = secondValue
    Else
        smaller = secondValue
        larger = firstValue
    End If

    GetOrderedPair = True
End Function

Public Sub ShowOrderedPair()
    Dim lowerValue As Double
    Dim upperValue As Double
    Dim completed As Boolean

    completed = GetOrderedPair(9, 4, lowerValue, upperValue)
    Debug.Print completed
    Debug.Print lowerValue
    Debug.Print upperValue
End Sub

The example prints True, 4, and 9. Since this particular operation always succeeds for two Double inputs, the status is mostly illustrative; in a real parser or lookup, the Boolean could indicate whether valid data was found. A useful convention is to set output arguments deliberately on every path, including failure paths, so callers do not accidentally rely on stale values from earlier work.

Make output parameters explicit with ByRef even though it is the default. This helps readers see which arguments may be changed. Do not mark an input ByRef unless the function truly needs to modify the caller’s variable. Passing an expression or literal to an argument that must be changed can also cause problems, so callers should supply a writable variable for each output.

Use a user-defined type for named fields

When the outputs have clear names and belong together, a user-defined type (UDT) can be more readable than an array. Declare the Type in a standard module, outside any procedure, then use it as the function’s return type. This example returns width and height as named fields instead of relying on index numbers.

Save a workbook copy before running VBA code; macro actions cannot be undone with Ctrl+Z. Add the code to a standard module:

Option Explicit

Public Type TRectangle
    Width As Double
    Height As Double
End Type

Public Function GetRectangle() As TRectangle
    Dim result As TRectangle

    ' Fill the named fields, then return the structure.
    result.Width = 12
    result.Height = 5
    GetRectangle = result
End Function

Public Sub ShowRectangle()
    Dim rectangle As TRectangle

    rectangle = GetRectangle()
    Debug.Print rectangle.Width
    Debug.Print rectangle.Height
End Sub

Here the caller can refer to Width and Height directly. A UDT is lightweight and suits a fixed set of related values used within VBA. Its rules are more restrictive than those of a class, especially when exposing data across object or automation boundaries. Keep the type in a standard module and check the requirements of the particular interface if the type must be used by a public class method or external client.

Use a class for richer results

A class is useful when the returned result needs more than a few fields—for example, when it needs validation, calculated properties, or methods. Create a class module named CInterval and put the first block there. Put the function and caller in a standard module. The function returns one object reference, and that object carries both values.

Save a copy of the workbook before running the macro. Changes made by VBA cannot be reversed with Ctrl+Z.

Option Explicit

' In class module CInterval:
Public Minimum As Double
Public Maximum As Double

Option Explicit

Public Function GetInterval(ByVal firstValue As Double, _
                            ByVal secondValue As Double) As CInterval
    Dim result As CInterval

    Set result = New CInterval
    ' Store the values in the returned object.
    If firstValue <= secondValue Then
        result.Minimum = firstValue
        result.Maximum = secondValue
    Else
        result.Minimum = secondValue
        result.Maximum = firstValue
    End If

    Set GetInterval = result
End Function

Public Sub ShowInterval()
    Dim interval As CInterval

    Set interval = GetInterval(9, 4)
    Debug.Print interval.Minimum
    Debug.Print interval.Maximum
End Sub

Unlike the UDT example, a class is an object, so callers use Set when assigning the returned reference. Classes can expose properties and methods, and can enforce rules about how their data is set. That flexibility comes with extra setup: a class module must be created and named correctly, and an object has a lifetime managed through references.

How to choose a return method

Pick the approach that makes the caller’s code easiest to understand, not merely the approach that takes the fewest lines. Consider how many results there are, whether their meaning is fixed, whether failure is possible, and whether other parts of the project need the same result structure.

Choose an array for a short, ordered sequence or a variable-length collection. Document the indexes and bounds.
Choose ByRef outputs when you need distinct output variables plus a status return, or when the calling pattern is already established.
Choose a UDT for a fixed group of named, related values used in VBA code.
Choose a class when results need behavior, validation, or a richer public interface.

For two simple outputs, ByRef may be the clearest. For coordinates, a range, or a record with stable named fields, a UDT may reduce mistakes. If the shape of the result may grow, consider whether a class will make later changes easier. Avoid choosing a class solely to make a tiny function look more object-oriented; it adds a module and object-handling steps that may not be worthwhile.

Common mistakes and practical checks

A common misconception is that adding multiple names after the As clause creates multiple return values. VBA functions have one declared return type. To return several pieces of information, use one container or output arguments. Another frequent error is assigning to a local variable but never assigning the completed result to the function name; in that case, the function may return its default value instead.

Watch for these practical issues:

Array indexes: Array returns a zero-based array, while arrays declared with other bounds may start elsewhere. Use LBound and UBound when bounds are not known in advance.
Unassigned outputs: Set every ByRef output on every relevant path. Initialize values before a risky operation if failure should leave a predictable result.
Object assignment: Use Set when assigning an object result to an object variable. A UDT or ordinary value does not use Set.
Type mismatch: Make sure the caller’s variables are compatible with the output types. Variant can accept many kinds of values, but that flexibility can conceal unexpected data.
Meaning and order: Keep related results clearly named or documented. A pair of Double values is not self-explanatory if their roles are undocumented.

When debugging, inspect the function result and output arguments immediately after the call. Step through the procedure with F8 in the VBA editor on Windows, or use the VBA editor’s equivalent step command on Mac. This can reveal whether a branch skipped an assignment or whether the function returned before all outputs were set.

Returning multiple values to an Excel worksheet

A VBA function called from a worksheet cell has additional constraints: Excel expects a value it can place in the cell or evaluate as a formula result. A function returning a Variant array may be useful in worksheet scenarios, but array-entry and spilling behavior depend on how the function is used and the Excel version. Do not assume that an array result behaves exactly like separate VBA variables or that every worksheet call will display each item in a separate cell automatically.

ByRef outputs are generally most natural when one VBA procedure calls another, because a worksheet formula does not provide a convenient way to supply separate writable variables for those outputs. For worksheet use, decide whether one combined value, a supported array result, or separate functions is the clearest interface. Also avoid changing other cells from a worksheet UDF; worksheet functions are intended to calculate results, not act as general-purpose macros. A regular macro or event procedure is more appropriate for actions that modify workbook contents.

Frequently Asked Questions

Can a VBA function return two values directly?
No. A function has one return value, but that value can be a container such as an array, UDT, or class. You can also use ByRef arguments to send additional outputs to the caller.

Is ByRef the same as returning a value?
No. ByRef lets a procedure change a variable supplied by the caller. The function’s own return is assigned to its function name. A function can use both mechanisms in the same call.

Should I use an array or ByRef arguments?
Use an array when the results form an ordered collection that the caller can index. Use ByRef arguments when individually named output variables, often alongside a success flag, make the calling code clearer.

Can a VBA function return a custom Type?
Yes, a UDT declared in a standard module can be used to group named fields in VBA code. UDTs have limitations at some public object and automation boundaries, so check the needs of code that must expose the type externally.

Can I return multiple values from a worksheet UDF?
A UDF can return an array value in applicable Excel scenarios, but how results appear across cells depends on Excel’s array handling and the way the formula is entered. For broader workbook changes, use a macro rather than a worksheet function.

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.