XL5 Err Msg: "Not Enough Memory" With Indirect Defined Names
ID: Q109209
|
The information in this article applies to:
-
Microsoft Excel for Windows, version 5.0
-
Microsoft Excel for the Macintosh, version 5.0
SUMMARY
In Microsoft Excel version 5.0, defined names are normally limited to
20 levels of indirection. If you use a defined name that exceeds this
limit, you will receive the "Not enough memory" error message.
MORE INFORMATION
A level of indirection in defined names is created when one name refers
to another name or to a cell reference.
If you have the following names defined in a workbook (Book1)
Name Refers to Level of indirection
--------------------------------------------
Test1 =Sheet1!$A$1 1
Test2 =Book1!Test1 2
Test3 =Book1!Test2 3
Test4 =Book1!Test3 4
Test5 =Book1!Test4 5
Test6 =Book1!Test5 6
Test7 =Book1!Test6 7
Test8 =Book1!Test7 8
Test9 =Book1!Test8 9
Test10 =Book1!Test9 10
Test11 =Book1!Test10 11
Test12 =Book1!Test11 12
Test13 =Book1!Test12 13
Test14 =Book1!Test13 14
Test15 =Book1!Test14 15
Test16 =Book1!Test15 16
Test17 =Book1!Test16 17
Test18 =Book1!Test17 18
Test19 =Book1!Test18 19
Test20 =Book1!Test19 20 (the limit)
Test21 =Book1!Test20 21
where each line represents one level of indirection, and if you attempt to
use a name that exceeds the 20th level of indirection, you will
receive the "Not enough memory" error message.
You may also receive this error message if you try to delete a name
that has too many levels of indirection below it. For example, if you
try to delete the name "Test1", you may receive the error message
because the name "Test21" is 20 or more levels of indirection away.
Steps to Reproduce Problem
Microsoft provides examples of Visual Basic procedures for
illustration only, without warranty either expressed or implied,
including but not limited to the implied warranties of merchantability
and/or fitness for a particular purpose. This Visual Basic procedure
is provided 'as is' and Microsoft does not guarantee that it can be
used in all situations. Microsoft does not support modifications of
this procedure to suit customer requirements for a particular purpose.
Note that a line that is preceded by an apostrophe introduces a
comment in the code--comments are provided to explain what the code is
doing at a particular point in the procedure. Note also that an
underscore character (_) indicates that code continues from one line
to the next. You can type lines that contain this character as one
logical line or you can divide the lines of code and include the line
continuation character. For more information about Visual Basic for
Applications programming style, see the "Programming Style in This
Manual" section in the "Document Conventions" section of the "Visual
Basic User's Guide."
To create an example that demonstrates what can happen if you exceed the
limit of 20 levels of indirection, do the following:
- Open a new workbook (Book1) that contains a Visual Basic module
(Module1) and a worksheet (Sheet1) and does not contain any defined
names. In the Visual Basic module, type the following:
'----------------------------------------------------------------------<BR/>
Option Explicit
Sub CreateNames()
'Dimension some variables.
Dim X As Integer
'Create the name "Test1", which refers to Sheet1!$A$1.
ActiveWorkbook.Names.Add Name:="Test1", RefersTo:="=Sheet1!$A$1"
'Iterate through the loop 20 times, creating the names "Test2"
'through "Test21".
For X = 2 To 21
'Create the name "Test(X)", which refers to the name "Test(X-1)".
'For example, Test2 refers to Test1, Test3 refers to Test2, etc.
'IMPORTANT: These two lines should be entered as one line.
ActiveWorkbook.Names.Add Name:="Test" & X, _
RefersTo:="=Book1!Test" & X - 1
Next 'loop until all done
End Sub
'----------------------------------------------------------------------
- To run the CreateNames() subroutine, position the insertion point in the
line that reads Sub CreateNames() and press the F5 key.
When you run the CreateNames subroutine, you will have 21 names defined
on Sheet1. Each name Test[X], where [X] is a number from 2 to 21, refers
to Test[X-1]. Test1 refers to Sheet1!$A$1.
- On Sheet1, enter the following values:
B1: =Test20
B2: =Test21
When you enter the formula =Test21, you will receive the "Not enough
memory" error message. This is because the name Test21 is at
the 21st level of indirection relative to Test1, to which it ultimately
refers. The "=Test20" formula works because Test20 is only at the 20th
level of indirection relative to Test1.
- From the Insert menu, choose Name, and then choose Define.
- From the Names In Workbook list, select Test1 and choose the
Delete button.
Again, you will receive the "Not enough memory" error message. The
name Test1 will not be deleted.
NOTE: If you delete the name Test21 first, you can delete Test1 because you
are then within the 20 levels of indirection limit.
Additional query words:
5.00
Keywords :
Version : 5.00
Platform : WINDOWS
Issue type :
|