CODEWITHSUNDEEP
excelvba
David
David

Reputation: 203

printing a collection item to immediate window in excel vba

I was wondering how do I print an item in my collection to the immediate window in excel VBA? I want to either have a collection for each collection item or an array for each collection item, which ever is easier to pull information from. Here is some example code of what I'm talking about

Sub test()

Dim c As Collection
Dim a As Collection

Set a = New Collection

For i = 1 To 10
    Set c = New Collection
    c.Add Array("value1", "value2", "value3","valvue4, "value5"), "key1"
    c.Add "value2", "key2"
    c.Add "value3", "key3"
    c.Add "value4, "key4"
    c.Add "value5", "key5"

    a.Add c, c.Item(1)    

'lets say I wanted to print value4 or value1 from the 1st item
Debug.Print a.Item(1(2))

Next i

End Sub

Upvotes: 4

Views: 21529

Answers (2)

Frank
Frank

Reputation: 454

To add to @Gary's Student's answer, you can't use integers as keys for a collection. So you either cast them to a string using the Cstr function or you can use a dictionary instead. If you decide to use a dictionary, make sure to enable the Microsoft Scripting Runtime (under tools -> references). I've added some examples below.

    Sub collExample()
        Dim i As Integer
        Dim c As Collection

        Set c = New Collection
        For i = 1 To 10
            c.Add 2 * i, CStr(i)
        Next i
        'keys cant be integers
        'see https://msdn.microsoft.com/en-us/library/vstudio/f26wd2e5(v=vs.100).aspx

        For i = 1 To 10
            c.Item (i)
        Next i
    End Sub

    Sub dictExample()
        Dim d As New Dictionary
        Dim i As Integer

        For i = 1 To 10
            d(i) = 2 * i
        Next i

        Dim k As Variant
        For Each k In d
            Debug.Print k, d(k)
        Next k

        Dim coll As New Collection
        coll.Add "value1"
        coll.Add "value2"
        coll.Add "value3"

        Set d("list") = coll

        Dim newCol As Collection
        Set newCol = d("list")

        Dim v As Variant
        For Each v In newCol
            Debug.Print v
        Next v

    End Sub

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96791

This seems to work:

Sub ytrewq()
    Dim c As Collection
    Set c = New Collection
    c.Add "x", CStr("x")
    c.Add "y", CStr("y")
    c.Add "z", CStr("z")
    i = 2
    MsgBox c.Item(i)
    Debug.Print c.Item(i)
End Sub

Upvotes: 2

Related Questions