Recursive combinations [please help]

[WorkHorse]

How do I make the code below recursive? I'm trying to create all combination of the array. This gives all combinations where the length is exactly 5. I want it to give all combinatoin where the length is 1 to the ubound of array s. So for a length of 3 there would be 3 nested loops, for length of 8 there would be 8 nested loops and so on.

Ultimately what I want to accomplish is to have an array filled with numbers and find the sum of every possible combination and pick the combination that has a sum that is closet to a target number without going under, but I thought I'd start here first.

Sub CreateCombos()
 
    Dim s(8)
 
    s(1) = "A"
    s(2) = "B"
    s(3) = "C"
    s(4) = "D"
    s(5) = "E"
    s(6) = "F"
    s(7) = "G"
    s(8) = "H"
 
    lLen = 5
 
    For i1 = 1 To (UBound(s) - lLen) + 1
    For i2 = i1 + 1 To (UBound(s) - lLen) + 2
    For i3 = i2 + 1 To (UBound(s) - lLen) + 3
    For i4 = i3 + 1 To (UBound(s) - lLen) + 4
    For i5 = i4 + 1 To (UBound(s) - lLen) + 5
                    
        Debug.Print s(i1) & s(i2) & s(i3) & s(i4) & s(i5)
    
    Next i5
    Next i4
    Next i3
    Next i2
    Next i1
 
End Sub

Any help?

[Logophobic]

By definition, for a procedure to be recursive, it must call itself.

Option Explicit
 
Private Sub CreateCombos()
  Dim s(1 To 4) As String
  Dim MyCombo As String
  Dim MaxLen As Long
  
  s(1) = "A"
  s(2) = "B"
  s(3) = "C"
  s(4) = "D"
  
  Call ComboRecursion(s, MyCombo, 1, 3)
End Sub
 
Private Sub ComboRecursion(s() As String, MyCombo As String, ByVal ThisLen As Long, MaxLen As Long)
  Dim i As Long
  
  For i = LBound(s) To UBound(s)
    MyCombo = MyCombo & s(i)
    If ThisLen < MaxLen Then
      Call ComboRecursion(s, MyCombo, ThisLen + 1, 3)
    Else
      Debug.Print MyCombo
    End If
    MyCombo = Left$(MyCombo, ThisLen - 1)
  Next i
End Sub
 
Private Sub Command1_Click()
  Call CreateCombos
End Sub

This code obviously doesn't do what you want, but it should give you an idea about how to approach this.

[WorkHorse]

I still don't get it. I understand recursion, but I just can't seem to apply it to my own code. Any help?

[WorkHorse]

OK, any way to do this without recursion? I don't care. I just need a way to handle this where lLen could be anything so there could be an infinite number of for...next loops. I'm not getting anything to work. Please help.

[Bruce Fox]

G'Day WH, how are ya.

Wouldn't the math be (in the example at least)
8x7x6x5x4x3x2x1 (being a range from 1-8)

So that something simple like A,B,C (an Array of 3 elements) yould yeild:

ABC
ACB
BAC
BCA
CAB
CBA

For this you have 3x2x1 = 6, hence 6 results (above) for A,B,C.


Is that theory correct (if so I have seen it posted here before)?

[WorkHorse]

G'Day WH, how are ya.

Wouldn't the math be (in the example at least)
8x7x6x5x4x3x2x1 (being a range from 1-8)

So that something simple like A,B,C (an Array of 3 elements) yould yeild:

ABC
ACB
BAC
BCA
CAB
CBA

Is that theory correct (if so I have seen it posted here before)?

No, no, no. The idea is to get combinations, not permutations (if I'm using those words right). ABC is the same as ACB as far as I'm concerned. I have an array of numbers. I need to find the best combination of those numbers so that the some of the combination gets closest to a target value. The sum of A+B+C would be the same as the sum of A+C+B. So I need exclusive combinations. And I need every combination or every length. The array might have 8 numbers, but the best combination might consist of only two of those numbers, or only one, or all 8.

I think I'm getting close with the code below, but I'm not sure how to work in the array.

Sub DooDah()
 
    For x = 1 To 3
        Combo "", 0, x, 1, 4
    Next x
 
End Sub
 
Sub Combo(sPrefix, lDepth, lLen, lMin, lMax)
 
    For i = lMin To lMax
      If lDepth = (lLen - 1) Then
        Debug.Print sPrefix & i
      Else
        Combo sPrefix & i, lDepth + 1, lLen, i + 1, lMax + 1
      End If
    Next i
 
End Sub

[WorkHorse]

Bwwwahhhhahhhahhhhaa!!!

I've at least managed to do what I asked for in the original post. Now I just need to work in the array.

Sub DooDah()
 
    For x = 1 To 5
        Combo "", 0, x, 1, 5
    Next x
 
End Sub
 
Sub Combo(sPrefix, lDepth, lLen, lMin, lMax)
 
    For i = lMin To lMax
      If lDepth = (lLen - 1) Then
        Debug.Print sPrefix & Chr$(i + 64)
      Else
        Combo sPrefix & Chr$(i + 64), lDepth + 1, lLen, i + 1, lMax
      End If
    Next i
 
End Sub

[Bruce Fox]

Could you now pass each Array element from an ireration within the DooDah() Sub (similar to the For/Next (x - being the Array Index))?