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


CK wrote:

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