Collection of Collections - Refer to SubCollection Item with Variable for Primary Collection

Question

I’m using (as example) a UserForm with six TextBoxes on a 3x2 matrix: like this:

c1  c2  c3
c4  c5  c6

I’m trying to add both of these Rows of TextBoxes to a Collection (secondary) and then add this Collections to a Collection (primary) of Collection. And then refer to an object in the nested Collection (secondary) with a variable for the Collection in the primary Collection. Like: The “Collection (primary).First Element. Collection (secondary).Third Element.name” is c3.

I’ve tried to read and implement Referencing an object within a collection of collections by key or Collection of Collections - How to make sub-collections by value rather than reference?, and I also tried with a Dictionary like suggested Dynamically Create collection of Collections VBA, but I keep getting:

Run-time error '438': Object doesn't support this property or method

On this line( or in one of the line variations posted in the code below):

If rigavar.ItemCollection(q)(3).Name = "c3" Then

I’ve tried to understand and adapt those examples but I just can’t get it done and I don’t know if I’m missing something or I’m doing it completely wrong or if I just didn’t understand how things works or all of them.

I got a workaround by creating just a collection for each row and by creating a specific procedure for each single collection. When the Routine reach a certain Collection, it fires the Routine with the specific references for that Collection, something like this:

Private Sub workaround_Click() 
    Set rigaAA = New Collection
    rigaAA.Add c1
    rigaAA.Add c2
    rigaAA.Add c3
    
    Set rigaBB = New Collection
    rigaBB.Add c4
    rigaBB.Add c5
    rigaBB.Add c6
    
    If rigaAA.ItemCollection(3).Name = "c3" Then
    
    Call rigaAAspecificRoutine
        
        End if
    End sub

    Private Sub rigaAAspecificRoutine()
        MsgBox c1.value & c2.value  & c3.value
    End sub

But I was hoping to get it done by variables instead of specific, something like:

Private Sub test_Click()

Dim rigaAA As VBA.Collection
Dim rigaBB As VBA.Collection
Dim rigavar As VBA.Collection

Set rigaAA = New Collection
rigaAA.Add c1
rigaAA.Add c2
rigaAA.Add c3

Set rigaBB = New Collection
rigaBB.Add c4
rigaBB.Add c5
rigaBB.Add c6

Set rigavar = New Collection
rigavar.Add rigaAA
rigavar.Add rigaBB

'none of this works:

For q = 1 To 2
    If rigavar.ItemCollection(q)(3).Name = "c3" Then
        MsgBox c1.value & c2.value  & c3.value
    End If
Next

For q = 1 To 2
    If rigavar.ItemCollection(q)(3).Name = "c3" Then
        MsgBox c1.value & c2.value  &  c3.value
    End If
Next

For q = 1 To 2
    If rigavar.ItemCollection(q, 3).Name = "c3" Then
        MsgBox c1.value & c2.value  & c3.value
    End If
Next

For q = 1 To 2
    If rigavar.ItemCollection(q).ItemCollection(3).Value <> "" Then
        MsgBox c1.value & c2.value  & c3.value
    End If
Next
    
End Sub

Any suggestion? Thanks a lot

Answer 1

A Collection has no property ItemCollection. To access element q of collection rigavar, you can use one of the following methods

rigavar(q)
rigavar.Item(q)

(Usually, the shorter form is used, this is basically an abbreviation to the longer form as the Item property is the so called default property of a collection).

As the element rigavar(q) is again a Collection, you can access the 3rd element with

rigavar(q)(3)
rigavar.Item(q)(3)
rigavar.Item(q).Item(3)

Easiest way to check the name of the control, use rigavar(q)(3).Name

What you can't do is to write rigavar(q, 3) because now VBA would take both parameters as parameters into rigavar.Item (as if it was a 2-dimensional array).