Runtime Error 7: “Out of memory,” explained and fixed

Error 7, “Out of memory”, is VBA's most misleading message. It fires when VBA cannot allocate something, an array, a string, a stack frame, but on a modern machine with free RAM the real cause is usually a loop that never stops growing something, code running in a 32-bit host with a hard 2 GB ceiling, or the fixed limits inside VBA itself. Treat it as a code bug first and a hardware problem last.

For people who just run the program

  1. Note how long the macro ran before failing. Failing after minutes of work points at something growing without limit; failing instantly points at one huge allocation.
  2. Close other workbooks and documents, then retry once. If it only fails alongside large files, the host application's memory ceiling is involved.
  3. Check whether the file is unusually large or the data range grew. A macro reading a used range that quietly expanded to a million rows is the classic trigger.
  4. Report the error with the file size and what the macro was doing. “Out of memory” alone sends the maintainer nowhere; the step it reached is the useful part.

For people who write the code

  1. Bound every loop that appends. A ReDim Preserve inside a loop with a condition that never becomes false grows the array until allocation fails. Log the counter and the array size when debugging.
  2. Read ranges in one shot, sized to real data. Range("A1").CurrentRegion.Value is fine; reading Columns("A:Z").Value allocates a million-row array for a ten-row sheet.
    3. Dim data As Variant
data = ws.Range("A1").CurrentRegion.Value   ' sized to the data
' not: data = ws.UsedRange.Value            ' UsedRange can be huge
  3. Watch string concatenation in loops. Repeatedly appending to one giant string forces constant reallocation and can exhaust memory on long text. Collect parts in an array and Join once at the end.
  4. Remember the 32-bit ceiling. 32-bit Excel cannot use more than about 2 GB no matter how much RAM is installed. If the workload is genuinely that big, the real fix is a 64-bit host or moving the heavy step out of VBA.
Adding RAM does not fix error 7 in a 32-bit Office install. The process has a fixed address-space limit it cannot cross. Check the host's bitness (File, Account, About) before anyone orders hardware.

If it still happens

If small inputs also fail instantly, suspect a corrupt workbook or a broken add-in rather than allocation size: test the same macro in a fresh blank file, and start the host with add-ins disabled. A failure that follows the macro into a blank file is in the code; a failure that stays with one file is in the file.

Common questions

Does error 7 mean the computer needs more RAM? Almost never. It means VBA's allocation failed inside the host process. The usual causes are a runaway loop, an oversized array or string, or a 32-bit host at its address-space limit, all fixable in code or configuration.

Why does error 7 appear only on large files? Because allocation size scales with the data. Code that loads an entire range or builds one giant string works on small inputs and crosses the limit only when the data grows.

Related errors

Runtime Error 14 · Runtime Error 28 · Runtime Error 6