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.
|
