CODEWITHSUNDEEP
excelvbacollectionsoutput
zaanwar
zaanwar

Reputation: 65

Printing entries in a Collection

I have a Collection containing x amount of items. I simply need the contents of that Collection to be output into an Excel column.

For Each item In myCollection
'Insert code here

Next item

Any idea how I can do this?

Upvotes: 1

Views: 2865

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

This is one of the reasons why a Dictionary object is superior to the Collection object.

With Collections you must loop through the entire collection object to retrieve all of the values.

With Dictionaries you can retrieve all of the values in one go, which is a tremendous speed boon when writing to a worksheet. Writing one cell at a time is one of the most inefficient things you can do in Excel.

So here is how to convert to using Dictionaries. Note the last line. The entire dictionary is written to the range in one go:

Sub zaanwar()

    Dim myDictionary As Object

    Set myDictionary = CreateObject("scripting.dictionary")

    With myDictionary
        .Item("key1") = "val1"
        .Item("key2") = "val2"
        .Item("key3") = "val3"
        .Item("key4") = "val4"
    End With

    [a1:d4] = Application.Transpose(myDictionary.Items)

End Sub

Upvotes: 2

Kris B
Kris B

Reputation: 446

You can change the starting row or column from 1, to whatever you want. Also you can insert into cells by incrementing columns by replacing 'r = r + 1' with 'c = c + 1'.

Note: Change "Sheet1" to whatever your worksheet name is, this is just the default name I used since I don't know the name of your actual worksheet.

Dim r As Integer
Dim c As Integer
r = 1 'Row 1
c = 1 'Column A
For Each item In myCollection
    ThisWorkbook.Worksheets("Sheet1").Cells(r, c).Value = item
    'Increment Row by 1
    r = r + 1
Next item

Upvotes: 3

Related Questions