Dynamically adding nested loops

Question

I have an 'X' amount of variables (likely to range between 3 to 20 options), which will be combined to calculate all possible combinations to meet a criteria. For every extra variable an extra loop is introduced, however I do not know if it possible to make the creation of loops dynamic (in excel VBA, the code doesn't 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 which are equal to 10 or the best combination of the 2 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 looks like this:

 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 introduced (for example C = 4), the code is:

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 to 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 distinct problems here. You didn't mention the first, and that is you also need to calculate a value with an indeterminate number of arguments. For that, you can use a ParamArray.

For example:

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

Which can be used and tested like this:

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

So, that takes care of that problem. Now, as for the problem you asked about, we'll take a similar approach. The key is to loop once for each argument you've received.

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