Excel Memory Leaks

Memory Leaks

Memory Leaks occur when a program (Excel, VBA…) obtains some memory but fails to hand it back to the memory manager.
Usually you only notice this if you monitor memory usage closely, or you run out of memory!
Most of the time you can reclaim the memory by saving your workbook, closing and then restarting Excel. Sometimes you have to restart Windows.

Sometimes you can delay the point at which you run out of memory by increasing the size of your Swapfile.

You can easily monitor memory leaks with Windows NT, 2000, XP, 7, 8 and 10 using the Windows Task Manager.

Here is a partial list of things that can cause memory leaks:

  • Page Setup in Win95 & Win98 & Win ME (GDI problem) - see MSKB Q192869
  • Printing using HP Printers - see MSKB Q165985 and Q218864
  • Querying an open Excel worksheet using ADO - see MSKB Q319998
  • Replacing external link formulae
  • Some external data retrieval actions, particularly repeatedly reading text files. Excel seems to store only unique strings rather than multiple occurrences of the same string, but does not seem to remove a unique string when it is no longer in use.
  • Controls embedded on worksheets - see MSKB Q238570
  • User Defined Functions, particularly when they take input parameters from another sheet and return a string. there are some fixes for these problems in Excel 97 SR2 and Excel 2000 SP2 - see MSKB Q265023
  • Inserting and deleting graphic objects such as bitmaps or JPEGs.
  • Not destroying objects in VBA by setting the object variables to nothing in the correct sequence so that you avoid "orphaned" objects. In theory this is not neccessary, but sometimes problems occur when this is not done. Destroy in inside-out container sequence, for example Range then Worksheet then Workbook.

Excel’s UNDO feature does not have a memory leak but will use up memory that could otherwise be used for formulae.
SAVE removes the UNDO information and frees the memory.

You can reduce the number of Undo levels by editing the Registry. This will tend to reduce the memory used by UNDO: see Q211922 - XL2000: How to Modify the Number of Undo Levels.

On large spreadsheets you should be careful about editing very large numbers of formulae in a single step: edit in small steps with intermediate saves.

© 2001-2020 Decision Models  Legal Disclaimer Privacy Statement E-Mail Webmaster