An array in Excel VBA is a variable that holds a group of related values under one name, with each value reached by an index. Instead of creating a separate variable for every item, you can store items such as names, numbers, or worksheet data in an array and process them with VBA code.
What an Array Means in VBA
A regular variable usually represents one value: for example, a Long variable might hold one row number, and a String variable might hold one name. An array is a structured set of values. Each item is called an element, and its position is identified by one or more numeric indexes, also called subscripts.
For example, an array named scores could hold five test results. You can refer to the first result as scores(1) and the fifth as scores(5), if those are the bounds you declared. The array itself is still one variable, but its indexed elements can hold separate values.
Arrays are useful when a macro needs to collect, calculate, sort, or examine many values. They are particularly helpful when looping through a set of data or moving a block of worksheet values into memory, working on it, and writing the results back.
How Arrays Work
An array has a name, a data type, a number of dimensions, and bounds that define the valid indexes. A one-dimensional array is like a list. A two-dimensional array is like a grid with rows and columns. VBA can also create arrays with more dimensions, although most Excel automation uses one- or two-dimensional arrays.
Each element in a typed array has the array’s declared data type. For instance, a Long array stores whole numbers, while a String array stores text. A Variant can hold different kinds of values, and a Variant variable can also contain an array. Choosing a specific type where practical makes the intended data clearer; Variant arrays are often convenient when receiving values from a worksheet range.
Array indexes are not necessarily numbered from zero. Their lower and upper bounds depend on the declaration and, in some cases, the module’s Option Base setting. Use LBound and UBound when code should work with whatever bounds an array actually has, rather than assuming that its first index is zero or one.
Declaring and Filling an Array
A fixed-size array has bounds declared in its definition. The following example creates five text elements, explicitly numbered from 1 through 5:
Sub FixedArrayExample()
Dim weekdays(1 To 5) As String
Dim i As Long
' Store a value in each element.
weekdays(1) = "Monday"
weekdays(2) = "Tuesday"
weekdays(3) = "Wednesday"
weekdays(4) = "Thursday"
weekdays(5) = "Friday"
' Display the elements in the Immediate window.
For i = LBound(weekdays) To UBound(weekdays)
Debug.Print weekdays(i)
Next i
End Sub
The explicit bounds make the intended numbering easy to see. If you declare an array without specifying its lower bound, VBA’s default is generally zero unless the module uses Option Base 1. Explicit bounds are often clearer, especially when array indexes need to correspond to worksheet row or column positions.
VBA’s Array function is a concise way to create a Variant array with initial values. For example, items = Array("Pen", "Pencil", "Ruler") assigns an array to a Variant variable named items. Check its actual bounds with LBound(items) and UBound(items); do not build code around an assumed first index when a module’s base setting might differ.
Indexes and Multidimensional Arrays
In a one-dimensional array, one index selects an element. In a two-dimensional array, two indexes identify an element, commonly representing a row and a column. For example, tableData(2, 3) means the element at the second index in the first dimension and the third index in the second dimension, subject to the array’s declared bounds.
A declaration such as Dim grid(1 To 4, 1 To 3) As Double creates a two-dimensional array with four positions in its first dimension and three in its second. A nested loop can visit every element. Using LBound(grid, 1) and UBound(grid, 1) gets the first dimension’s bounds; using 2 as the second argument gets the second dimension’s bounds. This is safer than hard-coding loop limits if dimensions may change.
For data taken from a normal rectangular Excel range, VBA commonly represents the values as a two-dimensional array: the first index corresponds to the row and the second to the column. That row-column arrangement is useful to remember when editing values in memory. A single-cell range is a special case: reading its Value can return a scalar rather than a two-dimensional array.
Using Arrays with Worksheet Ranges
One practical use is to read a block of cells into a Variant, process the values in VBA, and assign the resulting array to a same-sized output range. This reduces repeated interaction with worksheet cells, which can be helpful for larger data sets. The example below assumes that cells A1:A3 on a worksheet named Sheet1 contain numbers and that B1:B3 is available for the results.
Sub DoubleRangeValues()
Dim ws As Worksheet
Dim data As Variant
Dim rowIndex As Long
' Read the three numeric cells into a two-dimensional Variant array.
Set ws = ThisWorkbook.Worksheets("Sheet1")
data = ws.Range("A1:A3").Value
' Double each value in memory.
For rowIndex = LBound(data, 1) To UBound(data, 1)
data(rowIndex, 1) = data(rowIndex, 1) * 2
Next rowIndex
' Write the results to the matching-size destination range.
ws.Range("B1:B3").Value = data
End Sub
If A1:A3 contains 4, 7, and 10, the macro places 8, 14, and 20 in B1:B3. The example expects numeric values; text, blanks, or Excel error values may require validation or different handling. Also ensure the destination range has the same row and column dimensions as the array you assign.
Before running a macro that writes to a workbook, save a copy of the file. VBA changes generally cannot be reversed with Ctrl+Z, so a backup is a straightforward way to protect the original data. Excel for the web can open workbooks containing VBA but cannot create, run, or edit VBA macros; use desktop Excel for that work. Excel for Mac supports VBA, though some Windows-specific features and external integrations may not be available in the same way.
Fixed and Dynamic Arrays
A fixed-size array’s bounds are set when it is declared. Use one when the number of elements is known and stable, such as the five weekdays in the earlier example. A dynamic array is declared without bounds and allocated later with ReDim. This is useful when the required size depends on data discovered while a macro runs.
Sub DynamicArrayExample()
Dim productNames() As String
Dim itemCount As Long
' Set the number of elements after determining the required size.
itemCount = 3
ReDim productNames(1 To itemCount)
productNames(1) = "Notebook"
productNames(2) = "Folder"
productNames(3) = "Marker"
End Sub
Running ReDim on an existing dynamic array normally clears its contents. Add Preserve when resizing and retaining existing values is necessary. With ReDim Preserve, VBA limits which bounds can change: for a one-dimensional array, you can change its upper bound, but not its lower bound. For a multidimensional array, only the last dimension can be resized while preserving the data. These restrictions matter when choosing an array layout.
When to Choose an Array
Arrays suit work that involves a known or manageable group of values, especially when code needs to loop over those values or perform calculations before updating the sheet. They can make a macro easier to organize than a long series of individual variables, and they can reduce the number of calls between VBA and the worksheet when a whole range is transferred at once.
An array is not automatically the right choice for every task. If the data should remain visible and editable on the worksheet, a range or Excel Table may be more useful. If a collection must grow and shrink with frequent additions or removals, a VBA Collection may be a better fit. If the data is naturally stored as key-value pairs, a dictionary can be appropriate, but availability and setup can vary across platforms and VBA environments. Consider how the data will be accessed, changed, and returned before choosing.
Common Array Problems
- Subscript out of range: The code requested an index outside the array’s current bounds. Check the declared limits, and use
LBoundandUBoundin loops. - Using the wrong dimension: A range containing multiple rows and columns is commonly a two-dimensional array. Accessing it with only one index can cause an error or incorrect logic.
- Reading bounds before allocation: A dynamic array that has not yet been sized with
ReDimdoes not have usable bounds for ordinary iteration. Allocate it first or keep track of whether it has been initialized. - Losing values during resizing: A plain
ReDimclears the current array contents. UseReDim Preservewhen its restrictions fit the required resizing operation. - Assuming worksheet values are all numbers: A range can contain text, blanks, or errors. Validate values before applying numeric operations, or design the macro to handle those cases.
- Writing mismatched dimensions: When assigning an array to a range, make the destination range match the array’s row and column dimensions. Check both dimensions when working with a two-dimensional array.
A good debugging habit is to inspect the array’s bounds and dimensions before processing it. Explicit lower bounds, descriptive variable names, and small test ranges make it easier to find whether a fault comes from indexing, data type, or an unexpected range shape.
Frequently Asked Questions
Is an array the same as a range in Excel VBA?
No. A range is a set of worksheet cells, while an array is a set of values held in VBA memory. You can read range values into an array, change them there, and write them back, but the array is not itself a worksheet range.
Do VBA arrays start at zero or one?
Either. The bounds depend on how the array is declared and, for declarations that omit a lower bound, can depend on Option Base. Use explicit bounds or check them with LBound and UBound rather than assuming.
What does ReDim do in VBA?
ReDim sets or changes the size of a dynamic array. Without Preserve, resizing an existing array clears its values. ReDim Preserve retains values where allowed, but VBA restricts which bounds can change.
Why does a range become a two-dimensional array?
Excel returns the values from a multi-cell rectangular range in row-and-column form, so VBA code typically accesses them with two indexes. A one-cell range may return a single value instead, so code that handles either case should account for that difference.
Are arrays faster than working with cells one by one?
For many tasks, reading a block into an array, processing it in memory, and writing it back can reduce worksheet interactions and improve performance. The advantage depends on the task and data size; arrays also require careful handling of dimensions, types, and bounds.
Related
- Data Link Excel to Civil 3D: Steps and Limits
- Creating a Four-Quadrant Scatter Plot in Excel: Steps
- CSV Import Wizard in Excel: How to Import Files
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Conditional Formatting VBA: Rules, Examples, and TipsIn Excel for Microsoft 365 on Windows, VBA creates conditional formatting by adding a rule to a range’s…
- Active Range in VBA: What It Means and How to Use ItIn Excel VBA, “active range” usually means the cell or cells currently selected, but ActiveRange is not a…
- Excel VBA: Highlight the Active CellTo highlight the active cell in Excel for Microsoft 365 on Windows, put a Worksheet_SelectionChange event in that…
- VBA Loop Example: For, Do While, and For Each LoopsA VBA loop repeats a block of code, either a set number of times or until a condition…
- VBA Copy and Paste Special: Examples and OptionsIn Excel VBA, copy a range and paste only selected content with Range.Copy followed by Range.PasteSpecial. For example,…
- How to Create a Word Template With MacrosTo create a Word template that contains a macro, start with a blank document or an existing file,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.