Excel VBA Array Syntax: Declarations, Bounds, and Examples

VBA & macros · Updated November 21, 2026

In Excel VBA, declare an array with parentheses after its name, such as Dim scores(1 To 5) As Long; use Dim scores() As Long when you need to size it later. The bounds determine which indexes are valid, and VBA arrays can be fixed-size, dynamic, or multidimensional.

What an array is in VBA

An array is one variable name that holds multiple values of the same declared type, or values inside a Variant array. Instead of creating separate variables such as score1, score2, and score3, you can store related values under one name and access each item by its index. Arrays are useful for calculations, temporary data, and moving groups of worksheet values between Excel and VBA.

The basic declaration has three parts: the keyword Dim, the array name followed by parentheses containing bounds, and an optional data type. For example, Dim temperatures(1 To 12) As Double creates a fixed array with twelve Double elements. You refer to an element by putting its index in parentheses: temperatures(1) is the first element.

VBA array indexes are not necessarily the same as worksheet row or column numbers. The first index is whatever lower bound the declaration establishes. Keep that bound in mind when looping or matching an array to a range.

Fixed-size arrays and array bounds

A fixed-size array has its bounds in the declaration and cannot be resized later. In Dim marks(1 To 4) As Long, valid indexes are 1, 2, 3, and 4: the upper bound is included. By contrast, Dim marks(4) As Long normally means indexes 0 through 4, or five elements, not four. This default lower bound of zero is a frequent source of off-by-one mistakes.

Use explicit bounds when you want code to be easy to read and independent of module settings. Option Base 1 at the top of a module changes the default lower bound for declarations that omit the lower bound, so Dim values(4) then starts at 1. It does not change declarations that explicitly say 0 To 4 or 1 To 4. It is usually clearer to specify the intended bounds directly than to rely on Option Base.

VBA provides LBound and UBound to check the first and last valid indexes. These are especially useful when a procedure receives an array whose bounds are not known in advance. The second argument identifies a dimension for multidimensional arrays.

Sub FixedArrayExample()
    Dim scores(1 To 4) As Long
    Dim i As Long

    ' Fill each element, then display it in the Immediate window.
    For i = LBound(scores) To UBound(scores)
        scores(i) = i * 10
        Debug.Print scores(i)
    Next i
End Sub

Before running any macro, save a copy of the workbook. Macros can change workbook contents, and their actions cannot be reversed with Ctrl+Z.

Dynamic arrays and ReDim

A dynamic array is declared with empty parentheses and given bounds later. This is useful when the number of items depends on a worksheet selection, an imported file, or a calculation. ReDim allocates the array or changes its size. The array must be declared before it is redimensioned.

Sub DynamicArrayExample()
    Dim readings() As Double
    Dim i As Long

    ' Allocate three elements, indexed from 1 through 3.
    ReDim readings(1 To 3)
    For i = 1 To 3
        readings(i) = i / 2
    Next i

    ' Preserve the existing values while extending the upper bound.
    ReDim Preserve readings(1 To 5)
    readings(4) = 2
    readings(5) = 2.5
End Sub

Without Preserve, ReDim creates a newly sized array and existing contents are lost. With Preserve, VBA retains the values that still fit, but there are restrictions: for a one-dimensional array, you can change the upper bound, not the lower bound. For a multidimensional array, only the final dimension can be resized while preserving values. If those constraints do not fit the task, copy the data into a new array or redesign the dimensions.

Erase also behaves differently according to the array type. For a fixed-size array, it resets the elements to their default values; for a dynamic array, it releases the allocated storage. Numeric elements reset to zero, while strings and object references have their corresponding empty or unassigned state.

Multidimensional array syntax

Separate dimensions with commas inside the parentheses. For example, Dim table(1 To 3, 1 To 2) As Variant has three rows and two columns of array elements. Access an item with both indexes, as in table(2, 1). VBA permits more dimensions, but two-dimensional arrays are the most common when representing rows and columns.

The order matters: in a two-dimensional array, the first index is conventionally treated as the row and the second as the column. This convention aligns well with a two-dimensional range value from Excel. Use LBound(table, 1) and UBound(table, 1) for the first dimension, and pass 2 to check the second dimension. Do not assume both dimensions start at zero; worksheet range arrays commonly start at 1.

Array function and Variant arrays

The Array function is a compact way to create a Variant array from a list of values. Declare the receiving variable as Variant because it will hold the array: Dim items As Variant, followed by an assignment such as items = VBA.Array(10, 20, 30). The resulting elements are Variants, even though the example values are numbers.

The lower bound produced by the unqualified Array function is affected by the module’s Option Base setting. Qualifying it as VBA.Array makes the lower bound zero. Check it with LBound rather than guessing, especially if the code could run in modules with different settings. Array is convenient for a short list of values; for larger or strongly typed data, declaring a typed array gives clearer intent and can avoid unnecessary Variant values.

Reading and writing worksheet ranges

A common reason to use an array in Excel VBA is to process many cells without reading or writing each cell separately. Assigning a multi-cell range’s Value property to a Variant gives a two-dimensional Variant array. Its first dimension corresponds to worksheet rows and its second to columns. The indexes are generally one-based, so the top-left cell maps to element (1, 1).

Sub RangeArrayExample()
    Dim source As Variant
    Dim target As Variant

    ' Read a 3-by-2 block, then write the values two columns to its right.
    source = Worksheets(1).Cells(1, 1).Resize(3, 2).Value
    target = source
    Worksheets(1).Cells(1, 4).Resize(3, 2).Value = target
End Sub

This example writes to the active workbook’s first worksheet, so check the selected workbook and destination before running it. Save a copy first; Ctrl+Z does not undo macro changes. For a single-cell range, the Value property returns a scalar rather than a two-dimensional array. Code that may receive one cell or many should account for this difference instead of always indexing the result as data(1, 1).

When writing an array to a range, the destination dimensions must suit the array’s shape. A two-dimensional array with three rows and two columns belongs in a three-by-two range. Excel fills the corresponding cells from the array. If the source contains formulas or formatting you intend to preserve, remember that assigning Value transfers values, not the source cell’s formatting.

Looping through arrays

A numeric For loop is the most flexible way to visit array elements because it exposes each index. Use the bounds rather than hard-coding a final number, particularly when the array can be resized or comes from another procedure. For a two-dimensional array, use nested loops and separate bounds for each dimension.

A For Each loop can be more readable when the index is not needed. A Variant control variable is a useful choice for iterating array elements, especially when the element type may vary. However, when you need the position of a value, use a numeric loop: For Each gives you the value, not its array index. This distinction matters when updating a particular element or pairing values with row numbers.

Passing arrays to procedures

Array parameters use parentheses in the procedure declaration. For example, a procedure that accepts a Long array can declare its parameter as ByRef values() As Long, and a caller can pass an array declared as Long. The element type must be compatible. A procedure that accepts an array held in a Variant uses a different parameter pattern, such as a Variant argument, so make the expected kind of input explicit.

VBA procedure signatures can be confusing because an array is not the same thing as a single element. A parameter declared as values As Long accepts one Long, not a Long array. Conversely, parentheses in the declaration signal an array parameter. When passing arrays between procedures, agree on whether the receiving code may modify the original data; ByRef is the default in VBA, while ByVal has different behavior and array passing has language-specific constraints. Test the interface with a small example before relying on it in a larger macro.

Choosing arrays and avoiding common errors

Arrays work well when data is ordered, indexed, and processed in batches. They are not the only way to store multiple values. A VBA Collection is useful when you want an ordered set that can grow by adding items, while an array is often preferable when you know the size or need fast index-based access. Choose based on how the data will be used rather than assuming every list should be an array.

  • Use a fixed-size typed array when the number of items is known and consistent.
  • Use a dynamic array when the item count changes, then size it with ReDim.
  • Use a Variant array for mixed values or for values read from a multi-cell range.
  • Use a Collection when adding or removing individual items is more important than fixed indexes.

Common problems are usually bounds or shape errors, not complicated syntax. An index outside LBound and UBound raises a subscript error. ReDim without Preserve discards prior contents. A single-cell range does not produce the same array shape as a multi-cell range. Also avoid confusing the array’s element count with its upper bound: for bounds 0 To 4, there are five elements. Calculate a count as upper bound minus lower bound plus one when needed.

For Microsoft 365 Excel on Windows, open the VBA editor with Alt+F11. On Mac, Option+F11 commonly opens it; some keyboards require Fn as well, and the shortcut can depend on system keyboard settings. Excel for the web can open workbooks containing macros but does not provide the desktop VBA editor or run VBA macros. On either desktop platform, enable macros only for workbooks you trust.

Frequently Asked Questions

How do I declare an array in Excel VBA?

Use Dim name(lower To upper) As Type for a fixed-size array, such as Dim ids(1 To 10) As Long. For a dynamic array, use empty parentheses in the declaration, such as Dim ids() As Long, and allocate it later with ReDim.

Does VBA array indexing start at zero or one?

It depends on the declaration. If you give only an upper bound, the default lower bound is zero unless the module uses Option Base 1. Explicit bounds remove the ambiguity: Dim x(1 To 5) As Long starts at one, while Dim x(0 To 4) As Long starts at zero.

How do I find an array’s size?

Use LBound and UBound to find its first and last indexes. For a one-dimensional array, the number of elements is UBound(array) minus LBound(array) plus one. For a multidimensional array, specify the dimension number in both bound checks.

What does ReDim Preserve do?

It changes the size of a dynamic array while retaining values that fit in the new bounds. Preserve has limits: it cannot change a one-dimensional array’s lower bound, and for a multidimensional array it can resize only the last dimension.

Can I assign a worksheet range to a VBA array?

Yes. Assign a multi-cell range’s Value property to a Variant variable to receive a two-dimensional array, then read its elements using row and column indexes. Handle a one-cell range separately because its Value is a single value, not a two-dimensional array.

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.