CODEWITHSUNDEEP
excelvbadictionaryfor-loopcollections
Gooder
Gooder

Reputation: 11

how to get item in vba collection

I'm new to VBA and I got a issue when trying to get item from collection.

I have a self defined class, A, and I have a collection B=[A1,A2...] by using

B.add A

Then I have a dictionary like C={1:B1, 2:B2...} by using

C.add i, Bi

now I want to get the C(i)(j), I build code like following, but it keeps giving me error: object doesn't support this property or method.

dim levels as variant

levels = C.items

dim level as variant

dim newA as A

for i = 0 to levels.count -1

 level = levels(i)

 for j = 0 to level.count -1

  newA = level(j)

 next

next

The error happens when I try to assign the collection and class to variant, i.e. level = levels(i) and, newA = level(j)

I know I could use for each to build loop but I need to use the index, and the object it self(might need to modify the object inside the collection later), so wondering what's the best way to do this. Thanks!

Upvotes: 0

Views: 2332

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Here's an example which works for me.
Class A just has a single field Public id As String

Sub Tester()

    Dim C As Object, items, i As Long, objA As A
    
    Set C = CreateObject("scripting.dictionary")
    
    'populate dictionary with a couple of collections of Class A instances
    C.Add 1, New Collection
    C(1).Add GetAInstance("Id001")
    C(1).Add GetAInstance("Id002")
    
    C.Add 2, New Collection
    C(2).Add GetAInstance("Id003")
    C(2).Add GetAInstance("Id004")
    C(2).Add GetAInstance("Id005")
    
    'looping...
    items = C.items
    For i = LBound(items) To UBound(items)
        For Each objA In items(i)
            Debug.Print objA.id
        Next objA
    Next i
    
    'direct access
    Debug.Print C(1)(1).id '> "Id001"
    Debug.Print C(2)(3).id '> "Id005"
    
    C(2)(3).id = "New id"
    Debug.Print C(2)(3).id '> "New id"

    Set objA = C(2)(3) 'Set is required for object-type variables
    Debug.Print objA.id '> "New id"

End Sub

'function to return an object of class A with supplied id
Function GetAInstance(idValue)
    Dim rv As New A
    rv.id = idValue
    Set GetAInstance = rv
End Function

Upvotes: 1

Related Questions