Re: Exceeding 65K of defined names within workbook causes workbook to go into repair mode when it is opened

The funny thing about the written documentation that you stated, the system
that I ran into the issue with is the system that I got as of June 1999 with
no sort of upgrades to it on the hardware side ever since, so that
documentation must be prior to Excel 97, if that is the case. This also
held true for the system that the company bought back in 1997. The only
hardware upgrade it had was going from 128MB of RAM to 384MB of RAM. That
system still has just a 6GB HD while the other system only has 20GB HD size.
Compare that to today's standards, that's very small for an HD, but yet back
then, it was pretty high up.

As you may have noticed, I generally keep working and testing, documenting
until I find something that's seems to be statistically much more likely to
be something other than how it's stated and I point those things out. I
even been pointed to posts stating that things in Excel tends to run slow
with 5+ range names, and I haven't even found that to be the case with 62k+
range names.

Of anyone out there, I'm probably one of the very few people (on a relative
basis) that really push systems to their limits that doesn't even get into
graphics, audio, video, or design type stuff. I more or less heavily
process data with other things added to the files for readability purposes.
For me, it's the data and formats (Only cause Excel burns off so much memory
for formats, otherwise, it would only be data intensive for memory usage)
that gets to be heavy memory usage and CPU intensive for the large amount of
data that gets processed, though still probably minor compared to graphics
department. On the other hand, it still takes about an hour per day just to
run the reports and process the data. One reason why I am a heavy data
processor, I'm a real stickler about accuracy and precision cause when I
haven't been in the past, it came back and haunted me in some form. Along
those same lines, I don't like doing things manually, especially with the
days when I worked at the IRS doing nothing but data entry only to get the
early stages of CTS, so I have since then sharply enhanced my computer
skills and automated a lot of the tasks.

Just with so many changes that took place last fall, I had to go into a full
scale audit process after I got things back into alignment on a temporary
basis. I have had to since then put in fixes, just so as when those types
of changes takes place again, those numbers aren't nearly as likely to go
out of alignment again. That's what brought on me using range names, but I
see I will have to get creative once again and determine another route to
address these issues. I still will end up using range names, but probably
much less intensive given this 4 byte addressing limit for collection
objects.

Ronald R. Dodge, Jr.
Master MOUS 2000

"Jon Peltier" <jonxlmvpNO@xxxxxxxxxxxxxxxxxxx> wrote in message
news:uqk$RJzlHHA.1776@xxxxxxxxxxxxxxxxxxxxxxx
Well, I obviously didn't read past the first couple paragraphs in your
message. If I had done so, I may not have answered, because I would have
noticed you'd already learned what I then posted.

I understand your frustration with the stated limits. Maybe when the
limitations were first written down, available memory only allowed well
under 65k names, so whoever wrote the specs didn't consider the 4 byte
addresses.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Ronald Dodge" <ronald.dodge@xxxxxxxxxx> wrote in message
news:uClqi5ylHHA.4132@xxxxxxxxxxxxxxxxxxxxxxx
Uhmmm, I know this and I have stated this in the message, but it's not
working out that way. Regardless what the specifications says, the
testings are stating otherwise. Why do you think I stated so much about
the RAM and SWAP file availability and usage in the last paragraph of my
message?

Sorry if this seems harsh, but it seems to be rather frustrating when you
are doing things within written specifications, but things aren't working
out for you. I can pretty much do everything else within the files, but
this names issue has surfaced on me, and this only happens when I exceed
that 65536 limit. The file is a bit slow to open with the 62k names, but
I can still do so much other stuff even with the 62k names in it. It's
not even close to breaching the memory limitation with the 62k names.

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

"Jon Peltier" <jonxlmvpNO@xxxxxxxxxxxxxxxxxxx> wrote in message
news:e9NuIwylHHA.3656@xxxxxxxxxxxxxxxxxxxxxxx
Type 'Excel limits' in the help lookup box. Under "Work*** and
workbook specifications" you'll see:

Names in a workbook: Limited by available memory

I would say that 65k names is a bit excessive, but I've never had more
than a couple thousand.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Ronald Dodge" <ronald.dodge@xxxxxxxxxx> wrote in message
news:uIYwL1xlHHA.960@xxxxxxxxxxxxxxxxxxxxxxx
Okay, I'm at it again, but only with further testing on this defined
names issue. I have seen different posts stating that with excessive
number of range names, it causes the files to process significantly
slower, which I have seen no such symptom within my own files.
However, I have noticed a certain pattern and now I must raise another
question that I seem to have noticed?

What is the maximum number of items any one collection can have without
causing issues?

Why do I ask this?

It seems to me that the collection is using 4 bytes as addresses to get
an index number. If this is the case, then when an item exceeds 65536
different items, you no longer have a unique ID number, which is what
seems to be causing me these issues that I seem to have ran into. This
65536 number also made me think of rows instantly, which then makes me
wonder if this is also the reason why MS has made 65536 as the maximum
number of rows one can have within a single work***.

What had me think in this sort of direction?

Remember the early Windows 95 edition, and you could only have up to
2GB of HD Space on a per letter drive basis, thus if you had a larger
drive size, it had to be partitioned into multiple partitions to be
able to use the space on it? Well that issue related back to the fact
that the original FAT filing system could only handle up to the 2GB of
space per drive/partition for addressing purposes.

In this case, if the Names collection has more than the 4 bytes of
indexed values, the workbook goes into repair mode when it is opened,
which then makes the workbook almost useless as it stripes a lot of
stuff from it. While the application specifications says it's limited
to the amount of RAM on the system, I'm no where near reaching this
limit. Not only that, but supposedly, Excel 2002 should be able to
handle up to about 160MB of RAM usage, which according to the task
manager, when I ran into this issue, it was only using 67MB of RAM on a
system that has 512MB of RAM. According to the Task Manager, I still
have 130MB of RAM usage currently not in use and available to use along
with 768MB of swap file available to use for a total working memory
usage availability being 1.28GB, which only up to 853MB of that has
been used.

Now that I seemed to have found the real limit of defined names, it's
now time to start thinking in other directions to address the issues
that I been facing. I would dare to venture to claim that the
specification limit of defined names is in deed 65536 just like the
maximum number of rows is 65536. The "Cells" object has a 2 byte by a
1 byte index method, similar to a 2 dimensional array. To help me find
out the issues, I used the watch window to look at the count value on
the Names collection object and after several testings and watching
that value along with seeing other things, it's what has had me draw
that conclusion.
--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000









.