Limitation to Collection Object

[Keith]

I am using excel 2000 and trying to add 500 elements to a collection.

The collection will only add 256 elements. Is there a limit to the numer of
elements?

What about in excel 2003?

 

[Gary Keramidas]

not sure if this is relevant, but this is excel 2003

Worksheet arrays Limited by available memory. Also, arrays cannot refer to
entire columns. For example, an array cannot refer to the entire column C:C or
to the range C1:C65536. However, an array can refer to the range C165535
because the range is one row short of the maximum worksheet size and does not
include the entire C or D column


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


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

 

[Keith]

thanks for the post, but still does not answer the core question:

I beleive the collection only will hold 256.

an array is different from a collection.

 

[Leith Ross]

thanks for the post, but still does not answer the core question:

I beleive the collection only will hold 256.

an array is different from a collection.

Hello Keith,

If you are trying to add items to a system collection object, you may
have reached that collection object's limit. You failed to mention the
object you are working with. Gary is right in that a collection is
limited only by available memory. The Item and Count properties are
both Long integers which means the maximum number or items in the
collection is 16.7 million.

Sincerely,
Leith Ross

 

[Peter T]

In case - don't be misled by the 256 limit you can see in Locals or in a
Watch.

Dim i As Long
Dim col As Collection

Set col = New Collection
For i = 1 To 20000
col.Add i, CStr(i)
Next

MsgBox col("20000")

Regards,
Peter T