XL: Err Msg: "Not Enough Memory" When Free Memory Sufficient

The information in this article applies to:

• Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a

SUMMARY

If you are using Microsoft Excel, you may receive the error message, "Not Enough Memory," when you open a file or enter data into a sheet while other large files are open. This error message may occur even when there appears to be sufficient free memory to open the additional file or enter additional data. If this is the case, Microsoft Excel may be exceeding one of its internally set memory limits.

To check your available memory, click About Microsoft Excel on the Help menu in Microsoft Excel for Windows or on the Apple menu in Microsoft Excel for the Macintosh.

MORE INFORMATION

In Microsoft Excel, there are two categories of internal memory limits: the cell table and the heap space. If you reach a limit for one of these categories, you will receive the "Not Enough Memory" error message.

Cell Table

In this category, there are two internally set limits. The first is a limit on the number of rows that can contain data in each instance of Microsoft Excel. The second is a limit on the memory allocated to each cell.

The cumulative number of rows on all open worksheets that can contain data is approximately 37,120. For every 16 rows of data, there is one selector allocated which essentially keeps track of those rows. For each Microsoft Excel session, there is a limit of approximately 2,320 selectors which indicates a maximum of approximately 37,120 rows of data.

For example, if you open two sheets that each contain 16,384 (the maximum per sheet) rows of data, and you attempt to open a third sheet with 5,000 rows of data, you will receive the "Not Enough Memory" error message. If you close one of the sheets containing 16,384 rows and then open the sheet containing 5,000 rows, it will open successfully. Alternatively, you can keep the larger two sheets open if you run a second instance of Microsoft Excel; you can then open the sheet with 5,000 rows. (If you are using Microsoft Excel for the Macintosh, you can only run a second instance of Microsoft Excel if you have two separate copies of the application on your hard drive. You can then run each application once.)

The second internally set limit affects individual cells rather than the length of the whole cell table. For each cell used in a worksheet, an 8-byte memory block is allocated. If the information in a cell is too complex to be contained in 8 bytes, the cell table contains a pointer to a block in the second category of memory, the Heap Space.

Heap Space

In addition to containing more complex formulas, the heap space contains information for the worksheet such as formatting, borders, fonts, defined names, and other non-cell type data such as embedded objects and pictures. Microsoft Excel can use up to 16 megabytes (MB) of available memory for the heap space.

If your machine has more than 16 MB of available RAM, and if your open worksheets contain a large number of complex formulas (thereby exceeding the cell memory block limit), a large amount of formatting and/or non-cell data such as graphic and embedded objects, you are more likely to exhaust the 16 MB heap space allocation before you reach the row limit. In this case, reduce the number of fonts used in the worksheet, simplify formatting, reduce the number of graphic or embedded objects, and use array formulas to fill similar formulas into adjacent cells.