VBA Array Declaration: Syntax, Types, and Examples

In VBA, declare an array with a variable name followed by parentheses, then specify its element type: for example, Dim scores(1 To 5) As Long. Use empty parentheses for a dynamic array that you size later with ReDim; choose explicit bounds when you want the array’s index range to be clear.

What an array declaration does

An array is one variable that holds multiple values of the same declared element type, arranged by index. Instead of creating separate variables such as score1, score2, and score3, you can store the values in scores and refer to an item by its index, such as scores(2).

The declaration tells VBA the array’s name, whether its size is fixed or changeable, and the type of value each element can hold. A declaration inside a procedure, such as Dim scores(1 To 5) As Long, makes the variable local to that procedure. At module level, Private or Public can control its scope. VBA does not support declaring an array as a Const.

Fixed-size arrays and index bounds

A fixed-size array has bounds specified in its declaration. For example, Dim months(1 To 12) As String creates 12 string elements, indexed from 1 through 12. You can also omit the lower bound: Dim months(12) As String. By default, that means indexes 0 through 12, so it creates 13 elements—not 12.

VBA’s default lower bound is zero unless the module contains an Option Base 1 statement at the top. That setting changes the default for declarations that omit a lower bound, but it does not change an explicitly stated bound such as 1 To 12. Explicit bounds make the intended number of elements and valid indexes easier to see, and avoid relying on a module-level setting.

For example, these declarations have different meanings:

Dim items(0 To 4) As String has five elements, indexed 0 through 4.
Dim items(1 To 5) As String has five elements, indexed 1 through 5.
Dim items(5) As String normally has six elements, indexed 0 through 5.

Dynamic arrays and ReDim

A dynamic array is declared with empty parentheses and sized later. This is useful when the number of items is not known until the code runs. Declare the element type in advance, then use ReDim to set its bounds. The following example creates an array sized from a variable and fills it with values:

Sub BuildScores()
    Dim scores() As Long
    Dim itemCount As Long
    Dim i As Long

    ' Set the number of items before sizing the array.
    itemCount = 5
    ReDim scores(1 To itemCount)

    ' Populate each element.
    For i = LBound(scores) To UBound(scores)
        scores(i) = i * 10
    Next i
End Sub

In this example, the elements contain 10, 20, 30, 40, and 50. ReDim can also be used again to resize a dynamic array. Without Preserve, resizing discards the array’s existing contents. ReDim Preserve keeps the existing values that still fit within the new bounds:

ReDim Preserve scores(1 To 8)

For a one-dimensional array, this extends the upper bound while retaining the first five values; the new elements are initialized to the default value for their type. In a multidimensional array, VBA permits Preserve to change only the upper bound of the last dimension. It cannot be used to change the lower bound. These restrictions matter when choosing the array’s shape at the start.

Choosing an element type

After the closing parenthesis, add As and a data type to control what each element stores. Use a type suited to the data rather than defaulting automatically to Variant.

As Long is a common choice for whole numbers and indexes.
As Double holds floating-point numbers, including values with decimal places.
As String stores text.
As Date stores date and time values.
As Variant allows elements to hold values of different types, but uses a more flexible representation.

For example, Dim prices(1 To 20) As Double creates an array of 20 numeric elements. A typed array helps make the intended data clear and can prevent accidental assignment of an incompatible value. A Variant is useful when values genuinely vary in type or when working with data returned by Excel, but it is not a requirement for every array.

Be careful when declaring several variables in one statement: Dim first, second As Long declares first as a Variant and second as a Long. To give both the same type, write Dim first As Long, second As Long.

Checking bounds and avoiding index errors

Use the built-in LBound and UBound functions to get an array’s lower and upper indexes. For a one-dimensional array named scores, LBound(scores) returns its first valid index and UBound(scores) returns its last. This is safer than assuming an array starts at zero or one, especially when a procedure receives an array created elsewhere.

A loop can use those bounds directly, as in the example above. For a multidimensional array, specify the dimension: LBound(data, 2) and UBound(data, 2) return the bounds for the second dimension. If you use an invalid index, VBA raises a subscript-out-of-range error. A common cause is using a loop from 1 to the element count when the array actually starts at 0, or treating an upper bound as the number of elements.

To calculate how many elements a one-dimensional array contains, subtract the lower bound from the upper bound and add one. For an array indexed 1 through 5, that is 5 - 1 + 1, or five elements.

Multidimensional array declarations

Put a comma between dimensions to create a multidimensional array. For example, Dim table(1 To 4, 1 To 3) As Double creates a two-dimensional array with four rows and three columns. An element is addressed with both indexes, such as table(2, 3). A three-dimensional declaration adds another bound and another index.

VBA does not assign spreadsheet row and column meaning automatically; the code defines how to interpret each dimension. If the first dimension represents rows and the second represents columns, use nested loops to visit each item. Keeping that convention consistent is important when moving values between an array and a worksheet range. When resizing a multidimensional dynamic array with ReDim Preserve, remember that only the final dimension’s upper bound can change.

Arrays and Excel ranges

Arrays are useful for processing worksheet data in memory. In Excel VBA, assigning a multi-cell range’s Value to a Variant variable produces a two-dimensional, one-based array. For instance, reading Range("A1:C10").Value gives an array with row and column indexes that can be accessed from 1. You can edit its elements in code and assign the resulting array to a same-sized range.

There is an important exception: a single-cell range’s Value is a scalar value, not a two-dimensional array. Code that handles both single cells and multi-cell ranges should account for that difference rather than assuming every range assignment returns an array. Also make sure the destination range has dimensions compatible with the array being written; mismatched shapes can cause errors or unexpected placement.

Arrays can reduce repeated worksheet interaction when code needs to examine or transform many cells. A typical approach is to read a block of cells into a variable, work with the values in memory, and write the finished block back. This is particularly useful for larger ranges, but the array’s indexes and dimensions should be checked before the write.

Passing arrays to procedures and functions

You can pass a typed array to a procedure by declaring an array parameter with empty parentheses, for example, Sub PrintScores(ByRef values() As Long). The receiving parameter must be compatible with the array’s element type. The procedure can then use LBound and UBound to iterate without hard-coding the caller’s bounds.

A Variant variable can also contain an array. This is a common approach for arrays produced by the Array function or values read from a worksheet. For example, Dim values As Variant declares a Variant variable, not a typed dynamic array; it can later hold an array value. This differs from Dim values() As Long, which declares a dynamic array whose elements must be Long values.

Common declaration mistakes

Confusing the upper bound with the element count: Dim data(10) As Long normally creates 11 elements, from 0 through 10.
Using ReDim on an undeclared name: declare a dynamic array first with empty parentheses, such as Dim data() As Long.
Expecting values to survive a resize: use ReDim Preserve when existing values must remain, and observe its dimension restrictions.
Assuming Option Base changes explicit bounds: it does not change declarations that state their lower bound.
Using IsArray as proof an array has been sized: it identifies whether a value is an array, but it does not by itself establish that a dynamic array has been allocated. Keep track of initialization or handle the relevant error when bounds are not yet available.

When a declaration or loop fails, inspect the actual bounds and type first. In the Visual Basic Editor, a breakpoint and the Locals window can help reveal the current values while stepping through the procedure. Clear, explicit bounds often prevent the problem before debugging is needed.

Frequently Asked Questions

How do I declare an array in VBA?
Write a variable declaration with parentheses and a type, such as Dim names(1 To 10) As String for a fixed array or Dim names() As String for a dynamic array.

Does VBA array indexing start at 0 or 1?
It depends on the declaration. The default lower bound is 0 when omitted, but a declaration can state a different lower bound explicitly, such as 1 To 10. Option Base 1 changes the default for eligible declarations in that module.

What is the difference between Dim and ReDim?
Dim declares the array variable. For a dynamic array, ReDim sets or changes its bounds while the code runs. A fixed-size array gets its bounds in the declaration.

What does ReDim Preserve do?
It resizes a dynamic array while retaining the values that remain within the new bounds. In multidimensional arrays, it can change only the upper bound of the last dimension.

Can a VBA array hold different data types?
A typed array such as Dim values() As Long stores elements of that declared type. A Variant variable can contain an array with values of varying types, which is more flexible but should be used when that flexibility is useful.

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.