Collection Variable
 

Hey guys

I have a collection variable called "TestCollection"

How do I specify to dump the entire collection into range
A:A?

Lets say there are 3 items in the collection... First
item is dog, second is cat, third is bird. I want cell A1
to say dog, cell A2 to say cat, and cell A3 to say bird.


How do I do this?


Thank you
Todd Huttenstine

Collection Variable
 

Loop through the collection. to the best of my knowledge, there is no
support for dumping it in one command like you can do with an array.

--
Regards,
Tom Ogilvy

"Todd Huttenstine" wrote in message
...
Hey guys

I have a collection variable called "TestCollection"

How do I specify to dump the entire collection into range
A:A?

Lets say there are 3 items in the collection... First
item is dog, second is cat, third is bird. I want cell A1
to say dog, cell A2 to say cat, and cell A3 to say bird.


How do I do this?


Thank you
Todd Huttenstine

Collection Variable
 

Does this count?

Sub Main()
Dim col As Collection
Set col = New Collection
col.Add "dog", "dog"
col.Add "cat", "cat"
col.Add "bird", "bird"
Range("A1:A" & col.Count) = _
WorksheetFunction.Transpose( _
Array( _
col.Item("dog"), _
col.Item("cat"), _
col.Item("bird")))
End Sub

"Todd Huttenstine" wrote in message
...
Hey guys

I have a collection variable called "TestCollection"

How do I specify to dump the entire collection into range
A:A?

Lets say there are 3 items in the collection... First
item is dog, second is cat, third is bird. I want cell A1
to say dog, cell A2 to say cat, and cell A3 to say bird.


How do I do this?


Thank you
Todd Huttenstine

Collection Variable
 

For a collection of arbitrary length, you do have to loop, but at least you
can assign the range all at once.

Sub Main()
Dim col As Collection
Set col = New Collection
col.Add "dog", "dog"
col.Add "cat", "cat"
col.Add "bird", "bird"
Dim v, str As String
For Each v In col
str = str & v & ","
Next
v = Split(str, ",")
If UBound(v) -1 Then _
Range("A1:A" & col.Count) = _
WorksheetFunction.Transpose(v)
End Sub

"Todd Huttenstine" wrote in message
...
Hey guys

I have a collection variable called "TestCollection"

How do I specify to dump the entire collection into range
A:A?

Lets say there are 3 items in the collection... First
item is dog, second is cat, third is bird. I want cell A1
to say dog, cell A2 to say cat, and cell A3 to say bird.


How do I do this?


Thank you
Todd Huttenstine

Collection Variable
 

Try the dictionary object - its part of Microsoft Scripting Runtime - set a
reference to this from the Tools/References menu.
An advantage of the Dictionary is that you can load the keys as well as the
items into a variant & dump to a sheet.

The following code should be in a standard module and the reference set.

Option Explicit
Private mDictionary As Scripting.Dictionary

Sub Main()

Range("B:C").Clear

LoadDictioanry

DumpDictionary

End Sub

Private Sub LoadDictioanry()

Dim index As Long

Dim key As String

Set mDictionary = New Scripting.Dictionary

For index = 1 To 50 + Int(Rnd() * 50)

key = ""
Do While Len(key) < (2 + Int(Rnd() * 5))

key = key & Chr(65 + Int(26 * Rnd))

Loop

If Not mDictionary.Exists(key) Then
mDictionary.Add key, Rnd() * 1000
End If

Next


End Sub

Private Sub DumpDictionary()
Dim keys
Dim items
keys = mDictionary.keys

Range(Range("B2"), Cells(mDictionary.Count + 1, 2)) = _
Application.WorksheetFunction.Transpose(keys)
Range(Range("C2"), Cells(mDictionary.Count + 1, 3)) = _
Application.WorksheetFunction.Transpose(mDictionar y.items)
End Sub


For the answer to the question, examine the DumpDictionary procedure. Run
Main for the demo.

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Todd Huttenstine" wrote in message
...
Hey guys

I have a collection variable called "TestCollection"

How do I specify to dump the entire collection into range
A:A?

Lets say there are 3 items in the collection... First
item is dog, second is cat, third is bird. I want cell A1
to say dog, cell A2 to say cat, and cell A3 to say bird.


How do I do this?


Thank you
Todd Huttenstine