A VBA array stores several related values under one variable name. For example, declare a three-item array with Dim items(1 To 3) As String, assign values to its numbered positions, and loop from LBound(items) to UBound(items) to read them. The complete Excel macro below demonstrates that pattern.
A working VBA array example
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste in this procedure. On a Mac, open the editor from Developer > Visual Basic; the keyboard shortcut can depend on your keyboard and function-key settings.
Option Explicit
Sub ShowArrayExample()
' Store three item names in a fixed-size array.
Dim items(1 To 3) As String
Dim i As Long
items(1) = "Apples"
items(2) = "Pears"
items(3) = "Oranges"
' Print each position and its value in the Immediate window.
For i = LBound(items) To UBound(items)
Debug.Print i & ": " & items(i)
Next i
End Sub
To run the procedure, click inside it in the Visual Basic Editor and press F5, or return to Excel and use Developer > Macros, select ShowArrayExample, and choose Run. The output appears in the editor’s Immediate window, which you can show with View > Immediate Window. It lists the indexes 1, 2, and 3 alongside the corresponding names. Save a copy of the workbook before running macros: VBA changes cannot be undone with Ctrl+Z. A workbook containing VBA generally needs to be saved as an Excel Macro-Enabled Workbook (.xlsm).
What an array is and how its indexes work
An ordinary variable holds one value at a time; an array holds a sequence of values that share a name and data type. Each value is an element, and its index (also called a subscript) identifies its position. In the example, items(2) means the second position, which contains “Pears.” The index is not a worksheet row number unless you deliberately make it one.
The declaration specifies the array’s bounds and, optionally, its element type. In Dim amounts(1 To 12) As Double, the lower bound is 1, the upper bound is 12, and each element can hold a Double value, which is useful for decimal numbers. A Long array is suitable for whole-number counts; a String array stores text; and a Boolean array stores True or False values. Choosing a specific type makes the intended data clearer and can help avoid unintended type conversions.
In VBA, a declaration such as Dim amounts(12) As Double normally gives the array indexes 0 through 12: the number in parentheses is the upper bound, not the total number of elements. That is 13 positions. By contrast, Dim amounts(1 To 12) As Double has 12 positions. Explicit bounds make the intended count easier to see and reduce off-by-one mistakes.
Fixed-size and dynamic arrays
The example uses a fixed-size array: its bounds are set when the procedure is compiled, and it cannot be resized with ReDim. Fixed-size arrays are convenient when the number of values is known and unlikely to change. For a count that depends on data in a worksheet, use a dynamic array, declared with empty parentheses, then allocate it with ReDim.
Option Explicit
Sub ResizeScores()
' Allocate space after determining the required size.
Dim scores() As Double
Dim i As Long
ReDim scores(1 To 3)
scores(1) = 81.5
scores(2) = 93
scores(3) = 76.25
' Preserve existing values while expanding the final bound.
ReDim Preserve scores(1 To 5)
scores(4) = 88
scores(5) = 90.5
For i = LBound(scores) To UBound(scores)
Debug.Print scores(i)
Next i
End Sub
ReDim Preserve keeps the values already stored as it changes an array’s size. In a one-dimensional array, as shown here, its upper bound can be expanded while preserving the existing elements. Without Preserve, resizing an allocated dynamic array discards its existing contents. VBA also limits which bounds can be changed while preserving a multidimensional array: in practice, only the final dimension can be resized this way. Plan the array’s shape before building a procedure that depends on preserving data across dimensions.
Use LBound, UBound, and loops safely
LBound returns an array’s lowest index, while UBound returns its highest. Using both in a loop is safer than assuming every array begins at zero or one. It also makes a procedure easier to reuse if the array’s bounds change. For a two-dimensional array, supply a dimension number, such as UBound(data, 1) for the first dimension or UBound(data, 2) for the second.
For a one-dimensional array, a typical loop is For i = LBound(values) To UBound(values). The loop variable is declared as Long, which is suitable for array indexes in Excel VBA. Avoid accessing an element before the lower bound or beyond the upper bound. VBA will usually raise “Subscript out of range” when an index does not exist. Also check that a dynamic array has been allocated before asking for its bounds; calling UBound on an unallocated dynamic array can produce an error.
Some VBA projects use Option Base 1 to change the default lower bound for array declarations that do not explicitly state one. It is clearer to write the bounds directly, such as Dim values(1 To 10) As Long, rather than relying on a module-wide default. That way, readers can see the index range in the declaration itself.
Read worksheet cells into an array
Arrays are especially useful when a macro needs to process a block of worksheet data. Assigning a multi-cell range’s Value property to a Variant loads the values into a two-dimensional array. The first dimension corresponds to worksheet rows, and the second corresponds to columns. For example, with the range A2:A5 on a worksheet named Sheet1, the resulting array has four row positions and one column position.
Option Explicit
Sub ReadRangeIntoArray()
' Load four worksheet cells into a two-dimensional Variant array.
Dim values As Variant
Dim rowIndex As Long
values = ThisWorkbook.Worksheets("Sheet1").Range("A2:A5").Value
For rowIndex = LBound(values, 1) To UBound(values, 1)
Debug.Print values(rowIndex, 1)
Next rowIndex
End Sub
Even though the worksheet selection is one column wide, use two subscripts to retrieve each value: one for the row and one for the column. A common misconception is that a one-column range produces a one-dimensional array; when a range contains multiple cells, the returned array is two-dimensional. A single-cell range is a separate case: reading its value returns a scalar rather than a two-dimensional array, so code that must handle both one cell and several cells needs to account for that difference.
You can also write a correctly shaped array back to a range in one assignment. For example, an array containing four rows and two columns can be assigned to a matching four-row-by-two-column range. The destination must have the same dimensions as the array. This approach avoids writing each cell separately and is useful when a macro transforms a block of data before putting the results back on the sheet.
Multidimensional arrays
A two-dimensional array represents rows and columns, making it a natural fit for tabular data. Declare its bounds explicitly, for example Dim grid(1 To 3, 1 To 2) As Variant, and refer to an element with two indexes, such as grid(2, 1). A nested loop can visit every element, with one loop for the first dimension and another for the second. This structure is useful for data that has a consistent rectangular shape, such as a small table of products and quantities.
Arrays may have more than two dimensions, but additional dimensions make indexing and maintenance harder. For a typical worksheet range, a two-dimensional array is sufficient. Keep the meaning of each dimension consistent throughout the procedure: mixing up row and column indexes can put values in the wrong place without causing an obvious error.
Common problems and how to avoid them
- Confusing the upper bound with the item count: An array from 0 to 5 has six elements. Use
UBound(array) - LBound(array) + 1when you need the count for a one-dimensional, allocated array. - Using the wrong number of indexes: A one-dimensional array takes one index; an array loaded from a multi-cell worksheet range takes two. Match the indexing to how the array was created.
- Resizing away useful values:
ReDimwithoutPreservereplaces the allocated array and loses its contents. UseReDim Preservewhen appropriate, remembering its dimension limitation. - Assuming every cell contains a number: Worksheet data can include blanks, text, or error values. A numeric array may not be appropriate for mixed data; a Variant array can hold values of different kinds, but code should still handle unexpected content deliberately.
- Using a worksheet range with the wrong shape: When writing an array back, make the destination range match the array’s row and column counts.
During development, use Option Explicit at the top of the module and declare variables. It helps catch misspelled variable names before they cause confusing results. For debugging, the Immediate window and breakpoints in the Visual Basic Editor can help you inspect values; the editor’s Locals window can show variables while code is paused.
When to choose an array instead of another structure
Use an array when you need indexed access to a sequence or rectangular block of values, especially when the size is known or can be calculated. For example, an array can hold rows read from a range, allow calculations in memory, and return results to the sheet as a block. Arrays are also useful when processing many cells because they can reduce repeated communication with the worksheet.
A Collection may be more suitable when you need to add items without managing array bounds, or when you want to retrieve items by a key. A Dictionary is useful for looking up values by unique keys, such as matching a product code to a description; it is provided by the Microsoft Scripting Runtime or can be created through late binding. These structures have different behavior and are not interchangeable with arrays. For a simple fixed list or a worksheet-sized rectangle, an array is usually the more direct choice.
Frequently Asked Questions
How do I create an array in VBA?
Declare it with Dim, specify its bounds and data type, then assign values by index. For example, Dim values(1 To 3) As Long creates three Long elements with indexes 1 through 3.
What does LBound and UBound mean in VBA?LBound returns the lowest valid index, and UBound returns the highest valid index for a dimension. Use them to loop over an array without hard-coding its bounds.
How do I make a dynamic array in VBA?
Declare it with empty parentheses, such as Dim values() As String, and allocate a range of indexes using ReDim values(1 To 10). Use ReDim Preserve if you need to resize while retaining existing elements.
Can VBA read a range into an array?
Yes. Assign a multi-cell range’s Value to a Variant variable. VBA returns a two-dimensional array for a multi-cell range, even when that range has only one column.
Why does VBA say “Subscript out of range”?
The code is attempting to use an index that is outside the array’s allocated bounds, or it may be referring to a workbook or worksheet name that does not exist. Check the array bounds with LBound and UBound and verify the relevant workbook or sheet reference.
Related
- How to Open a TSV File in Excel
- How to Open a TXT File in Excel Without Losing Data
- How to Open a Query in Excel and Edit It in Power Query
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Copy and Paste Data Between Excel SheetsTo copy a range from one Excel worksheet to another with VBA, use a macro that names both…
- Excel VBA Find Method: Syntax, Examples, and TipsIn Excel VBA, use the Range.Find method to search a specified range and return the first matching cell;…
- Excel VBA Collection vs Dictionary: Which Should You Use?Use a VBA Collection when you need an ordered list of items, optionally addressed by unique string keys;…
- VBA Range.Insert: Insert Cells, Rows, and Columns in ExcelIn VBA, use the Range.Insert method to add cells, rows, or columns and shift existing content out of…
- Excel VBA: Open a SharePoint File in Edit ModeYes. In Excel for Microsoft 365 on Windows, VBA can ask Excel to open a SharePoint workbook with…
- VBA Array Duplicate Values: Find, Count, and RemoveYes. VBA arrays can contain duplicate values; VBA does not automatically prevent or remove them. To find repeats,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.