VBA Array Size: Practical Limits, Bounds, and Memory Use

There is no single fixed maximum size for a VBA array: its practical limit depends on available memory, the element type, and the array’s dimensions. To find how many elements an existing dimension contains, calculate UBound(arrayName, dimension) - LBound(arrayName, dimension) + 1; this counts both endpoints.

What “array size” means in VBA

People use “array size” to mean several different things: the number of elements, the range of valid indexes, the number of dimensions, or the amount of memory the array consumes. These are related but not interchangeable. An array declared with indexes from 1 through 10 has ten elements; the upper bound alone is not its element count. A two-dimensional array has a separate element count for each dimension, and its total number of elements is the product of those counts.

VBA arrays can be fixed-size or dynamic. A fixed-size array gets its bounds in a declaration. A dynamic array is declared without bounds and allocated or resized later with ReDim. Both kinds use lower and upper bounds, and both can store typed values or, if declared as a Variant array, values of different kinds. The chosen type and the array’s shape influence memory use.

How bounds determine the element count

VBA’s default lower bound is zero unless the module uses Option Base 1. You can avoid relying on that setting by writing explicit bounds, such as 1 To 5. The following procedure creates five Long elements and prints the count. It does not change workbook data, but save a copy of the workbook before running any macro.

Sub ShowArrayCount()
    Dim values(1 To 5) As Long
    Dim elementCount As Long

    ' Count both the first and last valid indexes.
    elementCount = UBound(values) - LBound(values) + 1
    Debug.Print elementCount
End Sub

The result is 5: UBound is 5, LBound is 1, and 5 − 1 + 1 equals 5. For a zero-based array declared with bounds 0 through 4, the same calculation also returns five. Use LBound and UBound rather than assuming an array begins at zero or one, especially when a procedure receives an array from another part of a project.

For a multidimensional array, supply the dimension number. If the first dimension has bounds 1 through 3 and the second has bounds 0 through 4, the shape is 3 by 5, or 15 elements in total. The first dimension’s count is UBound(data, 1) - LBound(data, 1) + 1; the second is calculated with dimension 2. Multiply the two counts to get the total number of positions.

VBA’s limits and Excel’s separate limits

VBA supports arrays with up to 60 dimensions, but that is a structural ceiling, not a promise that a very large array will fit in memory. There is no useful universal element-count limit for all arrays: a small array of simple numeric values and an equally sized array of Variants or strings can require very different amounts of memory. The available memory, the Excel process, the operating system, and other objects already in use all matter.

Excel worksheets have their own grid limits: 1,048,576 rows and 16,384 columns per sheet. Those limits apply to cells, not to every array held in VBA. A VBA array can be smaller than a worksheet range, or it can represent data that is not stored in a worksheet at all. Conversely, a range that fits on a sheet may still be too large to load into a memory-heavy array in a particular Excel session.

For this reason, avoid treating a stated row count as a guaranteed VBA maximum. A large allocation may fail with an out-of-memory error even when its indexes appear valid. Excel’s 64-bit edition can address more memory than the 32-bit edition, but it does not make memory unlimited or remove the need to consider the size and type of the data.

Choosing an array type and shape

Use the simplest suitable type. A typed array such as Long() stores whole numbers and generally uses less memory than a Variant() array containing the same number of values. Variants are useful when values have mixed types or when an Excel object method returns a Variant array, but they carry additional per-element information. Strings also need storage for their text, so their total memory use varies with the content.

Choose dimensions that match how the data will be used. A one-dimensional array is appropriate for a list. A two-dimensional array is often convenient for rows and columns. More dimensions can represent additional categories, but they make indexing and maintenance more complicated. Before allocating a large array, estimate its element count by multiplying the counts in each dimension and consider the type and likely content of each element.

Use a typed array when the values have one known type, such as Long for integer identifiers.
Use a Variant array when the values are mixed or when an Excel method supplies one.
Use explicit bounds when the intended starting index matters to the code.
Use a one-dimensional structure unless additional dimensions make the data easier to represent or process.

Declaring and resizing a dynamic array

Declare a dynamic array with empty parentheses, then allocate it with ReDim. This example creates ten Long elements with indexes 1 through 10, then enlarges the array to 20 elements while preserving its current values. It prints 20 as the final element count. Save a copy of the workbook before running a macro, since macros cannot be undone with Ctrl+Z.

Sub ResizeValues()
    Dim values() As Long
    Dim elementCount As Long

    ' Allocate ten elements with explicit bounds.
    ReDim values(1 To 10)

    ' Enlarge the one-dimensional array and keep its contents.
    ReDim Preserve values(1 To 20)

    elementCount = UBound(values) - LBound(values) + 1
    Debug.Print elementCount
End Sub

ReDim without Preserve creates a new allocation and discards the old contents. Use Preserve when existing values must remain. For a multidimensional array, VBA permits ReDim Preserve to change only the upper bound of the last dimension; it cannot change the number of dimensions or the other bounds while preserving data. This restriction can affect how you design an array that needs frequent growth.

Repeatedly enlarging an array one element at a time can be inefficient because resizing may require a new allocation and copying data. If the final size is known, allocate it once. If it is not known, a common approach is to grow capacity in larger steps, track how many positions contain real data, and resize to the exact count only when necessary.

Memory use and practical sizing

A rough estimate is the number of elements multiplied by the storage needed for each element, plus array and runtime overhead. For example, a one-million-element Long array contains about four megabytes of raw numeric values because a Long uses four bytes. That is not a complete estimate of the memory required by the VBA process: allocation metadata and other workbook objects also consume memory. A million Variants or strings should not be assumed to use the same amount.

Memory is also affected by how data moves between Excel and VBA. Loading a large worksheet range into an array can temporarily require memory for both the array and the workbook’s existing cell data. If the data is too large to handle comfortably at once, process it in batches, read only the columns needed, or avoid copying it into memory when a worksheet operation can do the job directly.

There is no need to allocate an array to the maximum possible worksheet size just because the sheet has that many rows available. Determine the actual last row or record count first, then allocate for the data you intend to process. This reduces unnecessary memory use and makes it easier to detect unexpected input sizes.

Arrays returned from Excel ranges

When VBA reads the Value or Value2 property of a range containing multiple cells, Excel generally returns a two-dimensional Variant array. Its bounds are commonly one-based, with the first dimension representing rows and the second representing columns. Code should still use LBound and UBound rather than hard-coding those assumptions, particularly if the array could come from another source.

A single-cell range is a notable exception: reading its value returns a scalar Variant, not a two-dimensional array. Code that expects an array can therefore fail when a selected range contains only one cell. If a procedure must handle both cases, account for the single-cell result explicitly or ensure that the input range always contains more than one cell.

When writing an array back to a range, the range dimensions should match the array’s row and column counts. For example, an array with three rows and two columns should be assigned to a range of three rows by two columns. Mismatched dimensions can cause errors or lead to results different from what the code expects.

Common size-related errors

Off-by-one counts: Subtracting the lower bound from the upper bound without adding one gives the distance between indexes, not the number of elements.
Unallocated dynamic arrays: Calling UBound on a dynamic array before it has been allocated can raise an error. Allocate it first or handle the unallocated state.
Wrong dimension number: A multidimensional array needs the correct dimension argument in LBound and UBound. Asking for a nonexistent dimension raises an error.
Unexpected lower bounds: Code that assumes index 0 or 1 may fail when the array was declared with different bounds or created under a different setting.
Out-of-memory errors: A valid-looking element count does not guarantee that Excel can allocate the requested storage at that moment.
Preserve restrictions: Trying to resize a non-final dimension with ReDim Preserve is not supported.

Frequently Asked Questions

How do I find the number of elements in a VBA array?
For a one-dimensional array, use UBound(arr) - LBound(arr) + 1. For a multidimensional array, calculate each dimension’s count separately using its dimension number; multiply those counts for the total number of positions.

Is there a maximum size for a VBA array?
There is no single practical element limit that applies to every array. Memory availability, element type, dimensions, and the Excel process all affect the allocation. VBA supports up to 60 dimensions, but that does not mean a large array will necessarily fit in memory.

Does Option Base 1 change an array’s size?
No. It changes the default lower bound for declarations that do not specify one; it does not change the number of elements requested. Explicit bounds such as 1 To 10 or 0 To 9 each describe ten elements.

Can ReDim Preserve resize every dimension?
No. For a multidimensional array, ReDim Preserve can change only the upper bound of the last dimension. If you need to grow a different dimension while retaining values, you may need to create another array and copy the data.

Are VBA arrays limited to Excel’s worksheet dimensions?
No. Worksheet row and column limits apply to the sheet grid, while VBA arrays are held in memory and have their own allocation constraints. The limits are different, although moving a large array to or from a worksheet still involves the worksheet’s row and column boundaries.

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.