Microsoft KB Archive/104696

{|
 * width="100%"|

Excel: "Not Enough Memory" Error When Defining Names

 * }

-

The information in this article applies to:


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