VBA loop where order doesn't matter

user_13407453 0
Viewed 113

I'm trying to run a loop on 3 variables, where order doesn't matter.

The code I've tried first is the following, where nx runs through the rows, and limit is the last row of my database: 

Do While n3 <= limit
    Do While n2 <= limit
       Do While n1 <= limit
          Call Output
          n1 = n1 + 1
       Loop
       Call Output
       n2 = n2 + 1
       n1 = n0
    Loop
    Call Output                         
    n3 = n3 + 1
    n2 = n0
    n1 = n0
Loop

This allows me to test every possibility, but it does also repeat the same combination several times, which increases the runtime. This will make the code unusable if I plan on testing, let's say, 20 variables.

Any tips on how to optimize this loop?

Thank you.

do-while vba excel loops
2 Answers

Based on your comment that you do not want permutations of a given combination. Lets say we are mixing paint. We have five different colors:

  1. white
  2. black
  3. yellow
  4. blue
  5. green

We want to mix all possible combinations of three cans, but once we have mixed

white,blue,green

we don't need any of these:

white,green,blue
green,white,blue
green,blue,white
blue,green,white
blue,white,green

because they all result in the same light teal.

First we run the loops in this staggered fashion:

Sub MixPaint()
    Dim arr(1 To 5) As String
    Dim i As Long, j As Long, k As Long, LL As Long
    arr(1) = "white"
    arr(2) = "black"
    arr(3) = "blue"
    arr(4) = "green"
    arr(5) = "yellow"
    LL = 1
    For i = 1 To 3
        For j = i + 1 To 4
            For k = j + 1 To 5
                Cells(LL, 1) = arr(i) & ":" & arr(j) & ":" & arr(k)
                LL = LL + 1
            Next k
        Next j
    Next i
End Sub

This gets us:

enter image description here

This removes the permuted duplicates, but it also removes combinations like:

blue,blue,white

To get these back we adjust the loops slightly:

Sub MixPaint2()
    Dim arr(1 To 5) As String
    Dim i As Long, j As Long, k As Long, LL As Long
    arr(1) = "white"
    arr(2) = "black"
    arr(3) = "blue"
    arr(4) = "green"
    arr(5) = "yellow"
    LL = 1
    For i = 1 To 5
        For j = i To 5
            For k = j To 5
                Cells(LL, 5) = arr(i) & ":" & arr(j) & ":" & arr(k)
                LL = LL + 1
            Next k
        Next j
    Next i
End Sub

Now we have:

enter image description here

Which may be what you are after.

user_24746560

If you need to loop through a table I would loop throw the rows and columns of the table, with a double for o double while, through all the cells, to avoid repeating combinations. According to your while approach this would be: 

Do While row <= rowLimit
   Do While col <= colLimit
      'with if conditions you can make your operations

      col = col +1
   Loop
   row = row + 1
Loop

If you need to loop through the rows independently, you dont need to the whiles to be nested, and each while can loop its row independently. If n1, n2, and n3 have dependencies with each other, you would need to explain those, so that their relation can be taken into account to exclude determined combinations from the nested loop. However, if the order of the combination matters as far as I checked there are no combinations repeated in your loop. This is the log of your loop for example for n1=n2=n3 and limit =2

1 0 0
0 1 0
0 0 0
1 0 0
2 0 0
3 0 0
0 1 0
1 1 0
2 1 0
3 1 0
0 2 0
1 2 0
2 2 0
3 2 0
0 3 0
0 0 1
1 0 1
2 0 1
3 0 1
0 1 1
1 1 1
2 1 1
3 1 1
0 2 1
1 2 1
2 2 1
3 2 1
0 3 1
0 0 2
1 0 2
2 0 2
3 0 2
0 1 2
1 1 2
2 1 2
3 1 2
0 2 2
1 2 2
2 2 2
3 2 2
0 3 2

But if the order does not matter and you need to loop through every n, up to the row limit, with no n value reptition, then the while loop can be independent so do not need to be nested.

So I am not sure if I have answered your question or I am missing something.

Hope that helps anyhow

user_118261320
Related