In VBA, the Array function creates an array of values and returns it inside a Variant. Assign the result to a Variant variable, then use LBound and UBound to find its first and last indexes before looping through the elements.
What the VBA Array Function Does
Array is a VBA function for building an array from a list of values in code. For example, it can make a short list of labels, choices, or test values without requiring you to declare an array’s size and fill each position individually. The returned value is a Variant that contains an array; it is not a special Excel worksheet formula.
That distinction matters if you search for an Excel formula called ARRAY. The VBA function runs in a macro or other VBA procedure, not in a worksheet cell. Excel’s dynamic-array formulas, such as FILTER and UNIQUE, are separate worksheet features and use different syntax.
The function’s basic form is Array(arglist). Each comma-separated argument becomes an element, in the order supplied. The elements can hold different kinds of values because the returned array is contained in a Variant. Use this for compact, fixed lists; it is not a replacement for every kind of array declaration.
Basic Syntax and a Working Example
In the Visual Basic Editor, a small procedure can create an array and print each item to the Immediate window. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the editor; press Ctrl+G there to show the Immediate window if it is hidden. On Mac, the Visual Basic Editor is available from Excel’s Tools > Macro menu; keyboard shortcuts may depend on the keyboard’s function-key settings.
Save a copy of the workbook before running a macro. This example only writes to the Immediate window, but VBA macros can change workbook data, and their changes cannot be reversed with Ctrl+Z.
Option Explicit
Sub ShowArrayItems()
Dim colors As Variant
Dim i As Long
' Build a Variant array with an explicit zero-based lower bound.
colors = VBA.Array(2Red2, 2Green2, 2Blue2)
' Print each index and its value in the Immediate window.
For i = LBound(colors) To UBound(colors)
Debug.Print i, colors(i)
Next i
End Sub
The output contains indexes 0, 1, and 2 paired with Red, Green, and Blue. The example uses VBA.Array deliberately: qualifying the function with the VBA library makes the array zero-based even if the module has an Option Base 1 statement. If you use the unqualified Array name, the module’s Option Base setting can affect the lower bound.
Indexes, Bounds, and Option Base
An array’s lower bound is its first valid index, and its upper bound is its last valid index. In the usual case, an array made with unqualified Array starts at zero. Consequently, the first item is colors(0), not colors(1). This catches people who assume every VBA array starts at 1.
A module can include Option Base 1 in its declarations section to set the default lower bound for arrays in that module. This affects the unqualified Array function as well as certain array declarations. It does not change the meaning of LBound or UBound; those functions report the actual bounds. An explicitly qualified call such as VBA.Array is not changed by Option Base.
For reusable code, avoid assuming a lower bound. Loop from the value returned by LBound through the value returned by UBound. That pattern also makes the intent clear when another procedure supplies an array with different bounds. If an array could be empty or uninitialized, account for that possibility before calling bounds functions; an invalid or uninitialized array can raise a run-time error.
- Use
LBound(values)to get the first valid index. - Use
UBound(values)to get the last valid index. - Use
UBound(values) - LBound(values) + 1to calculate the element count when the array has valid bounds.
Why the Variable Should Be a Variant
A common declaration for an array made with Array is Dim values As Variant. The variable is a single Variant that can hold the array returned by the function, and you can access its elements with parentheses, such as values(1). The values may have different subtypes: for instance, a list could contain text, a number, and a Boolean.
Do not confuse that declaration with Dim values() As Variant. The latter declares a dynamic array variable, which is an array of Variant elements, rather than one Variant variable holding the return value of Array. Directly assigning the result of Array to a Variant is the straightforward approach. If you need a separately declared dynamic array, use the appropriate array operations, such as ReDim, and populate its elements explicitly.
Each element remains accessible by index, so you can read or change an element after assignment. For example, assigning a new string to values(1) changes that element in the array. The array’s size is not automatically expanded by assigning to an index beyond its current bounds; that attempt produces a subscript error. For a resizable array, declare a dynamic array and use ReDim (or ReDim Preserve when retaining existing values is required).
When Array Is Useful—and When It Is Not
The function is particularly handy for short, fixed lists inside a procedure. It can make a set of permitted labels, a few test inputs, or a list of small options easier to see than a sequence of separate assignments. It can also be useful when a procedure needs a quick array value and the values are known when the code is written.
It is less suitable when the number of items changes at run time, when the code needs a specific element type, or when the data is large and comes from a worksheet range. In those cases, a dynamic array, a range read into a Variant, or a collection may be a better fit. Keep in mind that a one-dimensional array created by Array is not automatically a two-dimensional table. Writing values to a rectangular worksheet range generally requires a two-dimensional array shaped to match the range.
Choose based on how the data will be used:
- For a short, fixed list in code,
Arrayis concise. - For a list whose size changes, use a dynamic array and resize it as needed.
- For a list of text pieces separated by a delimiter, consider
Split. - For objects or a collection that grows and shrinks, consider a
Collectionor another suitable data structure. - For worksheet data, consider reading a range into a Variant array and handling its row and column bounds.
Array Compared with Split and Worksheet Arrays
Array and Split both produce arrays, but they address different inputs. Array takes separate arguments written in the code. Split takes a string and divides it at a delimiter. For example, a comma-separated string is a natural input to Split; a short list of individual values known in advance is a natural input to Array. Do not use Array as though it parses a delimited string: it treats the whole string argument as one element.
Arrays used with worksheet ranges have another practical difference. When Excel returns a multi-cell range’s values to a Variant, the result is generally a two-dimensional array indexed by row and column, even if the selected range is a single column. A one-dimensional result from Array does not have that same shape. Code that transfers data between an array and a range must match the dimensions and bounds expected by the destination.
Also distinguish VBA’s Array from worksheet functions that spill results into neighboring cells. A worksheet formula is calculated by Excel’s formula engine; a VBA array is created and manipulated while code runs. If the goal is to calculate a list directly on a sheet without a macro, use an appropriate worksheet formula instead.
Common Errors and How to Avoid Them
The most frequent problem is an incorrect index. If code assumes a zero lower bound when the array begins at 1, or assumes an upper bound that is not present, it can raise “Subscript out of range.” Use LBound and UBound rather than hard-coding the first or last index unless the code itself explicitly controls those bounds.
Another error is using the wrong variable declaration. A Variant holding an array returned by Array is not the same declaration as a dynamic array variable. When in doubt, start with Dim values As Variant for the direct return value, then test the code with Option Explicit enabled so misspelled variable names are caught during compilation.
Watch for assumptions about data type, too. Because the returned array can contain values of different types, an element that looks numeric might be text, and a conversion or comparison may behave differently than expected. Check or convert values deliberately when the code depends on a particular type. Finally, remember that changing an element does not grow the array, and that an array built with Array is not automatically suitable for a multi-cell range assignment.
Frequently Asked Questions
Is Array a built-in Excel worksheet function?
No. Array is a VBA function used in code. It is not entered into a worksheet cell as an Excel formula. Excel worksheet functions that return arrays are a separate feature.
Does a VBA Array start at 0 or 1?
Usually it starts at 0, but the unqualified function can follow the module’s Option Base setting. Use VBA.Array when you specifically want a zero-based array regardless of that setting, and use LBound to confirm the actual first index.
How do I loop through every value in an Array?
Use a loop whose start and end are LBound(values) and UBound(values). This is safer than assuming the array starts at zero or that it always contains a particular number of items.
Can an Array contain different data types?
Yes. The function returns a Variant containing an array, so its elements can hold values of different subtypes, such as text, numbers, and Booleans. Check or convert an element if later code requires a particular type.
Can I use Array to make a two-dimensional array?
Not directly as a two-dimensional rectangular array: a normal call creates a one-dimensional list. For row-and-column data, declare and populate a two-dimensional array or use values read from a worksheet range.
Related
- Markdown Table Merge Cells? What You Can Do
- PHP Backend Data Validation: A Practical Guide
- Protect Sheet in Excel: Allow Editing in Certain Cells
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Left Function: How to Return Text from the StartIn VBA, use Left(string, length) to return a specified number of characters from the beginning of a string.…
- Excel VBA: How to Add a Threaded Comment to a CellIn Excel for Microsoft 365 on Windows, add a threaded comment to a cell with the VBA method…
- Turn On AutoFilter in VBA: Code and ExamplesTo turn on AutoFilter with VBA, select the data range, including its header row, and call its AutoFilter…
- VBA Range Set: How to Set and Use Excel Cell RangesIn Excel VBA, use the Set keyword to assign a cell or range reference to an object variable:…
- VBA Access Find Record: FindFirst, FindRecord, and MoreTo find and move to a matching record in an Access form, use its RecordsetClone with FindFirst, check…
- Excel Macro Range: How to Reference and Use RangesAn Excel macro range is one cell or a group of cells that VBA can read, change, format,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.