VBA Array Example: Declare, Fill, and Loop Through Data

VBA & macros · Updated November 21, 2026

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) + 1 when 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: ReDim without Preserve replaces the allocated array and loses its contents. Use ReDim Preserve when 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

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.