Excessive Defined Names Causing Book Properties not to work properly

[Ronald Dodge]

WINXPPro, SP2
XLXP, SP3

I have one workbook with 27,144 defined names, when I go to pull up the
properties on the document, it works fine. Another workbook that is
essentially the same thing as far as how it's setup and what it does, just
the data is different, I have in it, 53,774 defined names, which when
attempting to bring up the document properties, it just hangs, RAM usage
keeps climbing up and have to end the program via the task manager.

Excel opens with base usage of 26MB
Workbook with 27,144 defined names as the only workbook open has the Excel
object using 48MB (Task Manager) and storage usage is 10.2MB
Workbook with 53,774 defined names as the only workbook open has the Excel
object using 59MB (Task Manager) and storage usage is 10.6MB
Excel open with no workbooks other than maybe Book#.xls that Excel opens
automatically uses 26MB (Task Manager)

This is just one more thing that has me questioning if I really have ran
into a limitation of the number of defined names one can use within Excel
(almost all of these names are being used for ranges)

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Guest]

hi
in xl help, type the word specifications.
per specs, number of names in a workbook is limit only by available resources.
i have never tried to put that may range names in a workbook so i have no
experience on how the workbook would react.

regards
FSt1

 

[Guest]

Maybe Ronald has run into a limitation on his available resources.

 

[Peter T]

See Charles Williams' comment in this post

http://tinyurl.com/35pjqe

Regards,
Peter T

 

[Ronald Dodge]

I know what it states in the specifications and I have done various searches
on the web with very little data to go by on this names issue that I have
ran into. Specifications is one of the first places I go to before I even
start programming as it has to be taken into account in the planning
process. However, based on the symptoms that I been seeing, it looks like
when you reach about 32k names (if truely a binary format number thing, then
it would be either 32767 or 32768 depending on how it's setup), that is when
things starts going bonkers, which I have 512MBs of RAM on the system. It
also doesn't matter if just a minor few things are open or if I have a lot
of things opened, I still get the same result.

Given the symptoms and what I have noticed, it seems as though I'm running
into MS's arbutary limitations again, but only this time with using defined
names.

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Ronald Dodge]

The post there mainly only mentions about slowness, which I can see that
when dealing with calculations cause of the fact that it's having to go
through the list of names, though if it stores those names into memory, it
doesn't have to go through the list so many times. I don't know which way
it works in Excel as that's dependent on how it's programmed in the Excel
program itself. However, if I had to take a gander at how it's being done
in Excel, I would tend to think it's more so storing to memory and then
refering to that memory back after that. The reason being, when the
workbooks with the list of range names in them are being processed, there
doesn't seem to be any real difference in the amount of time it takes for
those workbooks to be processed, but yet, at the same time, the amount of
RAM usage does go up.

For the workbook that has the 53,774 range names in it, The Excel memory
usage started at 59MB, and it had gotten up to 95MB RAM usage prior to me
forcing the Excel.exe to end via the "End Process" under the list of
processes. It went up after I went to take a look at the document
properties (File>Properties) and Excel never did show the dialog box.

Sincerely,

Ronald R. Dodge, Jr.