Excel VBA Dim Array: Declare and Use Arrays

VBA & macros · Updated November 21, 2026

In Excel VBA, use Dim to declare an array: for example, Dim scores(1 To 10) As Long creates a fixed array of ten whole-number values. To make an array whose size can be set later, declare it with empty parentheses—Dim scores() As Long—and use ReDim before storing values in it.

What Dim Means in VBA

Dim is VBA’s declaration statement. It tells VBA a variable’s name and, optionally, its data type and storage dimensions. An array is a variable that holds multiple values of the same declared type, arranged by one or more indexes. Instead of creating separate variables such as score1, score2, and score3, you can store related values in one array and refer to each value by its index.

The general pattern for a one-dimensional array is Dim arrayName(lowerBound To upperBound) As DataType. The lower and upper bounds identify the first and last valid indexes. For instance, an array declared from 1 to 10 has ten elements: indexes 1, 2, and so on through 10. The number in parentheses is the upper bound when no lower bound is written, not the number of elements in every possible declaration.

Arrays can also have multiple dimensions. A two-dimensional array is useful for data arranged in rows and columns, while a one-dimensional array is often enough for a list of values. Choose the simplest structure that matches the data you need to process.

Fixed-Size and Dynamic Arrays

A fixed-size array has its dimensions specified in the declaration. Its bounds remain the same while the procedure runs. A dynamic array is declared without bounds and sized later with ReDim. The choice depends on whether the number of values is known when the code is written.

Sub FixedAndDynamicExamples()
    Dim months(1 To 12) As String
    Dim values() As Double

    ' The dynamic array must be sized before it is used.
    ReDim values(1 To 5)

    months(1) = "January"
    values(1) = 12.5
End Sub

In this example, months always has twelve elements, while values receives five elements when the procedure runs. If the number of values depends on a worksheet or user input, a dynamic array is generally more practical. If the number is stable and known, a fixed array makes the intended capacity clear.

A dynamic array is not automatically sized to fit its contents. Attempting to assign an element before using ReDim causes a subscript-related runtime error. This is a common difference from some other programming languages, where arrays may grow automatically.

Choose Bounds and Check Array Size

VBA arrays do not always start at index zero. By default, an array with a single bound such as Dim items(5) As String usually has indexes 0 through 5, which is six elements. A declaration with explicit bounds, such as Dim items(1 To 5) As String, has five elements. Explicit bounds help prevent confusion because the lower and upper limits are visible in the code.

The module-level statement Option Base 1 changes the default lower bound for arrays declared in that module without an explicit lower bound. It must appear before procedures. It does not override bounds written explicitly, and it does not change the lower bound of arrays created with the Array function. For clarity, writing both bounds explicitly is often preferable to relying on a module setting.

Use LBound and UBound to find an array’s lowest and highest valid indexes. This is safer than assuming an array starts at zero or one, especially when working with an array passed in from another procedure.

Sub ShowArrayBounds()
    Dim names(1 To 4) As String
    Dim i As Long

    For i = LBound(names) To UBound(names)
        Debug.Print i
    Next i
End Sub

This loop prints indexes 1 through 4 in the VBA Immediate window. For a multidimensional array, specify the dimension as the second argument, such as UBound(grid, 2) for the upper bound of the second dimension.

Store Values and Loop Through an Array

Assign a value to a particular element by using its index in parentheses. The declared data type applies to each element, so a Long array stores whole numbers and a String array stores text. VBA converts some compatible values automatically, but relying on implicit conversions can make unexpected input harder to diagnose.

Sub FillAndReadArray()
    Dim quantities(1 To 3) As Long
    Dim i As Long

    quantities(1) = 8
    quantities(2) = 12
    quantities(3) = 5

    For i = LBound(quantities) To UBound(quantities)
        Debug.Print quantities(i)
    Next i
End Sub

Using LBound and UBound in the loop means the code still works if the bounds change. A For Each loop is another option when you need each value but do not need its index. When the index is needed to match an array value to a worksheet row or another array, a numbered For loop is more useful.

For multiple dimensions, supply an index for each dimension. For example, an array declared as Dim grid(1 To 3, 1 To 2) As Double has six elements, addressed as grid(rowIndex, columnIndex). Keep the dimension order consistent throughout the code so that rows and columns are not accidentally reversed.

Use Arrays with Excel Ranges

Arrays can reduce repeated worksheet communication when code processes many cells. In Excel VBA, assigning a multi-cell range’s Value to a Variant variable returns a two-dimensional, one-based array of values. The first dimension corresponds to worksheet rows and the second to columns. This makes it possible to read a block once, work with values in memory, and write a result block back to the sheet.

Sub ReadRangeIntoArray()
    Dim data As Variant
    Dim rowIndex As Long

    ' Read A2:B4 into a two-dimensional Variant array.
    data = ThisWorkbook.Worksheets("Sheet1").Range("A2:B4").Value

    For rowIndex = LBound(data, 1) To UBound(data, 1)
        Debug.Print data(rowIndex, 1), data(rowIndex, 2)
    Next rowIndex
End Sub

Here, data contains three rows and two columns, so its row indexes run from 1 to 3 and its column indexes from 1 to 2. A range containing only one cell is an important exception: its Value is a single value rather than a two-dimensional array. Code that may receive either one cell or a larger range should account for that difference instead of blindly indexing two dimensions.

A Variant array is convenient for range values because cells can contain different kinds of data, including text, numbers, dates, and errors. When using an array to write back to a range, its dimensions should match the target range. Test code on a copy of the workbook before running a macro that changes cells; VBA macros cannot be undone with Ctrl+Z.

Resize a Dynamic Array with ReDim

ReDim sets or changes the bounds of a dynamic array. You can use it again to allocate a different size, but a normal ReDim discards the existing contents. Add Preserve when you need to keep the current values while changing the array’s size.

Sub ResizeArrayExample()
    Dim values() As Long

    ReDim values(1 To 3)
    values(1) = 10
    values(2) = 20
    values(3) = 30

    ' Preserve existing elements while extending the upper bound.
    ReDim Preserve values(1 To 5)
    values(4) = 40
    values(5) = 50
End Sub

There is a significant restriction: with ReDim Preserve, VBA permits changing only the upper bound of the last dimension. For a one-dimensional array, that is the only dimension, so extending its upper bound works as shown. For a two-dimensional array, you can preserve values while changing the upper bound of the second dimension, but not the first. If an algorithm needs to grow rows repeatedly, consider storing the data differently or allocating a sufficiently large array and tracking how many elements are in use.

Resizing an array repeatedly by one element can be inefficient for large amounts of data because VBA may need to allocate memory and copy existing values each time. If the eventual size can be estimated, allocate capacity in larger increments or determine the required size before filling the array.

Pick an Appropriate Array Data Type

The type after As determines what each element is intended to hold. For counters and ordinary integer values, Long is a common choice. Use Double for values that need fractional numbers, String for text, and Date for date and time values. A typed array documents what the code expects and can catch some incompatible assignments.

Use Variant when values genuinely may have different types or when receiving a worksheet range. A Variant can hold many kinds of values, but it uses more flexible storage and makes it easier for unexpected content to pass through unnoticed. A useful pattern is to read worksheet data into a Variant array, validate or convert the values, and then put the cleaned results into a more specific array if the program benefits from one.

For example, a list of product names and a list of quantities may be kept in separate arrays with matching indexes, but that relationship must be maintained by the code. If records need to grow or contain many different fields, a collection or a user-defined type may be a better fit than several parallel arrays.

Common Array Errors and How to Avoid Them

  • Subscript out of range: An index is outside the array’s declared bounds, or code refers to a dimension that does not exist. Check the bounds with LBound and UBound, and verify which dimension an index represents.
  • Using a dynamic array before sizing it: Declare it with empty parentheses, then execute ReDim before assigning an element.
  • Assuming a bound means a count: Dim a(5) normally provides six elements because the default lower bound is zero. Use explicit bounds or calculate the count as UBound(a) - LBound(a) + 1.
  • Losing values during resizing: A plain ReDim erases the prior contents. Use ReDim Preserve where its dimension restriction allows it.
  • Indexing a one-cell range as an array: A single cell returns a scalar value. Handle that case separately if the same code processes both single-cell and multi-cell ranges.

When debugging, inspect the bounds and the value being used at the point the error occurs. The VBA editor’s Immediate window can display values written with Debug.Print. You can open the Visual Basic Editor from Developer > Visual Basic; the Windows shortcut is Alt+F11, and on Mac it is Option+F11, sometimes with Fn depending on the keyboard and system settings.

Arrays Compared with Collections

An array is a good choice when the items have a consistent structure, you want indexed access, or you need to pass a block of values to and from a worksheet. Arrays are especially convenient for numerical calculations and for processing a known set of values without repeated sheet reads.

A VBA Collection can grow as items are added and can hold items without declaring a fixed array size. It also supports keys for retrieving items by name. That flexibility comes with different access patterns and does not make a Collection a direct substitute for a rectangular range-sized array. For a changing list of simple, similarly typed values, a dynamic array may still be suitable; for a set of objects or keyed items, a Collection may fit better. The choice is about how the data changes and how it needs to be retrieved, not about one structure being universally faster or better.

Frequently Asked Questions

How do I declare an array in Excel VBA?
Use Dim name(bounds) As Type for a fixed array, such as Dim ids(1 To 20) As Long. For an array sized later, use empty parentheses, such as Dim ids() As Long, and then call ReDim.

Does a VBA array start at zero or one?
It depends on the declaration. An array without an explicit lower bound generally starts at zero unless its module uses Option Base 1. An explicit declaration such as Dim data(1 To 10) As String starts at one regardless of that default.

How can I find the number of elements in an array?
For a non-empty, one-dimensional array, calculate UBound(arrayName) - LBound(arrayName) + 1. For a multidimensional array, perform that calculation separately for each dimension.

What is the difference between Dim and ReDim?
Dim declares the array variable and its type. For a dynamic array, ReDim allocates or changes its bounds after declaration. ReDim Preserve also retains existing values when resizing is allowed.

Can I put an Excel range into a VBA array?
Yes. Assign a multi-cell range’s Value to a Variant variable to receive a two-dimensional array. Remember that a one-cell range returns 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.