Excel VBA Array Maximum Size: Limits and Practical Tips

There is no single, guaranteed maximum number of elements for an Excel VBA array: its usable size depends mainly on available memory, Excel’s process limits, the element type, and the array’s shape. VBA permits up to 60 dimensions, but that is a language limit, not a promise that a large array will fit in memory.

The short answer: memory sets the practical limit

For a real workbook, an array usually becomes too large because Excel cannot allocate enough contiguous memory or because the computer is under memory pressure—not because the array has reached one universal element count. A compact array of numeric values can hold far more elements than an array of Variants containing strings or other separately allocated data. Even two arrays with the same number of elements can have very different memory requirements.

VBA uses a signed Long integer for many common array indexes and loop counters; its largest positive value is 2,147,483,647. That is an indexing detail, not a reliable allocation target or a statement that every array can contain that many elements. The actual allocation can fail much earlier. An array’s lower bound, upper bound, number of dimensions, and element type also affect how it is represented and accessed.

So if you are asking how large an array you can safely use, the useful answer is: test the intended data shape on the target computer, leave headroom for Excel and the workbook, and process large data in chunks rather than designing around a theoretical ceiling.

What the array limit does—and does not—mean

VBA supports fixed-size arrays, dynamic arrays, and arrays with more than one dimension. A declaration such as a one-dimensional array describes a different layout from a two-dimensional table, even if both contain the same number of values. Multi-dimensional arrays also have bounds for each dimension; their total element count is the product of the dimension sizes.

VBA allows an array to have as many as 60 dimensions. In everyday Excel work, arrays are usually one-dimensional lists or two-dimensional tables. Adding dimensions does not create more memory, and a 60-dimensional array is not a practical route to handling more worksheet data. It is simply the upper dimensionality supported by the language.

Bounds can start at zero or one, depending on how the array is declared and whether an applicable Option Base setting is used. Code should use LBound and UBound when it needs to discover bounds instead of assuming that every array starts at zero or one. This is especially important when a procedure receives an array from another procedure or gets values from a worksheet range.

A further distinction matters: the largest index, the number of elements, and the amount of memory are not interchangeable. An array with bounds 1 to 100 has 100 elements, not 101; an array with bounds 0 to 100 has 101. Neither example says anything by itself about how much memory a larger array will require.

Why array type changes memory use

Element type is one of the biggest factors in practical capacity. A typed Double array stores numeric values in a compact, predictable format, while a Variant array stores a Variant descriptor for every element. A Variant can hold several kinds of values, which makes it flexible but adds overhead. If Variant elements contain strings or objects, the underlying string or object data can require additional memory beyond the descriptors.

As a rough illustration, ten million Double values need about 80 MB just for the numeric payload, because each Double occupies eight bytes. A Variant array with ten million entries needs substantially more than 80 MB for its element storage, and strings, array bookkeeping, temporary copies, and Excel itself add further demands. These are estimates, not allocation guarantees: memory alignment, representation, and the rest of the process affect the actual requirement.

When the data type is known, a typed array can reduce overhead and catch some mistakes. For example, use a Long array for whole-number identifiers that fit in the Long range, or a Double array for floating-point numbers. Use Variant when values genuinely vary in type, or when an Excel range returns mixed values. Converting a large Variant array to another type can temporarily require memory for both the original and converted data, so conversion itself may be the point at which allocation fails.

Worksheet limits are not VBA array limits

In current desktop Excel for Microsoft 365, a worksheet has 1,048,576 rows and 16,384 columns. Those grid limits describe where cells can exist; they do not mean that VBA can load every cell on a sheet into one in-memory array. The full grid has more than 17 billion cell positions, and an array representing that many values would be far beyond a practical single allocation.

When a multi-cell range is assigned to a Variant using Value or Value2, Excel normally returns a two-dimensional, one-based array. For example, a range covering A1:B3 is represented as three rows by two columns. A one-cell range is a special case: its value is returned as a scalar rather than as a two-dimensional array. Code that accepts either a single cell or a larger range needs to account for that difference.

Value2 is commonly used for bulk data transfer because it avoids the Currency and Date conversions associated with Value. It does not make the data free to store: the returned array still consumes memory. A range containing formulas returns their calculated values through Value2, not the formula text. To read formulas, use the appropriate formula property instead.

Choosing a safe array size

There is no universal safe row count. Start with the values your macro needs, estimate the array’s element type and dimensions, then account for the workbook, add-ins, other open workbooks, and any temporary copies. A million numeric values may be manageable in one setup and problematic in another if each value is a Variant, the workbook is large, or memory is already heavily used.

Use the narrowest suitable type. Typed numeric arrays generally use less memory than Variants, provided the data fits the selected type.
Keep only necessary columns. Reading a range with ten columns when the calculation needs two multiplies the number of returned values.
Consider the array’s shape. The same count of values can be arranged as a long one-column array or a smaller two-dimensional block; choose the shape that suits the work and the source range.
Leave room for temporary data. Sorting, copying, conversion, or building a second result array can raise peak memory use well above the size of the original array.
Test realistic workloads. Include the largest expected input, not just a small sample, and test on the same Excel architecture and computer environment used by the workbook’s users.

Do not use the worksheet’s maximum row count as a target array size. An array sized for every row in several columns may be wasteful if only a small part of the sheet is populated. Find the relevant last row and read only the needed area.

Common allocation and indexing problems

An “Out of memory” error is not always proof that a computer has no free physical RAM. Excel must be able to make the required allocation inside its process, and fragmented or otherwise occupied address space can matter. A 32-bit Excel process has tighter address-space constraints than 64-bit Excel. Microsoft describes 32-bit Excel as having a limited process address space shared with the workbook and other loaded components; the exact usable amount depends on the Office version, Windows configuration, and what else is loaded. It is not safe to treat a quoted memory figure as memory reserved solely for your array.

64-bit Excel can generally use a much larger address space, but it is still constrained by available system memory, Excel’s needs, and the size of individual allocations. Moving to 64-bit Excel can help with memory-heavy workbooks, but it does not remove array-size limits or guarantee that a particular allocation will succeed. Compatibility with existing add-ins and other components should also be considered before changing Office architecture.

Other common failures are ordinary code errors rather than capacity ceilings. A subscript-out-of-range error can mean an index is outside the array’s actual bounds. A Type mismatch can arise when a value does not fit the expected type. ReDim Preserve has an important restriction: in a multi-dimensional array, it can resize only the last dimension while preserving existing values. Code that repeatedly expands an array can also become slow and memory-hungry; collecting data in blocks is often more efficient.

Use chunks for large worksheet data

Chunking means reading a manageable block, processing it, and then moving to the next block rather than loading a whole column or sheet at once. The example below reads column A from a worksheet named Data in blocks of 50,000 rows and totals numeric values. The block size is a practical starting point, not a guaranteed optimum; adjust it for the data and computer. The example leaves the source sheet unchanged.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. This macro uses standard desktop Excel VBA; Excel for the web cannot run VBA macros.

Sub SumColumnInChunks()
    Dim ws As Worksheet
    Dim data As Variant
    Dim firstRow As Long
    Dim lastRow As Long
    Dim finalRow As Long
    Dim chunkSize As Long
    Dim i As Long
    Dim total As Double

    ' Read only the used portion of column A in manageable blocks.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    chunkSize = 50000

    For firstRow = 1 To lastRow Step chunkSize
        finalRow = firstRow + chunkSize - 1
        If finalRow > lastRow Then finalRow = lastRow

        data = ws.Range(ws.Cells(firstRow, 1), _
                        ws.Cells(finalRow, 1)).Value2

        For i = LBound(data, 1) To UBound(data, 1)
            If IsNumeric(data(i, 1)) Then
                total = total + CDbl(data(i, 1))
            End If
        Next i
    Next firstRow

    MsgBox total
End Sub

This example assumes there is at least one used cell in column A; for a completely empty column, Excel’s End(xlUp) pattern reports row 1, so the macro reads one blank cell and returns zero. If your data starts below row 1, change the first-row logic to match. For a very large task, process each block into its final destination or update a running result instead of retaining every block in memory.

Windows, Mac, and Excel for the web

The array concepts and VBA language limits discussed here apply to desktop Excel on Windows and Mac, but practical memory availability depends on the computer and Excel build. Current Microsoft 365 desktop Excel for Mac is 64-bit; large allocations are still bounded by available resources. Windows-specific process address-space considerations should not be assumed to describe a Mac allocation exactly.

Excel for the web does not execute VBA macros. A workbook may contain VBA code, but running that code requires opening the file in a supported desktop version of Excel. If a workflow must run in a browser, consider whether Office Scripts or another supported approach fits the task; those are alternatives to VBA, not a way to run a VBA array in the web app.

Frequently Asked Questions

What is the maximum number of elements in a VBA array?
There is no dependable universal element count for every array and computer. The actual maximum depends on the array’s type and dimensions, available memory, Excel’s process limits, and other allocations. Treat the theoretical bounds as different from a safe working capacity.

Can a VBA array contain more than one million items?
Yes, an array can contain more than one million items when its type and memory requirements allow it. A million Double values have about 8 MB of raw numeric data, while a million Variants or strings require more. Excel, the workbook, and temporary copies also consume memory.

Is a VBA array limited to the number of rows in a worksheet?
No. Worksheet row and column counts are limits on the grid, not general limits on VBA arrays. Conversely, the existence of a large worksheet grid does not mean all of its cells can be loaded into one array at once.

Does 64-bit Excel remove the VBA array size limit?
No. 64-bit Excel gives the process access to a larger address space than 32-bit Excel, but memory and allocation limits remain. A large array can still fail, especially if it uses Variants, requires a second copy, or competes with other workbook data.

Why does assigning a single cell to a Variant sometimes break array code?
A multi-cell range normally returns a two-dimensional array, but a one-cell range returns a scalar value. Code that calls LBound or UBound on the result will fail if it assumes the scalar is an array. Handle the one-cell case separately when the selected range can contain only one cell.

What is the best way to handle a dataset that is too large?
Read and process the data in chunks, select only needed columns, and avoid keeping unnecessary copies. If the task is a data transformation or analysis, Excel features such as Power Query may also be suitable, depending on the operation and the workbook’s requirements.

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.