Defined Names Exceeding 65536

[Ronald Dodge]

Excel 2002
Windows XP, PS2

Yeah yeah yeah, according to specifications, it's limited to memory, but
according to the testings that I have done, that's simply isn't the case.

As long as the number of names in the workbook doesn't exceed the 65536
limit, I can do anything and everything in the workbook, but the moment I
exceed that limit, save it, and close it out, when I open the file again, it
goes straight into repair mode, and then everything but the data and
formulas are striped from the file. The thing about it, it's not even close
to reaching either the systems limits (hardware side) or the applications
limits (the software arbituary limits set in Excel) as far as memory is
concerned.

Based on other things that I have noticed, I'm thinking that the collection
objects can only handle index values that are supported by a 4 byte variable
for addressing purposes.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[Jan Karel Pieterse]

Hi Ronald,

As long as the number of names in the workbook doesn't exceed the 65536
limit, I can do anything and everything in the workbook, but the moment I
exceed that limit, save it, and close it out, when I open the file again, it
goes straight into repair mode, and then everything but the data and
formulas are striped from the file. The thing about it, it's not even close
to reaching either the systems limits (hardware side) or the applications
limits (the software arbituary limits set in Excel) as far as memory is
concerned.

Interesting fact.
But what would you need 65536 defined names for? I have seen workbooks with
many range names, as much as 8000 even. But 65,000? Never. I can imagine the
file size skyrocket with such numbers too.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Ronald Dodge]

Actually, the file size didn't jump up very much. It was less than 1MB for
about 60k range names, but then range names for the most part were kept
short too. As far as that's concerned, can't be any worse than formulas
within cells are concerned. I was actually using range names to replace
some of the formulas indirect calculations. The workbook was going to have
something like 200,000 range names, but obviously, that's not going to work
out. There's a lot of different things dealing with just one machine center
over the course of the current year, and I'm using VBA to control a lot of
this stuff.

Due to other changes that took place, I had to temporarily get the numbers
back into alignment, but then for the long term, think of a way to keep the
numbers in alignment, and range names is what came to mind after performing
an audit process on the production reporting system. However, due to this 2
byte addressing issue used for indexing purposes on the Names collection
object, it has caused me to go in a somewhat different direction. Still
using range names, but not nearly as extensively. I know I said 4 bytes
earlier, but that was my mistake, it's only 2 bytes, not 4 bytes. If it was
4 bytes, it would have been able to handle over 4 billion range names, which
obviously, I wouldn't even attempt to exceed that for a single workbook
given other memory limitations.

Once again, I come across another undocumented limitation/error. I seem to
be constantly pushing systems/programs to their limits, thus one big reason
why I turn to written specifications from a planning stand point of view not
to mention also having to think in terms of efficiencies. There's 3 main
things I strive for though in no particular order, accuracy, user
friendliness, and efficiency. These 3 different things can at times be at
odds with each other.

Ronald R. Dodge, Jr.
Master MOUS 2000