Runtime Error 7: Out of memory (Large array handling).

VBA raises Runtime Error 7: Out of memory when a macro demands more memory than the Excel process or the operating system can allocate. During data processing, this error typically occurs when building oversized dynamic arrays, dimensioning multi-dimensional arrays with excessive bounds, retaining large object graphs in memory, or running automated loops inside 32-bit Excel where available address space is strictly limited.

Quick Answer

To resolve Runtime Error 7, replace memory-heavy Variant arrays with explicit numeric types (such as Long or Double), process large datasets in smaller chunks rather than loading millions of cells into RAM at once, clear large arrays using Erase arrayName, and consider upgrading from 32-bit to 64-bit Excel if your data models regularly exceed 2 GB of addressable memory.

---

Why “Out of Memory” Happens in VBA

Memory in Excel is not infinite. Even on modern computers with 32 GB or 64 GB of physical RAM, Excel operates under strict process boundaries:

1. 32-bit Excel Limit: The 32-bit edition of Excel has a maximum virtual address space of 2 GB (or up to 4 GB with Large Address Aware updates). This space is shared between the Excel workbook, formatting, undo buffers, add-ins, and the VBA runtime.
2. The Variant Overhead Trap: A default dynamic array declared as Dim myData() is an array of Variant types. Each individual Variant element consumes 16 bytes of RAM plus string or data storage. An array typed as Long consumes only 4 bytes per element—75% less memory.
3. Multi-Dimensional Matrix Bloat: Declaring a 3D or large 2D matrix (such as ReDim matrix(10000, 10000)) requests 100 million elements simultaneously. Even with lightweight data types, this easily exhausts contiguous RAM.

' Risky: Declaring an unbounded Variant array on massive ranges
Dim massiveData As Variant
massiveData = Range("A1:ZZ100000").Value ' Can quickly trigger Error 7 in 32-bit Excel

If your workbook runs out of calculation resources independent of VBA code, review “Excel ran out of resources while attempting to calculate”: Understanding 2GB vs. Large Address Aware limits..

---

Key Diagnostic Steps and Solutions

1. Use Typed Arrays Instead of Variants

If you do not require mixed data types, declare the array using the smallest appropriate data type:

' Inefficient (16 bytes per element):
Dim rawVals() As Variant

' Efficient (4 bytes per element):
Dim cleanVals() As Long

' Efficient for floating point (8 bytes per element):
Dim metrics() As Double

2. Reclaim Memory Explicitly with Erase

VBA’s internal garbage collection does not always reclaim memory immediately during long-running procedures. If you populate a large array, write its contents to a worksheet or database, and then continue running code, use the Erase statement to free that memory immediately:

Sub ProcessDataInBatches()
    Dim tempArray() As Double
    ReDim tempArray(1 To 500000)

    ' ... perform calculations ...

    ' Free the memory buffer back to Excel:
    Erase tempArray
End Sub

For persistent objects (like dictionaries or external collections), set them to Nothing when you finish using them, as covered in Excel Crashing on Close: How to properly clear Object variables from memory..

3. Chunk Massive Datasets

Never pull hundreds of thousands of rows into a single in-memory array all at once. Process rows in manageable blocks (for example, 25,000 to 50,000 rows at a time):

Sub ChunkProcessing()
    Dim ws As Worksheet
    Dim startRow As Long, chunkSize As Long, lastRow As Long
    Dim chunkData As Variant

    Set ws = ThisWorkbook.Worksheets("BigData")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    chunkSize = 25000

    For startRow = 1 To lastRow Step chunkSize
        ' Pull only the active chunk into memory
        chunkData = ws.Range(ws.Cells(startRow, 1), _
                             ws.Cells(Application.Min(startRow + chunkSize - 1, lastRow), 20)).Value

        ' Process chunkData here...

        ' Clear reference before next iteration
        chunkData = Empty
    Next startRow
End Sub

For architecture limits and migrating large-scale financial or engineering models to 64-bit systems, consult 32-bit vs. 64-bit: Fixing “The code in this project must be updated for use on 64-bit systems.”.