i can't seem to remember, can a collection only hold 256 elements?

--

Gary Keramidas
Excel 2003

Not to my knowledge. I have larger collections working, not sure what is or
controls the upper limit?

--

Regards,
Nigel
(E-Mail Removed)



"Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
news:(E-Mail Removed)...
>i can't seem to remember, can a collection only hold 256 elements?
>
> --
>
> Gary Keramidas
> Excel 2003
>
>

i went through a range of 20k+ records to add unique items to a collection
and it always stopped at 256. there are 322 in the list. i just used an
advanced filter for now, to copy the unique values and add them to an array.

so, it doesn't really matter, i was just curious and i thought i had used
collections with more than 256 elements, but wasn't sure.

--

Gary Keramidas
Excel 2003


"Nigel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Not to my knowledge. I have larger collections working, not sure what is
> or controls the upper limit?
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
> "Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
> news:(E-Mail Removed)...
>>i can't seem to remember, can a collection only hold 256 elements?
>>
>> --
>>
>> Gary Keramidas
>> Excel 2003
>>
>>
>

Here's a macro that has no problem adding 64K items to a collection although
it takes a minute or so to run. Perhaps your dataset had 256 unique values
and no more, so it was capping there. I commented out the key assignment in
the test macro, but it would work uncommented too since all the values
generated below are unique.

Sub CollectionTester()
Dim coll As Collection, n As Long
Dim cell As Range
' Create a list of values all the way down col A
For Each cell In Range("A1:A65536")
cell.Value = cell.Address(0, 0)
Next
' Add to the collection every value in the list
Set coll = New Collection
For Each cell In Range("A1:A65536")
coll.Add cell.Value ', cell.Value ' Optional
Next
' Loop through the collection and
' put the values in column 2
For n = 1 To coll.Count
Cells(n, 2).Value = coll(n)
Next
Set coll = Nothing
End Sub


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
news:%(E-Mail Removed)...
>i went through a range of 20k+ records to add unique items to a collection
>and it always stopped at 256. there are 322 in the list. i just used an
>advanced filter for now, to copy the unique values and add them to an
>array.
>
> so, it doesn't really matter, i was just curious and i thought i had used
> collections with more than 256 elements, but wasn't sure.
>
> --
>
> Gary Keramidas
> Excel 2003
>
>
> "Nigel" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Not to my knowledge. I have larger collections working, not sure what is
>> or controls the upper limit?
>>
>> --
>>
>> Regards,
>> Nigel
>> (E-Mail Removed)
>>
>>
>>
>> "Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
>> news:(E-Mail Removed)...
>>>i can't seem to remember, can a collection only hold 256 elements?
>>>
>>> --
>>>
>>> Gary Keramidas
>>> Excel 2003
>>>
>>>
>>
>

i see where my confusion set it, in the locals window all it displayed was
256 items, when in actuality, all 322 items are in the collection.

--

Gary Keramidas
Excel 2003


"Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
news:(E-Mail Removed)...
>i can't seem to remember, can a collection only hold 256 elements?
>
> --
>
> Gary Keramidas
> Excel 2003
>
>

It is not "production" code. It's a test macro to prove that the collection
loads 65536 items.

Of course it can be written more robustly. That's not the point.

--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"Simon Lloyd" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> This might be a little picky but you should shorten the range to the
> used range in coulmn A rather than test every cell, also doing it like
> below allows forward compatability
>
>
> Code:
> --------------------
> Sub CollectionTester()
> Dim coll As Collection, n As Long
> Dim cell As Range
> ' Create a list of values all the way down col A
> For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
> cell.Value = cell.Address(0, 0)
> Next
> ' Add to the collection every value in the list
> Set coll = New Collection
> For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
> coll.Add cell.Value ', cell.Value ' Optional
> Next
> ' Loop through the collection and
> ' put the values in column 2
> For n = 1 To coll.Count
> Cells(n, 2).Value = coll(n)
> Next
> Set coll = Nothing
> End Sub
> --------------------
>
>
> Tim Zych;393786 Wrote:
>> Here's a macro that has no problem adding 64K items to a collection
>> although
>> it takes a minute or so to run. Perhaps your dataset had 256 unique
>> values
>> and no more, so it was capping there. I commented out the key
>> assignment in
>> the test macro, but it would work uncommented too since all the values
>> generated below are unique.
>>
>> Sub CollectionTester()
>> Dim coll As Collection, n As Long
>> Dim cell As Range
>> ' Create a list of values all the way down col A
>> For Each cell In Range("A1:A65536")
>> cell.Value = cell.Address(0, 0)
>> Next
>> ' Add to the collection every value in the list
>> Set coll = New Collection
>> For Each cell In Range("A1:A65536")
>> coll.Add cell.Value ', cell.Value ' Optional
>> Next
>> ' Loop through the collection and
>> ' put the values in column 2
>> For n = 1 To coll.Count
>> Cells(n, 2).Value = coll(n)
>> Next
>> Set coll = Nothing
>> End Sub
>>
>>
>> --
>> Regards,
>> Tim Zych
>> 'Compare Excel data with Workbook Compare Pro. Flexible Excel compare
>> tool.' (http://www.higherdata.com)
>> Workbook Compare - Excel data comparison utility
>>
>> "Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
>> news:%(E-Mail Removed)...
>> >i went through a range of 20k+ records to add unique items to a
>> collection
>> >and it always stopped at 256. there are 322 in the list. i just used
>> an
>> >advanced filter for now, to copy the unique values and add them to an
>> >array.
>> >
>> > so, it doesn't really matter, i was just curious and i thought i had
>> used
>> > collections with more than 256 elements, but wasn't sure.
>> >
>> > --
>> >
>> > Gary Keramidas
>> > Excel 2003
>> >
>> >
>> > "Nigel" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> Not to my knowledge. I have larger collections working, not sure
>> what is
>> >> or controls the upper limit?
>> >>
>> >> --
>> >>
>> >> Regards,
>> >> Nigel
>> >> (E-Mail Removed)
>> >>
>> >>
>> >>
>> >> "Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
>> >> news:(E-Mail Removed)...
>> >>>i can't seem to remember, can a collection only hold 256 elements?
>> >>>
>> >>> --
>> >>>
>> >>> Gary Keramidas
>> >>> Excel 2003
>> >>>
>> >>>
>> >>
>> >
>
>
> --
> Simon Lloyd
>
> Regards,
> Simon Lloyd
> 'The Code Cage' (http://www.thecodecage.com)
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
> http://www.thecodecage.com/forumz/member.php?userid=1
> View this thread:
> http://www.thecodecage.com/forumz/sh...d.php?t=109977
>

Simon:

I re-read my reply to you and it sounds kind of blunt. To clarify, I agree
with End(xlup) etc for when code should be migrated to production. Your
change creates a different operating result. Whereas my example populates
65536 rows, yours changes data in the userdrange. It's actually quite
different than my macro.

Regards,

Tim


--
Regards,
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility

"Tim Zych" <tzych@nospam at earthlink dot net> wrote in message
news:%(E-Mail Removed)...
> It is not "production" code. It's a test macro to prove that the
> collection loads 65536 items.
>
> Of course it can be written more robustly. That's not the point.
>
> --
> Regards,
> Tim Zych
> http://www.higherdata.com
> Workbook Compare - Excel data comparison utility
>
> "Simon Lloyd" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> This might be a little picky but you should shorten the range to the
>> used range in coulmn A rather than test every cell, also doing it like
>> below allows forward compatability
>>
>>
>> Code:
>> --------------------
>> Sub CollectionTester()
>> Dim coll As Collection, n As Long
>> Dim cell As Range
>> ' Create a list of values all the way down col A
>> For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
>> cell.Value = cell.Address(0, 0)
>> Next
>> ' Add to the collection every value in the list
>> Set coll = New Collection
>> For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
>> coll.Add cell.Value ', cell.Value ' Optional
>> Next
>> ' Loop through the collection and
>> ' put the values in column 2
>> For n = 1 To coll.Count
>> Cells(n, 2).Value = coll(n)
>> Next
>> Set coll = Nothing
>> End Sub
>> --------------------
>>
>>
>> Tim Zych;393786 Wrote:
>>> Here's a macro that has no problem adding 64K items to a collection
>>> although
>>> it takes a minute or so to run. Perhaps your dataset had 256 unique
>>> values
>>> and no more, so it was capping there. I commented out the key
>>> assignment in
>>> the test macro, but it would work uncommented too since all the values
>>> generated below are unique.
>>>
>>> Sub CollectionTester()
>>> Dim coll As Collection, n As Long
>>> Dim cell As Range
>>> ' Create a list of values all the way down col A
>>> For Each cell In Range("A1:A65536")
>>> cell.Value = cell.Address(0, 0)
>>> Next
>>> ' Add to the collection every value in the list
>>> Set coll = New Collection
>>> For Each cell In Range("A1:A65536")
>>> coll.Add cell.Value ', cell.Value ' Optional
>>> Next
>>> ' Loop through the collection and
>>> ' put the values in column 2
>>> For n = 1 To coll.Count
>>> Cells(n, 2).Value = coll(n)
>>> Next
>>> Set coll = Nothing
>>> End Sub
>>>
>>>
>>> --
>>> Regards,
>>> Tim Zych
>>> 'Compare Excel data with Workbook Compare Pro. Flexible Excel compare
>>> tool.' (http://www.higherdata.com)
>>> Workbook Compare - Excel data comparison utility
>>>
>>> "Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
>>> news:%(E-Mail Removed)...
>>> >i went through a range of 20k+ records to add unique items to a
>>> collection
>>> >and it always stopped at 256. there are 322 in the list. i just used
>>> an
>>> >advanced filter for now, to copy the unique values and add them to an
>>> >array.
>>> >
>>> > so, it doesn't really matter, i was just curious and i thought i had
>>> used
>>> > collections with more than 256 elements, but wasn't sure.
>>> >
>>> > --
>>> >
>>> > Gary Keramidas
>>> > Excel 2003
>>> >
>>> >
>>> > "Nigel" <(E-Mail Removed)> wrote in message
>>> > news:(E-Mail Removed)...
>>> >> Not to my knowledge. I have larger collections working, not sure
>>> what is
>>> >> or controls the upper limit?
>>> >>
>>> >> --
>>> >>
>>> >> Regards,
>>> >> Nigel
>>> >> (E-Mail Removed)
>>> >>
>>> >>
>>> >>
>>> >> "Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
>>> >> news:(E-Mail Removed)...
>>> >>>i can't seem to remember, can a collection only hold 256 elements?
>>> >>>
>>> >>> --
>>> >>>
>>> >>> Gary Keramidas
>>> >>> Excel 2003
>>> >>>
>>> >>>
>>> >>
>>> >
>>
>>
>> --
>> Simon Lloyd
>>
>> Regards,
>> Simon Lloyd
>> 'The Code Cage' (http://www.thecodecage.com)
>> ------------------------------------------------------------------------
>> Simon Lloyd's Profile:
>> http://www.thecodecage.com/forumz/member.php?userid=1
>> View this thread:
>> http://www.thecodecage.com/forumz/sh...d.php?t=109977
>>
>
>