Number of objects in a collection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CK - 14 Apr 2008 00:50 GMT

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.

Reply to this Message

Dave Peterson - 14 Apr 2008 01:29 GMT

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

> Hi,
>
[quoted text clipped - 4 lines]
>
> Cheers.

Signature

Dave Peterson

Reply to this Message

Ron Rosenfeld - 14 Apr 2008 01:40 GMT

>I ran this in xl2003:
>
[quoted text clipped - 15 lines]
> 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

Reply to this Message

CK - 14 Apr 2008 01:54 GMT

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.

> I ran this in xl2003:
>
[quoted text clipped - 24 lines]
> >
> > Cheers.

Reply to this Message

Dave Peterson - 14 Apr 2008 02:06 GMT

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.

> Interesting. I didn't use the count method but I dragged the collection
> variable in the watches window instead.
[quoted text clipped - 34 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message