Recursive Combinations

[lrobbo314]

I feel like I am pretty good at VBA, but, I have always struggled with wrapping my head around recursion. I banged my head against the wall for hours this morning trying to figure this out. I got the code to work, but I am sure there must be a better way to do it. I would like to get better at recursion, so any pointers would be appreciated.

Basically I have an array with R, G, B, Y, and O. And I wanted to list each combination with an increasing number of elements in each group. So, the desired results are as below.

R
G
B
Y
O
RG
RB
RY
RO
GB
GY
GO
BY
BO
YO
RGB
RBY
RYO
GBY
GYO
BYO
RGBY
RBYO
GBYO
RGBYO

And here is the code I came up with...

Sub Main()
Dim AR(1 To 5) As Variant 'Initialize array
Dim Index As Long: Index = 1 'Index to keep track of spot in array
Dim Length As Long: Length = 0 'Length of characters to add to string
Dim Total As Long: Total = UBound(AR) + 1 'Number of iterations per group


AR(1) = "R"
AR(2) = "G"
AR(3) = "B"
AR(4) = "Y"
AR(5) = "O"


DoIt AR, Index, Length, Total


End Sub




Sub DoIt(V As Variant, Index As Long, Length As Long, Total As Long)
Dim tmp As String


For i = Index + 1 To Total
    tmp = V(Index) 'Add index value to string
    If Length = 0 Then Index = Index + 1
    For j = 1 To Length
        tmp = tmp & V(i + j - 1) 'Add other element from recursion
    Next j
    Debug.Print tmp
    tmp = vbNullString 'Clear out string
Next i


Index = Index + 1


If Index >= Total Then
    Length = Length + 1 'Increase length of string
    Index = 1
    Total = Total - 1 'Decrease number of iterations
End If




If Total > 1 Then DoIt V, Index, Length, Total




End Sub

Thanks in advance to anyone who can help give me some pointers on how to do this better.

 

[MickG]

Try this for data in column "A" and Results in column "C".

[COLOR="Navy"]Sub[/COLOR] MG21Dec49
[COLOR="Navy"]Dim[/COLOR] rRng [COLOR="Navy"]As[/COLOR] Range, p
[COLOR="Navy"]Dim[/COLOR] vElements, lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] vresult [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] rRng = Range("A1", Range("A1").End(xlDown))
[COLOR="Navy"]For[/COLOR] p = 1 To 5
    vElements = Application.Index(Application.Transpose(rRng), 1, 0)
    ReDim vresult(1 To p)
    Call CombinationsNP(vElements, CInt(p), vresult, lRow, 1, 1)
[COLOR="Navy"]Next[/COLOR] p
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 
[COLOR="Navy"]Sub[/COLOR] CombinationsNP(vElements [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] vresult [COLOR="Navy"]As[/COLOR] Variant, lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] iElement [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] iIndex [COLOR="Navy"]As[/COLOR] Integer)
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
 
[COLOR="Navy"]For[/COLOR] i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    [COLOR="Navy"]If[/COLOR] iIndex = p [COLOR="Navy"]Then[/COLOR]
        lRow = lRow + 1
        Range("C" & lRow) = Join(vresult, "")
    [COLOR="Navy"]Else[/COLOR]
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[lrobbo314]

Thanks for the reply Mick. That does look like more proper recursion to me. I'll dig into it and try to understand the mechanics. Does my code have fundamental flaws? Do you have any resources to get a better understanding of how to do this stuff?