Yes. In Excel VBA, declare the function as Variant, place an array in that Variant, and assign it to the function name; the caller then receives the array. For a worksheet function, return a two-dimensional array for a rectangular result, then enter the function in a cell to spill the values in current Microsoft 365 versions or use a legacy array formula where required.
What It Means for a Function to Return an Array
A VBA function normally returns one value through its function name. For example, a function declared As Long can return a whole number. VBA does not let you declare a function’s return type using an array type such as As Long(). The usual solution is to declare the function As Variant, because a Variant can hold an array as well as a single value.
Inside the function, build an array and assign it to the function name. When the function finishes, the Variant contains the array. A procedure calling the function can store that result in another Variant and inspect its elements. If the function is called from a worksheet, Excel can use the returned values as a multi-cell result, subject to the array-entry behavior of the Excel version in use.
This is different from writing values directly into cells. Returning an array supplies a result to the caller; it does not, by itself, put values in arbitrary worksheet locations. That distinction matters when deciding whether to write a function (which calculates and returns a result) or a Sub procedure (which can perform actions such as changing cells).
A Small VBA Function That Returns an Array
Put this example in a standard module. In the Visual Basic Editor, choose Insert > Module, then paste the code into the new module. It builds a three-row, two-column array and returns it through the function name.
Public Function MakeGrid() As Variant
' Return a two-column array with three rows.
Dim result(1 To 3, 1 To 2) As Variant
result(1, 1) = "Product"
result(1, 2) = "Quantity"
result(2, 1) = "Notebook"
result(2, 2) = 12
result(3, 1) = "Pen"
result(3, 2) = 24
MakeGrid = result
End Function
The important line is MakeGrid = result. Do not use Set: arrays are not objects that are assigned with the object-reference keyword. The function is declared As Variant, while the local array is a two-dimensional array of Variants. This makes the result suitable for text and numbers together, as well as for a rectangular worksheet result.
For a procedure that calls this function, declare the receiving variable as a Variant too. The returned array is held inside that Variant, and its dimensions and elements can then be examined. Returning an array does not make it a special Excel object; it is still an array value that the caller must handle with compatible indexing and bounds.
Using the Function in a Worksheet
With no input cells required and the code stored in a standard module, enter this formula in an empty worksheet cell:
=MakeGrid()
In current Microsoft 365 for Windows, a two-dimensional array returned by a VBA user-defined function can appear as a spilled result, filling the three rows and two columns starting at the formula cell. The example returns Product and Quantity in the first row, Notebook and 12 in the second, and Pen and 24 in the third. The cells the result needs to occupy must be clear; otherwise Excel displays a spill error rather than overwriting existing content.
In Excel versions that use legacy array formulas, select the full output range first, type the function formula, and confirm it with Ctrl+Shift+Enter. On Mac, the equivalent legacy array-formula shortcut is Control+Shift+Return. Follow the behavior of the Excel version you use: in a dynamic-array version, enter the formula in the top-left cell and let the result spill rather than selecting a destination range in advance.
A worksheet function must be saved in a macro-enabled workbook, such as .xlsm, if you need to retain its VBA code. Users may also need to enable macros for the function to run. Excel for the web can open a workbook containing VBA, but it cannot run or edit VBA macros; use Excel desktop for this function. The Mac desktop version supports VBA, though ribbon and editor details may differ from Windows.
Returning Values Read from a Range
A common use is to read a range, calculate a result in memory, and return a new array. The next example accepts a multi-cell, single-column range of numbers and returns each value doubled. It uses Value2 to read the cells into an array and sizes the output to match the input’s row count.
Public Function DoubleColumn(ByVal source As Range) As Variant
' Return a two-dimensional, single-column result.
Dim inputValues As Variant
Dim outputValues() As Variant
Dim i As Long
inputValues = source.Value2
ReDim outputValues(1 To UBound(inputValues, 1), 1 To 1)
For i = 1 To UBound(inputValues, 1)
outputValues(i, 1) = inputValues(i, 1) * 2
Next i
DoubleColumn = outputValues
End Function
With numbers in A2:A4, such as 3, 5, and 8, enter this formula in an empty cell:
=DoubleColumn(A2:A4)
The result is 6, 10, and 16 in three rows. The example assumes the argument is a multi-cell, single-column range containing numeric values. A one-cell range behaves differently because Range.Value2 returns a scalar for a single cell, not a two-dimensional array; this code would need a separate single-cell case before being used that way. It also does not define special handling for blank cells or error values, so adapt the calculation if those can occur in the input.
Choosing Array Shape, Bounds, and Data Type
For worksheet output, a two-dimensional array is generally the clearest choice: its first dimension represents rows and its second represents columns. A result such as (1 To 3, 1 To 2) has three rows and two columns. A one-dimensional array can be less predictable to work with because the caller or worksheet needs to interpret its orientation. Explicitly creating a two-dimensional array avoids ambiguity when the intended output is a table or column.
- Use Variant elements when the result may mix text, numbers, dates, or other value types.
- Use a typed array internally when the values are all one known type, but keep the function return type as Variant so it can carry the array.
- Set bounds deliberately. Use
ReDimwhen the result size depends on input, and make the row and column bounds match the intended output. - Return a rectangular array for a worksheet range. Every row must have the same number of columns; a jagged array is not a normal rectangular range result.
VBA arrays can have lower bounds other than 1, and the Option Base setting can affect the default lower bound of some arrays. That is a reason to use explicit bounds such as 1 To rowCount when the code depends on exact indexing. The two examples above use explicit bounds, so their loops and output positions are clear regardless of a module’s default base.
Worksheet Function or Macro?
Use a worksheet UDF when a calculation should return a value or array from its arguments and recalculate as worksheet inputs change. A UDF is not a replacement for a macro that needs to edit other cells, apply formatting, add sheets, or perform a sequence of user actions. Worksheet functions are intended to calculate results, not to change unrelated workbook state as a side effect.
Use a Sub procedure when the goal is to place values into a chosen range or otherwise change the workbook. A macro can call a function that produces an array and then write that array to a range, but it is the Sub that performs the worksheet change. Save a copy of the workbook before running a macro that changes data: macro actions cannot be undone with Ctrl+Z (or Command+Z on Mac). The two functions shown here only return results; they do not edit cells on their own.
Common Problems and How to Fix Them
- Declaring the function with an array return type: VBA does not use a declaration such as
As Double()for a function result. Declare itAs Variantand return an array held in a Variant. - Returning a scalar by mistake: If a calculation assigns one number or string to the function name, it returns one value, not an array. Check that the assigned variable is actually an array.
- Wrong number of output cells: A legacy array formula needs a selected range sized to the result. A dynamic-array formula needs enough unobstructed cells to spill. Match the destination to the array’s row and column dimensions.
- Single-cell range handling: As shown above, reading one cell with
Value2gives a scalar. Add a branch for that case or require a multi-cell input and document the requirement. - Function not recognized in the sheet: Confirm the code is in a standard module, the workbook retains its VBA project, macros are permitted, and the formula uses the function’s correct name.
- Unexpected recalculation: A UDF recalculates when Excel determines that its precedent cells have changed. If code reads cells not passed as arguments, Excel may not track those dependencies reliably. Pass the relevant ranges as arguments rather than hiding dependencies inside the function.
Frequently Asked Questions
Can a VBA function return an array directly?
It can return an array value, but the function’s declared return type is normally Variant, not an array type. Assign the array to the function name, then receive the result in a Variant.
Can I use a VBA array-returning function as a worksheet formula?
Yes, if the function is available in the workbook’s VBA project and macros are enabled. In current Microsoft 365 desktop Excel, a two-dimensional result can spill from the formula cell; older Excel versions may require a legacy Ctrl+Shift+Enter array formula.
Why does my function return only one value?
Check whether the function name is being assigned a scalar instead of the array, and confirm the function is declared As Variant. In a legacy Excel version, also check that you entered the formula over the full output range as an array formula.
Can a function return a one-dimensional array?
VBA can return a one-dimensional array inside a Variant. For worksheet results, a two-dimensional array is usually easier to size and interpret, especially when the result needs a particular row-and-column layout.
Can Excel for the web run a VBA function?
No. Excel for the web can preserve a workbook that contains VBA, but VBA code must be run in the Excel desktop application. The desktop version for Windows or Mac is needed to use and edit the function.
Related
- Excel Text to Columns: Split at the First Delimiter
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Scroll: Limits and Scrollable AlternativesNo: VBA’s built-in MsgBox has no setting or argument that adds a scrollbar. For long text, use a…
- Excel VBA Database Connections: Setup, Code, and SafetyExcel VBA can connect to a database on Windows by using a data provider, opening a connection, and…
- Excel VBA Date and Time Stamp: Add Fixed TimestampsTo add a fixed date-and-time stamp with Excel VBA, assign Now to a cell and apply a date-time…
- Excel VBA Function Not Defined? Causes and Fixes ExplainedIf Excel VBA reports Sub or Function not defined, it cannot find the procedure named in the highlighted…
- Macro Recorder for Automation: What It Can and Can’t DoYes. Excel for Microsoft 365 on Windows includes a macro recorder that turns many repeated actions into VBA…
- Excel VBA: Find All Cells With a ValueTo find every cell that matches a particular value in Excel VBA, use the Range.Find method and repeat…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.