In VBA, declare a function that returns an array as As Variant, then assign an array to the function’s name before it exits. For example, Function GetColors() As Variant can return VBA.Array("Red", "Green", "Blue"); you can then use the result in another procedure or, in supported Excel versions, return it to worksheet cells.
The simplest VBA array-returning function
VBA does not let you declare a function’s return type as a conventional typed array such as As Long(). The usual approach is to declare the function As Variant, because a Variant can hold an array. This small function returns three strings:
Public Function GetColors() As Variant
' Return a Variant containing an array of strings.
GetColors = VBA.Array("Red", "Green", "Blue")
End Function
Put the function in a standard module in the Visual Basic Editor. In the function, the name GetColors acts as the return variable: assigning the array to it sets the function’s result. The function then ends normally at End Function. You do not use Set, because the result is an array of values, not an object reference.
The explicit VBA.Array qualification makes this array zero-based regardless of an Option Base statement in the module. Its elements are indexed from 0 through 2. When you receive an array from a function, check its bounds rather than assuming where indexing begins.
How returning an array works
A function’s declared return type describes the value it hands back to its caller. Since a Variant can contain an array, a Variant-returning function can pass back an array even though its declaration does not name the element type. The returned array can hold values such as numbers, text, dates, or other Variants, subject to VBA’s normal type rules.
There are two common ways to create the returned array. The built-in Array function is concise for a short list of values. Alternatively, declare and fill an array inside the function, then assign that array to the function name. The second pattern is more convenient when the number of elements or their values depend on calculations.
For a one-dimensional result, use LBound and UBound to find the first and last valid indexes. For a multidimensional result, supply a dimension number, such as UBound(data, 2) for the upper bound of the second dimension. This makes calling code safer when the array’s size or lower bound might change.
Return a two-dimensional array
A two-dimensional array is often useful when the result represents rows and columns, especially for a worksheet function. Declare the function as Variant, fill an array, and return it. This example produces a two-row-by-two-column result:
Public Function MakeGrid() As Variant
' Fill a two-dimensional array of worksheet values.
Dim result(1 To 2, 1 To 2) As Variant
result(1, 1) = "Item"
result(1, 2) = "Count"
result(2, 1) = "Pens"
result(2, 2) = 4
MakeGrid = result
End Function
The array’s first dimension represents rows and its second dimension represents columns. The explicit bounds make the intended shape clear: indexes 1 to 2 in each dimension. You could instead use dynamic bounds if the output size is not known until the function runs, but you must allocate that array with ReDim before assigning elements.
For example, an array read from a multi-cell range with Range("A1:B3").Value2 is a two-dimensional Variant array. You can process or reshape its values and return a two-dimensional result in the same way. A range containing only one cell is a special case: its Value2 result is a single value rather than a two-dimensional array, so code that accepts either size should account for that difference.
Use the function in an Excel worksheet
A public function in a standard module can be called from a worksheet like other user-defined functions. In Microsoft 365 for Windows, enter =MakeGrid() in one cell. When the result is a supported array result and the neighboring cells are available, Excel can display it across the corresponding rows and columns as a spilled result. If cells in that output area are occupied, Excel may show #SPILL!; clear the obstructing cells or move the formula.
In older Excel versions without dynamic arrays, select the intended output range first, enter the formula, and confirm it as a legacy array formula with Ctrl+Shift+Enter. On Mac, the equivalent array-formula shortcut is typically Command+Shift+Enter; keyboard layouts and function-key settings can affect shortcuts. Excel for the web can calculate many workbooks containing VBA, but it cannot create, edit, or run VBA macros. Use desktop Excel to work with the code.
Worksheet UDFs have important limits. They return values to the cell or cells containing the formula; they are not a general way to change other worksheet cells, format them, or run actions such as saving a file. A function that tries to modify Excel’s environment or other cells is not an appropriate worksheet UDF. Use a Sub procedure for actions and a Function for a value that a caller needs.
Choose the right array shape and return type
Use a Variant return when you need to hand back an array from a VBA function. The choice of array shape depends on how the caller will consume the result:
- One-dimensional array: suitable for a list used by another VBA procedure. Use
LBoundandUBoundwhen looping through it. - Two-dimensional array: usually the clearest choice for a rectangular block of worksheet output, with one dimension for rows and one for columns.
- Scalar Variant: return a single value instead of an array when the caller expects just one result. A function should not return an array merely because it can.
Be deliberate about bounds. An array made with the qualified VBA.Array function starts at zero, while the explicitly dimensioned array in MakeGrid starts at one. A module can also use Option Base 1 for arrays whose bounds are omitted, but that setting does not change explicitly written bounds. Writing the bounds in code or checking them with LBound avoids hidden assumptions.
Common errors and how to fix them
One frequent mistake is trying to write a function declaration such as Function GetNumbers() As Long(). VBA does not support that array return declaration in the ordinary function syntax; declare the function As Variant instead. Another common mistake is assigning an array to the function name before the array has been initialized or filled. Check that you allocated a dynamic array with ReDim and populated the intended elements.
Index errors often come from assuming that every array begins at 1. For example, reading items(1) from the zero-based result of VBA.Array skips its first item, and reading beyond its upper bound causes a subscript error. Loop with the actual bounds:
Public Sub PrintColors()
' Read the array using its actual bounds.
Dim items As Variant
Dim i As Long
items = GetColors()
For i = LBound(items) To UBound(items)
Debug.Print items(i)
Next i
End Sub
Another worksheet issue is confusing an array’s shape with the cells available to display it. A two-by-two result needs a clear two-by-two output area in versions that spill arrays. In a legacy version, the selected array-formula range must have the intended dimensions. If a function works in VBA but displays an error in the sheet, check the output range, the return value’s dimensions, and whether the function is in a standard module and declared Public.
For any code that you run, save a copy of the workbook first. VBA macros cannot be undone with Ctrl+Z. If the workbook contains VBA, save it in a macro-enabled format such as .xlsm; saving it as a macro-free .xlsx removes its VBA project.
Call the array function from other VBA code
In a procedure, assign the function result to a Variant and then access its elements. The PrintColors example above uses that pattern: items = GetColors() captures the returned array, and the loop prints each value to the Immediate window. This separates the function that creates the result from the procedure that decides what to do with it.
A caller should know whether it expects an array or a single value. If a function can return different kinds of results depending on its inputs, document that behavior and make the caller check the result carefully. For most straightforward code, returning a consistent array shape is easier to maintain than changing between a scalar and an array. When a two-dimensional array is involved, use two loop variables and call LBound and UBound for each dimension.
When to use an alternative
If the goal is only to put a list of fixed values in VBA, a module-level constant or a collection may better match the task, though neither is a direct substitute for returning a worksheet array. If the source data is already in worksheet cells, a formula or a direct range operation may be simpler than a VBA UDF. For users who need a reusable calculation that returns a value to a cell, a Variant-returning function is a practical fit.
Use a Sub when the purpose is to perform an action, such as writing results into a range or changing formatting. Use a Function when the purpose is to calculate and return a result. Keeping that distinction helps avoid trying to make a worksheet function behave like a macro.
Frequently Asked Questions
Can a VBA function return an array?
Yes. Declare the function As Variant and assign an array to the function’s name. The Variant holds the array that is returned to the caller.
Can I declare a VBA function as returning Long()?
Not with the usual VBA function declaration syntax. Return a Variant containing the array instead, and use typed arrays locally inside the function if they suit the calculation.
How do I get the first and last array indexes?
Use LBound(arrayVariable) and UBound(arrayVariable) for a one-dimensional array. For a multidimensional array, pass the dimension number, such as LBound(arrayVariable, 2).
Can an array-returning VBA function spill into worksheet cells?
In Microsoft 365 desktop Excel, a suitable array result can spill from the formula cell when the output area is clear. Older Excel versions generally require selecting the output range and confirming the formula with Ctrl+Shift+Enter.
Why does my returned array show a subscript error?
The code may be using an index outside the array’s bounds, or it may assume a one-based array when the result starts at zero. Check the bounds with LBound and UBound before indexing or looping.
Related
- Conditional Formatting Based on Text in Excel: Rules
- How to Merge Cells in Jira: Options and Workarounds
- Splinter Cell Split Jump: How the Wall Move Works in the Games
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Google Sheets Macro Recorder: How to Record and RunYes. Google Sheets has a built-in macro recorder: in a spreadsheet open in a desktop browser, choose Extensions…
- Excel VBA Range Hyperlinks: Insert, Edit, and RemoveTo insert a hyperlink into an Excel range with VBA, use the worksheet’s Hyperlinks.Add method and set its…
- Outlook VBA: Select an Excel Worksheet by PathIn Outlook VBA on Windows, create an Excel application object, open the workbook by its full file path,…
- Excel VBA: Find the Last Row in a Column with DataIn Excel VBA, the usual way to find the last row with data in column A is ws.Cells(ws.Rows.Count,…
- Excel VBA: Delete a Worksheet Without a Prompt SafelyTo delete an Excel worksheet without the confirmation prompt, set Application.DisplayAlerts to False immediately before calling the worksheet’s…
- Excel VBA: Find the Minimum Value in a Range or ArrayTo find a range’s minimum in Excel VBA, use Application.WorksheetFunction.Min, for example, Application.WorksheetFunction.Min(Range("B2:B20")). For a macro you can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.