Range Names Limitation

[microsoft.com]

WXP, SP2
XL2K2, SP3

What is the maximum number of range names one can have within a file without
running into issues to the point that Excel decides to say that the file has
been corrupted and goes into repair mode?

According to the specification help file, the only thing to limit Excel with
range names is the amount of memory, which I know I still have plenty of RAM
for the range names that were added to the various workbooks. The HD isn't
that full either as far as extended memory is concerned.

The first wave of 3 waves dealing with range names, the workbooks only
showed as being at a size between 8MB and 10MB, even after saving, which
after the first wave, it has something like 26k range names in it. The
second wave and the third wave both added another 26k or so of range names
each, which opened fine after the second wave, but then claims the workbooks
are damaged after the 3rd wave when you try to reopen those files. The
files now shows a size of between 11MB and 13MB, so it's still well below
the 160MB of MS's arbituary RAM usage limitation.

What brought on the need to put in range names?

With the changes that took place, it took the numbers out of alignment cause
cell refences within code doesn't adjust like cell references within
formulas does. Guess to overcome this issue, I will just have to name rows
and columns, then use long variables within VBA to get around both issues
(cell references not adjusted within VBA when rows/columns are
inserted/deleted and apparently to avoid reaching the maximum number of
range names allowed within a single workbook that seems to be somewhere
between 52k and 78k).

Sincerely,

Ronald R. Dodge, Jr.

 

[Norman Jones]

Hi Ronald,

'-----------------------
WXP, SP2
XL2K2, SP3

What is the maximum number of range names one can have within a file without
running into issues to the point that Excel decides to say that the file has
been corrupted and goes into repair mode?

According to the specification help file, the only thing to limit Excel with
range names is the amount of memory, which I know I still have plenty of RAM
for the range names that were added to the various workbooks. The HD isn't
that full either as far as extended memory is concerned.

The first wave of 3 waves dealing with range names, the workbooks only
showed as being at a size between 8MB and 10MB, even after saving, which
after the first wave, it has something like 26k range names in it. The
second wave and the third wave both added another 26k or so of range names
each, which opened fine after the second wave, but then claims the workbooks
are damaged after the 3rd wave when you try to reopen those files. The
files now shows a size of between 11MB and 13MB, so it's still well below
the 160MB of MS's arbituary RAM usage limitation.

What brought on the need to put in range names?

With the changes that took place, it took the numbers out of alignment cause
cell refences within code doesn't adjust like cell references within
formulas does. Guess to overcome this issue, I will just have to name rows
and columns, then use long variables within VBA to get around both issues
(cell references not adjusted within VBA when rows/columns are
inserted/deleted and apparently to avoid reaching the maximum number of
range names allowed within a single workbook that seems to be somewhere
between 52k and 78k).

Sincerely,

Ronald R. Dodge, Jr.

'-----------------------

You have inadvertently arrived at the Italian language
Excel NG. I suspect that you would be better served
by the corresponding English language Excel pragramming
group:

microsoft.excel.programming

However, as you have seen in the Specification limits file,
the number of names is limited only by memory.

I have, on occaision used vast numbers of names without
experiencing any discernable problems.

I would suspect, therefore, that the source of your problem
may lie elsewhere,

 

[Norman Jones]

Hi Ronald

Please ignore my response - a case of crossed wires
and two PC's!

 

[Ronald Dodge]

No problem. I also had to modify the name within the newsgroup account
(where it shows as "microsoft.com" as it being from them, but now that's
been modified to reflect my name.) as the system that I'm working on had
been totally reimaged, which means I have had to go through and reset
everything just about. No fun to have to go through all of the settings and
everything else to get things back to normal, which was as a result of the
DS patch that wasn't marked as for XP only (as provided from one of our IT
guys who claimed it was marked as such, but our local IT guy confirmed it
wasn't marked as such) and I was working on a W2K Pro at the time, thus why
I had to get the system completely reimaged. If you try to use the DS patch
that's meant for WXP Pro, and used it on W2K Pro, the system will go into a
constant state of rebooting itself and never actually get to the login
screen.

Sincerely,

Ronald R. Dodge, Jr.

 

[Ronald Dodge]

I only started doing this after the system was completely reimaged, and this
morning, I realized that one of the things that I had to do was to take the
number of UNDO's in the registry from the default 16 down to 4. I
completely forgot about this aspect as I have had the system for just about
9 years now, and I had to deal with that very issue back then when it stated
that my resources were low even though everything was being ran through VBA.
Why Excel seems to retain the information when you copy and paste large
amounts of data, but yet, it doesn't allow you to undo your changes cause it
was ran through VBA (By default anyhow), that doesn't seem to make sense to
me. Had to put in the registry setting when I was under W2KPRO, XL2K;
W2KPRO, XLXP; and now WXPPRO, XLXP.

We shall see if this resolves the issue, which I suspect it will.

Sincerely,

Ronald R. Dodge, Jr.

 

[Ronald Dodge]

Well the undo registry hack didn't seem to make a difference in this issue.

With about 1/4 of the range names put into the 12 different machine center
files, both before and after putting in the registry hack for the Undo
Feature, I got the low resource error message as it attempted to open the
11th file (Note: The first 10 files has already been opened, processed, and
closed up to this point). Even though each machine center file has
something like 31k range names, I also got rid of a little more than 30k
"SUMIF" functions thinking that was the culprit. There was nothing changed
on the VBA side. I initially put in the "SUMIF" function as a short-term
fix, and that's cause I had to get my numbers back into alignment after the
other changes took place as what ended up happening with regards to the lean
manufacturing team now that our parent company is pushing more things unto
us. However, I don't like using the SUMIF function as a long-term solution
cause it's a huge memory and CPU hog. Anyhow, with these adjustments that I
put in (added range names and converted the SUMIF function to formulas
refering to range names, instead of it getting better (reduced processing
time), it got worse (getting the low resource error message). With the
SUMIF functions, at least I could have all of the 12 files and other files
processed all the way through the reporting system in one go, but with the
1/4 of the needed range names, the process had to be split up into 2
different passes to get all the way through the production reporting system.

Process via the VBA code is to open a set of summary files, open the first
individual machine center file, open it's raw data file (that file only
retains up to the last 45 days of data), update the raw data into the
individual machine center file, convert all data that's older than the
previous 2 weeks as of the last date of the previous fiscal week (Monday
through Sunday is our fiscal week), close out the raw data file, then
calculate the data through. After all of the data has been calculated
through and processed via the VBA code, the individual machine center file
is closed out and the next file is then opened up as specified within the
control file.

Here's the weird thing about it, if I setup in my control file to process
just the first 8 files of the 12, everything works as expected, and then I
have that same control file then process the remaining 4 files, and then
also process other production files, though not tied to machine centers, it
process all of those files just fine too provided everything else is working
properly as expected.

Sincerely,

Ronald R. Dodge, Jr.

 

[Peter T]

I haven't read the thread but this caught my eye

Even though each machine center file has
something like 31k range names

FYI, Charles Williams answered this question earlier this year -

Q. "is there a limit to the number of named cells or named ranges you can
create?"

A. "there is no real limit that I know of, but things can get a bit slow if
you
get above 5-10000 names."

his site www.DecisionModels.com

Regards,
Peter T

 

[Ronald Dodge]

Well in this situation, the files aren't being processed any slower, but
rather resources seem to be eaten up at a much faster rate than what would
have been expected, which based on the symptoms, I will have to look at my
object variables within VBA.