Number of objects in a collection

[CK]

Hi,

Does anyone know the maximun number of objects a collection variable can
hold (in Excel 2003 and 2007)? I tested it in Excel 2003 and it seems to me
that there is an upper limit of 256 objects per collection variable but I
still want to confirm.

Cheers.

 

[Dave Peterson]

I ran this in xl2003:

Option Explicit
Sub testme()
Dim myColl As Collection
Dim iCtr As Long

Set myColl = New Collection
For iCtr = 1 To 1000000
myColl.Add Item:="A" & Format(iCtr, "000000")
Next iCtr

Debug.Print myColl.Count

End Sub

And got:
1000000
in the immediate window

 

[Ron Rosenfeld]

I ran this in xl2003:

Option Explicit
Sub testme()
Dim myColl As Collection
Dim iCtr As Long

Set myColl = New Collection
For iCtr = 1 To 1000000
myColl.Add Item:="A" & Format(iCtr, "000000")
Next iCtr

Debug.Print myColl.Count

End Sub

And got:
1000000
in the immediate window

Interesting.

I ran this in Excel 2007 and got the same result.

But when I "Watch" myColl, only items 1 to 256 are displayed in the Watches
window.

However, the other items are there, as evidenced by this in the immediate
window (with the macro stopped and End Sub):


?mycoll(256)
A000256
?mycoll(257)
A000257
?mycoll(1234)
A001234
?mycoll(123456)
A123456
?mycoll(999999)
A999999
?mycoll(1000000)
A1000000

--ron

 

[CK]

Interesting. I didn't use the count method but I dragged the collection
variable in the watches window instead.

For some reasons, only 256 items of the collection variable are shown in the
watches window.

 

[Dave Peterson]

I wasn't smart enough to look in the watch window--but Ron was.

I don't have any idea why collections are treated like this.