Variable number of Nested Loops

Question

I have a problem I can't wrap my head around. It seems like a recursive function could do the job but I need a bit of help to get started

I have a Collection of collections looking like this:

CollInput
'Each Items can have a variable number of SubItems
Item 1
    Item 1 = 2
    Item 2 = 4
Item 2 = 0
Item 3
    Item 1 = 5
    Item 2 = 7
Item 4
    Item 1 = 6
Item 5 = 0
Item 6
    Item 1 = 7
    Item 1 = 8
Item 7 = 0
Item 8 = 0

And I want to return for a given Item in "CollInput" a collection of all the subsequent items

CollOuput(CollInput(1))
Item 1 = 2 'CollInput(1)(1)
Item 2 = 4 'CollInput(1)(2)
Item 3 = 0 'CollInput(CollInput(1)(1))
Item 4 = 6 'CollInput(CollInput(1)(2))(1)
Item 5 = 7 'CollInput(CollInput(CollInput(1)(2))(1))(1)
Item 6 = 8 'CollInput(CollInput(CollInput(1)(2))(1))(2)

I have tried several combinations of For each, Do until, For i = 1 to CollInput(x).count but I really can't get anything working

Hoping it is clear! Thanks

EDIT : It wasn't very clear actually so here is some precisions:

The values found in the second level gives the items for which I need to loop through. So when we look at my example above, the function CollOuput is given the argument CollInput with an index Value (1 in that case).

1. it should look at item 1 of CollInput, save the values found in level 2 (2 and 4)

2. go to item 2 of level 1, either get 0 because there is no level 2 or simply pass

3. look at item 4 level 1, save the values found in level 2 (6)

4. look at item 6 level 1, save the values found in level 2 (7 and 8)

5. look at item 7 level 1, either get 0 because there is no level 2 or simply pass

6. look at item 8 level 1, either get 0 because there is no level 2 or simply pass

if given the index argument of 3, the result should be :

CollOuput(CollInput(3))
Item 1 = 5 
Item 2 = 7 
Item 3 = 0 'or ignore
Item 4 = 0 'or ignore

Hoping it helps

Answer 1

Thanks to @Peh , I managed to get the results I wanted! That was such a good VBA lesson for me. That's so satisfying and I'm really grateful to you Peh.

So here is the code

Public Function FlattenCollection2(ByVal Col As Collection, ByVal index As Long) As Collection
    Dim FlatCol As Collection
    Set FlatCol = New Collection
    Dim c
    Dim counter As Long
    Dim Val As Long
    counter = 0

    If TypeName(Col(index)) = "Collection" Then
        For Each c In Col(index)
            counter = counter + 1
            Val = Col(index)(counter)
            FlatCol.Add Val
            Dim tmpCol As Collection
            Set tmpCol = FlattenCollection2(Col, Val)
            For j = 1 To tmpCol.Count
                FlatCol.Add tmpCol(j)
            Next j
            Set tmpCol = Nothing
        Next
    End If
    Set FlattenCollection2 = FlatCol
    Set FlatCol = Nothing
    counter = 0
End Function

So from this collection :

I get this output for index = 2

Answer 2

Imagine the following test collection TestCol:

And the following recoursive function:

Public Function FlattenCollection(ByVal Col As Collection) As Collection
    Dim FlatCol As Collection
    Set FlatCol = New Collection

    Dim i As Long
    For i = 1 To Col.Count
        If TypeName(Col(i)) = "Collection" Then 'if current item of Col is a collection itself …
            Dim TmpCol As Collection
            Set TmpCol = FlattenCollection(Col(i)) ' … flatten this collection too
            Dim j As Long
            For j = 1 To TmpCol.Count
                FlatCol.Add TmpCol(j)
            Next j
            Set TmpCol = Nothing
        Else
            FlatCol.Add Col(i)
        End If
    Next i

    Set FlattenCollection = FlatCol
    Set FlatCol = Nothing
End Function

That you can call like this:

Dim OutputCol As Collection
Set OutputCol = FlattenCollection(TestCol)

To get the following flat output collection OutputCol:

Note that if there are too many items in the collections or too many levels then you will easily run out of memory.