Excel VBA Out of Memory Error: Causes and Fixes

VBA & macros · Updated November 19, 2026

An Excel VBA “Out of memory” error usually means Excel cannot allocate enough usable memory for the macro’s next operation—not necessarily that the computer has run out of physical RAM. Check whether Excel is 32-bit, identify the operation that fails, then reduce the amount of data the macro holds or processes at once; restarting Excel may help temporarily, but it will not fix a macro that repeatedly requests too much memory.

What the VBA out-of-memory error means

In VBA, “Out of memory” is commonly associated with runtime error 7. It can appear while a macro creates an array, assigns a large range to a variable, opens a workbook, copies data, or creates many objects. Excel may also show a general warning or become unresponsive instead of displaying a clear VBA error. The point of failure is useful evidence: a failure on a line that reads a million-cell range suggests a different problem from a failure when opening one particular file.

Memory is not the same thing as workbook file size. An .xlsx file is compressed on disk, while Excel may need much more working memory to represent its cells, formulas, formatting, calculations, and temporary copies. A modest-looking file can therefore be demanding to open or process. Conversely, a large file may work if the macro handles its data efficiently and the computer has sufficient resources.

Why Excel’s bitness matters

A 32-bit Excel process has a much smaller address space available to it than a 64-bit process. That limit applies to the memory Excel can address, not just the amount of RAM installed in the computer. As a result, 32-bit Excel can encounter an allocation failure even when Windows reports that the computer still has free memory. Some 32-bit Excel releases and Windows configurations can use more address space than others, so a single fixed limit does not describe every installation; the practical constraint is still substantially tighter than with 64-bit Excel.

To check the installation on Windows, open Excel and choose File > Account > About Excel. The dialog identifies whether the program is 32-bit or 64-bit. On Mac, use Excel > About Microsoft Excel; current Mac versions are 64-bit, but they still have finite memory and can fail on huge workloads. Excel for the web does not run VBA macros, so a VBA memory error must be investigated in the desktop application.

Moving to 64-bit Excel can help when the workbook and macro genuinely need a large address space, but it is not a guarantee against memory errors. Confirm that any COM add-ins, ActiveX components, API declarations, and other VBA dependencies are compatible with 64-bit Office before changing installations. A macro that unnecessarily loads millions of values at once can still exhaust resources in 64-bit Excel.

Common causes in macros and workbooks

  • Oversized arrays: Loading an entire worksheet column into a VBA array creates a large in-memory structure, especially if the code also makes copies of it.
  • Excessive cell-by-cell work: Repeatedly reading and writing cells is slow; attempts to speed it up by retaining every intermediate result can increase memory use instead.
  • Unbounded ranges: Code that uses a whole column, a bloated UsedRange, or a range extending far past the real data may process hundreds of thousands or millions of unnecessary cells.
  • Large clipboard operations: Copying and pasting very large ranges can require temporary resources in addition to the source and destination data.
  • Many open workbooks or add-ins: Excel shares its available resources among open files, add-ins, calculations, and the macro currently running.
  • Workbook bloat: Formatting applied to entire rows or columns, excessive conditional formatting, many shapes, and formulas across unused areas can make a workbook heavier than its visible data suggests.
  • Object-heavy code: Creating many workbooks, worksheets, charts, or other objects in a loop—and retaining references to them—can build up resource use.

Diagnose the failure before changing code

First save a copy of the workbook, then note the exact error text, the highlighted VBA line, and whether the failure is consistent. If the error occurs only after several loop iterations, record which iteration or row is being processed. If it happens at the same operation every time, check the size and shape of the input at that point. This narrows the cause more effectively than changing unrelated settings.

  1. Close other workbooks and unnecessary applications, then reopen the affected file and try the macro once. If this helps, limited available resources may contribute, although it does not prove the macro is efficient.
  2. Check whether the macro reads entire columns or uses UsedRange. Inspect the last genuinely populated row and column; formatting far below the data can make the used area unexpectedly large.
  3. Watch Excel’s memory use in Windows Task Manager while the macro runs. On Mac, use Activity Monitor. A rising value can indicate that the macro’s workload grows over time, but operating-system memory figures alone do not identify the faulty line.
  4. Try the workbook on another computer or with a smaller copy of the data. If only one workbook fails, investigate its size, formulas, objects, and possible corruption. If many workbooks fail, look at Excel’s bitness, add-ins, and the overall workload.
  5. For an add-in question on Windows, start Excel in Safe Mode by running excel /safe from the Run dialog. If the problem disappears, investigate add-ins or customizations; Safe Mode is a diagnostic, not a permanent repair.

Make the macro use less memory

The most useful change is often to limit the data brought into VBA. Find the actual last row rather than reading a whole column, process rows in manageable batches, and avoid keeping duplicate arrays when one will do. Reading a block into an array and writing a result back in a block is usually much faster than making a worksheet call for every cell, while batching prevents the array from growing without limit.

The example below sums numeric values in column A in blocks of 10,000 rows. It assumes a worksheet named Data, a header in A1, and values beginning in A2. It writes the total to B1, so change that destination if B1 contains information you need. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.

Option Explicit

Sub SumColumnInBatches()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim firstRow As Long
    Dim lastBatchRow As Long
    Dim data As Variant
    Dim i As Long
    Dim total As Double

    ' Read the source worksheet and find its last populated row in column A.
    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Read and release one block at a time instead of loading a whole column.
    For firstRow = 2 To lastRow Step 10000
        lastBatchRow = firstRow + 9999
        If lastBatchRow > lastRow Then lastBatchRow = lastRow

        data = ws.Range("A" & firstRow & ":A" & lastBatchRow).Value2
        For i = LBound(data, 1) To UBound(data, 1)
            If Not IsError(data(i, 1)) Then
                If Not IsEmpty(data(i, 1)) And IsNumeric(data(i, 1)) Then
                    total = total + CDbl(data(i, 1))
                End If
            End If
        Next i
        Erase data
    Next firstRow

    ' Store the result; choose another cell if B1 is in use.
    ws.Range("B1").Value = total
End Sub

Batch size is a trade-off, not a universal magic number. Smaller blocks use less temporary memory but may add processing overhead; the right size depends on the data and computer. Also avoid creating a second full-size array just to transform the first. If the task can be expressed as a worksheet calculation, a PivotTable, or a Power Query transformation, those may be simpler alternatives to maintaining a large VBA data structure.

Release references and reduce workbook overhead

When a macro creates objects, set object variables to Nothing when they are no longer needed, and close workbooks it opened if the workflow allows it. For example, a loop that opens many files should close each processed workbook rather than leaving all of them open until the end. Clear large arrays with Erase when they are no longer required. These steps help release references, but they cannot force Excel or Windows to instantly return every reserved memory block to the operating system; Excel may retain memory for reuse.

Review the workbook as well as the code. Remove formatting from rows and columns beyond the real data, delete unused shapes, and check for formulas or conditional formatting applied over unnecessarily large ranges. Save a copy before making structural changes. If a file behaves unusually, test a copy with Excel’s repair option when opening it, or move the actual data into a new workbook; preserve the original because repair or reconstruction can remove damaged content.

Restarting Excel is a reasonable short-term step after a failed macro because it closes the process and releases resources held by that session. It is not a substitute for fixing a repeatable allocation problem. Likewise, adding RAM may improve overall system capacity but will not remove the address-space ceiling of 32-bit Excel or prevent inefficient code from requesting an enormous allocation.

Misconceptions and fixes that can backfire

A common misconception is that “out of memory” always means the computer needs more RAM. The error can instead reflect Excel’s process limit, a large temporary allocation, a workbook with an inflated used range, or a resource leak in the macro. Another mistaken fix is to disable calculation or screen updating and assume that memory use must therefore fall. Those settings can improve speed in some macros, but they do not make a huge array smaller; calculation settings can also leave results temporarily stale if not restored carefully.

Do not blindly change the system page file, delete registry keys, or install untrusted “memory cleaner” tools to fix a VBA error. Such steps may create new problems and do not address the code or workbook that triggered the failure. Make one controlled change at a time, test on a copy, and keep a known-good version of the workbook.

Frequently Asked Questions

Why does Excel say out of memory when my computer has free RAM?
Excel may have reached the address space available to its process, especially in 32-bit Excel, or it may be unable to allocate one large contiguous block. Free physical RAM does not guarantee that a particular Excel allocation will succeed.

Will switching to 64-bit Excel fix the VBA error?
It can help if the macro needs more address space than 32-bit Excel can provide. It will not fix unbounded ranges, repeated large copies, workbook bloat, or code that continually retains objects. Check add-in and VBA compatibility first.

Can I clear Excel’s memory without restarting it?
Close unneeded workbooks, release object references, erase arrays, and close objects the macro opened. Excel may keep some allocated memory available for reuse, so restarting the application is the most reliable way to clear memory held by that Excel session.

Why does the error appear only with one workbook?
That file may contain more data, formulas, formatting, shapes, or hidden used-range rows than other files. It could also be damaged. Compare a copy with a smaller dataset or transfer the necessary content into a clean workbook to help isolate the cause.

Does increasing the VBA batch size make the macro faster?
Sometimes, because fewer read and write operations can reduce overhead, but a larger batch also requires more temporary memory. Increase it only after testing, and choose a size that completes reliably with the largest dataset you expect to process.

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.