CODEWITHSUNDEEP
vbafor-looppermutation
Eduards
Eduards

Reputation: 68

VBA List all possible combination of variable number of items (number of nested loops as variable)

gentleman! I am having trouble with figuring out a way to define the number of elements as variable when listing all possible combinations. I have a hard coded example of this where number of elements = 3


'Declare variables
Dim a as long
Dim b as Long
Dim C as Long
Dim ElementsArray  as variant

'Array
ElementsArray = array("1400","1900","2400")

'Loop through combinations
for a = lbound(ElementsArray) to ubound(ElementsArray)
    for B= lbound(ElementsArray) to ubound(ElementsArray)
        for c = lbound(ElementsArray) to ubound(ElementsArray)
        debug.print(ElementsArray(a) & " - " & ElementsArray(b) & " - " & ElementsArray(c))
        next c
    next b
next a

But What I am looking for is a code in which perhaps the number of nested For loops is a variable or some other ways to permutate through all possible combinations. Please help solve this problem.

Upvotes: 0

Views: 480

Answers (1)

FunThomas
FunThomas

Reputation: 29296

Here is an example of a recursive implementation. Just be warned that you shouldn't make your array too large as you will get n to the power of n solutions - for 4 elements, that's 256, for 5 elements 3'125, for 6 you get 46'656 and for 7 already 823'543 - don't complain if the program takes a long time to execute. And of course you need a way to do something with every permutation.

Option Explicit

Sub test()
    Dim ElementsArray  As Variant
    ElementsArray = Array("1400", "1900", "2400")
    ReDim SolutionArray(LBound(ElementsArray) To UBound(ElementsArray))
    
    recursion ElementsArray, SolutionArray, LBound(ElementsArray)
End Sub

Sub recursion(elements, solution, level As Long)
    Dim i As Long
    For i = LBound(elements) To UBound(elements)
        solution(level) = elements(i)
        If level = UBound(elements) Then
            Debug.Print Join(solution, " - ")
        Else
            recursion elements, solution, level + 1
        End If
    Next i
End Sub

Update: This is the result:

enter image description here

Update
Still not sure if I understand. The following code will create a list of n-Tupel out of an array of values. In the example (test), we have an array of 4 values and set n to 3 (defined as constant).

Sub test()
    Const n = 3
    Dim ElementsArray  As Variant
    ElementsArray = Array("1400", "1900", "2400", "9999")
    
    ReDim SolutionArray(0 To n - 1)
    recursion ElementsArray, SolutionArray, LBound(ElementsArray)
End Sub

Sub recursion(elements, solution, level As Long)
    Dim i As Long
    For i = LBound(elements) To UBound(elements)
        solution(level) = elements(i)
        If level = UBound(solution) Then
            Debug.Print Join(solution, " - ")
        Else
            recursion elements, solution, level + 1
        End If
    Next i
End Sub

Upvotes: 4

Related Questions