Is the Name Object limited to 256 members?

Bocephus

I need to add the Names objects in my workbook to a Collection. However, it seems that there is never more than the first 256 objects in the MyWorkbook.Names list. I have maybe 2500 names that I need to work with. How do I expand the Names object's capability?


Dim MyWorkbook As Workbook
...
Dim DefinedNamesCollection As New Collection
...

For Each XName In MyWorkbook.Names
Set DefinedName = XName
DefinedNamesCollection.Add DefinedName, DefinedName.NameLocal
Next XName

Nov 21 '06 #1

Killer42

I thought that Excel only allowed 255 or 256 worksheets in a workbook. Was I mistaken?

Nov 21 '06 #2

AricC

I think with the later versions of Excel the workbook limit is based on memory not a specific number here are Microsofts spec's.

Nov 21 '06 #3

AricC

Sorry didn't read the initial post. Not sure if this applies but the specs say the names in a workbook are limited by memory. That's Excel 2003 though.

Nov 21 '06 #4

Killer42

I think with the later versions of Excel the workbook limit is based on memory not a specific number here are Microsofts spec's.

Ah yes, I see. "Limited by available memory". Thanks for that, I'll bookmark that link.

So I guess the followup questions then become fairly obvious

• What version of Excel?
• How much memory is available?
• Why would someone have 2500 worksheets in a workbook? Wow!

Nov 21 '06 #5