Excel: "Not Enough Memory" Error When Defining Names

• Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c

SYMPTOMS

If you define a large number of names (over 6,000) on a worksheet or macro sheet, you may receive one of the following errors when you choose Define Name from the Formula menu:

• The Define Name dialog box does not appear and you receive the "Not enough memory" error message.

  -or-

• The Define Name dialog box is displayed for a fraction of a second and then disappears. At the same moment, you are switched to another open application, just as if you had pressed the ALT+TAB key combination.

CAUSE

This error occurs because of a limitation in the Standard Dialog Manager (SDM), which manages memory allocation for dialog boxes in Microsoft Excel. If the number of names on your worksheet is too great, the SDM runs out of memory when it tries to populate the "Names in Sheet" list box.

The number of defined names that you can have before the error begins to occur varies, but is usually between 6300 and 6800 names, depending on the complexity of the names that you have defined.

Note that the DEFINE.NAME() and DELETE.NAME() macro commands are not influenced by the amount of available memory in the SDM.

WORKAROUND

To avoid this problem, delete enough names so that the SDM can handle the remaining names. Because the Define Name dialog box is unavailable, you can delete names in two ways:

• Write a macro that uses the DELETE.NAME() command to eliminate names.

  -or-

• Copy the entire contents of the worksheet to a new worksheet. On the new worksheet, redefine only the names you need.

STATUS

This error only occurs in Microsoft Excel for Windows. Please comment this article if this behavior is seen to occur on other platforms.

REFERENCES

"User's Guide 1," pages 263-266