Collection Variable

[Todd Huttenstine]

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

 

[Tom Ogilvy]

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.

 

[Bob Kilmer]

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

 

[Bob Kilmer]

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

 

[Patrick Molloy]

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(mDictionary.items)
End Sub


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