Maximum number of named ranges?

J

Jim Thomlinson

More than you could ever reasonably hope to manage... That is more of an
editorial than an answer but I can not think of a circumstance where I would
run into the limit. What are you up to that you want to know the limit???
 
J

JNW

~500 right now, but could envision hitting upwards of 5k. I figure I'm more
than safe in 07 since they've all but done away with most memory limitations.
I'm more worried about xl03.

Along the same lines, any known speed issues with named ranges? Is is
faster/slower? (code and worksheet references)
 
S

ssGuru

I too would like to see some discussion concerning speed comparisons
between using named ranges and specified cell ranges.
I use a lot of named ranges in all the Excel applications I develop to
make things easier to debug visually and to make a bit of "human
sense" of it all.

Dennis
 
C

Charles Williams

Since named ranges involve Excel doing more work they are slower than direct
references.

I have used workbooks with more than 50K names (xl97-xl07), but I would not
recommend it. Personally I would feel comfortable with up to 5K names and
with that kind of volume you probably wont hit any speed problem unless they
are all dynamic ranges or other named formulae.

Presumably you are using our Name Manager ...
http://www.decisionmodels.com/downloads.htm

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm
 
J

JNW

Thanks Charles

I haven't used NameManager yet, but I think I'll start. Looks useful.

I'm using the named ranges for range that I have to refer to in the code.
The client loves to add columns and rows and generally mess with the format.
Using the names helps me not have to update the code everytime they want a
structure change. Any other methods out there that I have overlooked?

Thanks again.
 
D

Dean Meyer

I'm hitting a limit as well. See the posting: Limit (not memory) consumed
by Range Names, UDF calls, VB code, st
 
C

Chip Pearson

Just out of curiosity, how many names do you have?

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
C

Charles Williams

I don't know what the limit actually is, but I have seen useable workbooks
with over 50000 Defined Names

(personally I would not recommend using this many ...)

There used to be a limit on the depth of recursion allowed (names referring
to names referring to names ...),
not sure if this has been extended in Excel 2007.

Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com
 
P

Peter T

I did check a while ago following protestations from someone in this ng and
for me there was absolute limit of 64k names (ie 65,536).

I don't quite recall if that was how many could be saved rather than
created, either way that was the limit.

Regards,
Peter T
 
C

Charles Williams

Hi Peter,

Sounds reasonable to me, I will have to to try to break the 64K barrier!

Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

Peter T said:
I did check a while ago following protestations from someone in this ng and
for me there was absolute limit of 64k names (ie 65,536).

I don't quite recall if that was how many could be saved rather than
created, either way that was the limit.

Regards,
Peter T
Click to expand...
 
P

Peter T

Hi Charles,

Great, barriers are there to be broken <g>
Might take a while to make 64k+ names.

Regards,
Peter T
 
Top