Maximum number of named ranges?

[JNW]

Does anyone know the maximum number of named ranges allowed in xl03 and xl07?

Thanks!

 

[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???

 

[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)

 

[Gary Keramidas]

Names in a workbook Limited by available memory


http://office.microsoft.com/en-us/assistance/HP051992911033.aspx

 

[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

 

[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

 

[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.

 

[Dean Meyer]

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

 

[Gary Keramidas]

http://office.microsoft.com/en-us/excel/HP051992911033.aspx

 

[Socko]

The maximum Named range in a workbook is limited by available memory.

Please Refer: http://office.microsoft.com/en-us/excel/HP051992911033.aspx


I hope this helps.

Selva V Pasupathy
For more on Excel, VBA, and other resources
Please visit: http://socko.wordpress.com

 

[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)

 

[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

 

[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

 

[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

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

 

[Peter T]

Hi Charles,

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

Regards,
Peter T