Dynamically add nested loops

Question

I have a number of "X" variables (probably 3 to 20 options) that will be combined to calculate all the possible combinations to meet the criteria. An additional loop is introduced for each additional variable, however I don’t know whether it is possible to make the creation of loops dynamic (in excel VBA the code does not have to be very fast).

To demonstrate: I have var. A with h = 2, var. B with h = 3. I would like to know all combinations that are 10 or the best combination of two variables.

In this case: option 1 = 5 * A = 10, 3 * B = 9.2 * A and 2 * B = 10, 3 * A and 1 * B = 9.

The code is as follows:

For A = 0 to 5 h = 0 'Reset previous h if solution is found For B = 0 to 5 h_test = A * height(A) + B * heigth(B) if h_test > 10 if h = 0 then exit for else write h exit for end if h = h_test Next B Next A 

If another parameter is entered (for example, C = 4), the code:

 For A = 0 to 5 h = 0 'Reset previous h if solution is found For B = 0 to 5 h = 0 'Reset previous h if solution is found For C = 0 to 5 h_test = A * height(A) + B * heigth(B) + C * heigth(C) if h_test > 10 if h = 0 then exit for else write h exit for end if h = h_test Next C Next B Next A 

In other words, I would like to know if it is possible to translate the pseudocode into real code:

 For #parameter. = X For loop1 = 1 to 5 h = 0 For loop2 = 1 to 5 h = 0 .... For loopX = 1 to 5 h_test = loop1 *parameter1 + loop2 * parameter 2 ... + loopX * parameter X If h_test > 10 Somecode exit for End if Next X ... Next loop2 Next loop1 

Answer 1

There are two different problems here. You did not mention the first one, and you also need to calculate the value with an indefinite number of arguments. You can use ParamArray .

For instance:

 Public Function Sum(ParamArray args() As Variant) As Long Dim i As Long Dim operand As Integer Dim result As Long For i = LBound(args) To UBound(args) result = args(i) + result Next i Sum = result End Function 

What can be used and tested as follows:

 Public Sub Test() Debug.Print Sum(1,2) '=> 3 Debug.Print Sum(1,2,3) '=> 6 End Sub 

So, this will take care of this problem. Now, regarding the issue you asked about, we will take a similar approach. The key should loop once for each argument you receive.

 Public Sub Run() NestedLoop 1, 2, 3 End Sub Public Sub NestedLoop(ParamArray args() As Variant) Dim result As Long Dim a As Variant a = args Dim h_test As Long Dim i As Long, j As Long For i = LBound(args) To UBound(args) For j = 1 To 5 result = 0 h_test = Sum(a) If h_test > 10 Then If result = 0 Then Exit For Else Debug.Print result Exit For End If End If result = h_test Next j Next i End Sub Public Function Sum(args As Variant) As Long Dim i As Long Dim operand As Integer Dim result As Long For i = LBound(args) To UBound(args) result = args(i) + result Next i Sum = result End Function