Excel VBA Array Allocation: Declare, Size, and Resize

VBA & macros · Updated November 21, 2026

In Excel VBA, declare a dynamic array with empty parentheses, then allocate it with ReDim; for example, use Dim values() As Long followed by ReDim values(1 To 10). Use a fixed-size declaration when the number of elements is known in advance, and use explicit lower and upper bounds when you need predictable indexes.

What array allocation means in VBA

An array stores multiple values of the same declared type under one variable name, with each value accessed by an index. Allocation is the step that gives a dynamic array its dimensions and reserves storage for its elements. Declaring a dynamic array alone does not give it usable indexes: before reading or writing an element, the code must allocate it.

VBA distinguishes between fixed-size arrays and dynamic arrays:

  • A fixed-size array has its bounds in the declaration, such as Dim labels(1 To 8) As String. Its size is set when the procedure or module is initialized.
  • A dynamic array is declared with empty parentheses, such as Dim labels() As String, and receives its bounds later with ReDim.

Each element in a typed array starts with that type’s default value: for example, numeric values start at zero and strings start as empty strings. A Variant array can hold values of different kinds, but it generally uses more memory than an array of a specific type. Prefer a specific type such as Long, Double, or String when the data has a consistent type.

Allocate a dynamic array with ReDim

Use ReDim when the required number of elements is known only while the procedure runs. An explicit lower bound and upper bound make the number of elements clear: an array from 1 through 10 contains 10 elements. By contrast, an array from 0 through 10 contains 11 elements.

Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. The following procedure allocates five elements, assigns values, and prints them in the Immediate window of the VBA editor.

Sub AllocateAndFill()
    Dim values() As Long
    Dim i As Long

    ' Allocate five elements with indexes 1 through 5.
    ReDim values(1 To 5)

    ' Fill and display each element.
    For i = LBound(values) To UBound(values)
        values(i) = i * 10
        Debug.Print values(i)
    Next i
End Sub

Here LBound returns the lowest valid index and UBound returns the highest. Using those functions in loops is safer than assuming the array starts at zero or one. In the example, the Immediate window displays 10, 20, 30, 40, and 50.

Choose fixed or dynamic sizing

A fixed-size array is convenient when the required size is part of the design and will not change. It avoids a separate allocation statement and can make a small routine easier to read. For example, a procedure that always records the 12 months of a year could declare 12 elements directly.

A dynamic array is usually the better fit when the size comes from a worksheet range, a user selection, or a calculation. It lets code allocate exactly the needed capacity after measuring the input. Common choices include:

  • Use a fixed-size array for a stable, known count, such as seven weekday values.
  • Use a dynamic array when the number of records varies from one workbook run to another.
  • Use a typed array for uniform data, such as Long IDs or Double measurements.
  • Use a Variant array when values may have mixed types or when storing a range’s Value result.

Do not choose a very large fixed array simply to avoid resizing unless the memory and bounds are justified. An oversized array makes the intended data range less clear, while repeated resizing can also be inefficient. Select a strategy based on how the count is known and how the data will be used.

Set array bounds and avoid indexing errors

VBA’s default lower bound is usually zero, but a module can use Option Base 1 to change the default for declarations or dimensions that omit a lower bound. The exact lower bound can therefore be easy to overlook when code uses a single number inside the parentheses. For predictable behavior, specify both bounds, as in ReDim items(1 To count).

For an array with a lower bound of 1, the first element is items(1); with a lower bound of 0, it is items(0). An index outside the allocated bounds causes a subscript-out-of-range error. Loop from LBound(items) to UBound(items) when the bounds may vary, and check that a calculated index is within those limits before using it.

Calling LBound or UBound on a dynamic array that has not yet been allocated can itself raise an error. Do not treat a successful declaration as proof that the array has usable bounds. Allocate it first, or track its allocated state in the surrounding code. A Variant containing an array can be checked with IsArray, but that check alone does not establish that a particular dimension has been allocated with usable bounds.

Allocate two-dimensional arrays

Arrays can have more than one dimension. A two-dimensional array is useful for rows and columns, for example when preparing a rectangular block of worksheet data. Supply a lower and upper bound for each dimension in the same ReDim statement.

Sub AllocateGrid()
    Dim grid() As Variant
    Dim rowIndex As Long
    Dim columnIndex As Long

    ' Allocate three rows and two columns.
    ReDim grid(1 To 3, 1 To 2)

    ' Store a value at every row-column position.
    For rowIndex = LBound(grid, 1) To UBound(grid, 1)
        For columnIndex = LBound(grid, 2) To UBound(grid, 2)
            grid(rowIndex, columnIndex) = rowIndex * 10 + columnIndex
        Next columnIndex
    Next rowIndex
End Sub

The optional dimension argument to LBound and UBound identifies which dimension to inspect: 1 is the first dimension and 2 is the second. This example creates six values. Keep the meaning and order of dimensions consistent throughout the procedure; swapping row and column indexes can put data in the wrong worksheet positions even when the array bounds themselves are valid.

Resize an array and preserve its contents

Running ReDim on an already allocated dynamic array normally creates the requested dimensions again and discards the existing contents. To retain values while changing the size, use ReDim Preserve. For a one-dimensional array, this is a typical way to expand its upper bound without losing earlier elements.

There is an important limitation: ReDim Preserve can change only the upper bound of the last dimension. It cannot change the lower bound, and for a multidimensional array it cannot resize an earlier dimension while preserving the contents. If a two-dimensional array needs more rows, consider allocating a larger array and copying values, or using a different data structure.

Preserving data can involve copying it into new storage, so repeatedly growing an array by one element may be slow when a procedure processes many records. If the approximate or exact count is available, allocate once to that size. If it is not, grow the capacity in larger steps and keep a separate count of how many positions contain actual data. That approach reduces the number of resize operations, though it requires code to distinguish used elements from spare capacity.

Use arrays with worksheet ranges

VBA arrays are useful for transferring worksheet data in batches. Assigning the Value property of a multi-cell rectangular range to a Variant stores the result as a two-dimensional, one-based array. A single-cell range is different: its value is a scalar, not a two-dimensional array. Code that may receive either one cell or several should account for that distinction rather than assuming every result supports two indexes.

For a rectangular output range with the same number of rows and columns as a two-dimensional array, assigning the array to the range’s Value property writes the values as a block. This is often simpler and faster than writing each cell in a loop. The source and destination dimensions should match; otherwise, the assignment may not produce the intended layout. When working with a one-dimensional array, reshape or copy the values into a two-dimensional structure if a row or column range requires it.

For example, with data in cells A1:C3 on Sheet1, a Variant can receive the range’s values and be processed by row and column. The range must contain multiple cells for the result to be a two-dimensional array. Excel for the web does not run VBA macros; use the desktop Excel application to run VBA. The core array syntax is similar in desktop Excel for Windows and Mac, though available integrations or platform-specific code outside basic arrays can differ.

Common allocation problems and fixes

Array errors often come from a mismatch between the bounds the code allocated and the indexes it later uses. Before changing a routine, identify the declared type, the allocation statement, the lower and upper bounds, and the dimension being indexed. These checks help diagnose the most frequent problems:

  • Subscript out of range: A loop or lookup uses an index below the lower bound or above the upper bound. Use the array’s actual bounds and verify calculated indexes.
  • Array not allocated: A dynamic array was declared but never sized, or it was cleared with Erase. Allocate it before reading elements or requesting its bounds.
  • Values disappear after resizing: A later ReDim omitted Preserve. Preserve existing contents when appropriate, remembering its dimension restrictions.
  • Wrong number of elements: A bound was mistaken for a count. An inclusive range from 1 to 10 has 10 elements, while 0 to 10 has 11.
  • Range assignment behaves unexpectedly: A single-cell range returns a scalar value, unlike the two-dimensional array returned for a multi-cell range.

Erase also behaves differently depending on the array type. For a dynamic array it releases the allocated array storage; for a fixed-size array it resets the elements to their default values. If a procedure uses Erase, it should not assume the former dynamic bounds are still available afterward. Reallocate the dynamic array before reusing it.

Frequently Asked Questions

Do I need ReDim after declaring an array with empty parentheses?
Yes. A declaration such as Dim data() As Long defines a dynamic array variable, but it has no allocated elements yet. Use ReDim to give it bounds before accessing an element.

What is the difference between Dim and ReDim in VBA?
Dim declares a variable and its type; it can also declare a fixed-size array when bounds are included. ReDim sets or resets the bounds of a dynamic array while the code runs.

How can I find the size of an array?
Use LBound and UBound for its bounds. For a one-dimensional array, the element count is the upper bound minus the lower bound plus one. For a multidimensional array, ask for the bounds of each dimension separately.

Can ReDim Preserve resize every dimension?
No. It can preserve existing values while changing only the upper bound of the last dimension. To grow a different dimension while keeping data, allocate another array and copy the values, or redesign the storage approach.

Should I use a Variant array for every worksheet task?
No. A Variant is useful for mixed data and for receiving multi-cell range values, but a typed array is clearer and often more memory-efficient for uniform data. Choose the type that matches the values and the way the procedure handles them.

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.