Varying Number Of Nested Loops

[f64]

I am writing a program that requires evaluating combinations of from 2 to 5 variables.

For Variable_E = 1 To 10
For Variable_D = 1 To 10
For Variable_C = 1 To 10
For Variable_B = 1 To 10
For Variable_A = 1 To 10

Total = Function (Variable_A)
+ Function (Variable_B)
+ Function (Variable_C)
+ Function (Variable_D)
+ Function (Variable_E)

Next Variable_A
Next Variable_B
Next Variable_C
Next Variable_D
Next Variable_E

The number of variables to be compared varies from 2 to 5. Is it possible to programmatically change the number of nested loops from 2 to 5 as required?

 

[Zathras]

Instead of hard-coding "1 to 10" use a variable like "1 to AControl" and set AControl to 10 or zero accordingly. (similarly with B and BControl, etc.)

 

[rmikesmith]

I initially had the same idea as Zathras. However, this doesn't work. As an experiment, using your existing example code, change the loop Variable_B endpoint from 10 to 0. Run this modified code (step through it with the debugger) and you will see that Function is never called. Regardless of which loop variable termination is set to zero, Function will never be called.

Perhaps you can expound a bit on what exactly your procedure is supposed to do. You say the number of variables to be compared varies between 2 and 5 but while Variable_A .. Variable_D are strictly speaking variables, you are using them as loop counters with fixed ranges, not inputs. I have to think there is another way to set this up.

Regards,
Mike

 

[Zathras]

Mike, absolutely correct. Perhaps he can set the inner loops for "AControlX to AControY" and set them to 1 and 10 for normal case and set them both to 11 for the inoperative case, being careful to code for that special case when the value is outside the normal range of 1 to 10.

 

[rmikesmith]

Zathras,

I don't quite follow (perhaps a code example ). I'm just wondering if a solution to the problem as stated won't be a bit of a kludge. I probably wasn't very clear in my 2nd paragraph but it seems highly unusual that variables in the sense of constructs for holding inputs, outputs, etc. would be used as loop counters. I would expect to see something like:

Function DoSomething(ByVal Inp1 As Long, ByVal Inp2 as Long) As Long
Dim i as Long

  For i = Inp1 To Inp2
   'Do stuff
  Next i
  DoSomething = ??
End Function

But not

Function DoSomething(ByVal Inp1 As Long, ByVal Inp2 as Long) As Long

  For [b]Inp1[/b] = 1 To 10
    For [b]Inp2[/b] = 1 To 10
      'Do stuff
    Next [b]Inp2[/b]
  Next [b]Inp1[/b]
  DoSomething = ??
End Function

OK, I've blathered on long enough. F64, my ramblings aren't meant as a criticism, just trying to understand what you want to accomplish in order to provide the best solution.

Mike

 

[Zathras]

Ok, here is a code example. Frankly I can't quite imagine a real-life application of the technique, but for what it's worth...

Option Explicit

Sub test()
Dim Variable_A As Integer
Dim Variable_B As Integer
Dim Variable_C As Integer
Dim Variable_D As Integer
Dim Variable_E As Integer
Dim AControlX As Integer
Dim BControlX As Integer
Dim CControlX As Integer
Dim DControlX As Integer
Dim EControlX As Integer
Dim AControlY As Integer
Dim BControlY As Integer
Dim CControlY As Integer
Dim DControlY As Integer
Dim EControlY As Integer
Dim nLoops As Integer
Dim Total As Long

nLoops = 3   [COLOR=green]'<--- change this value[/color]

SetupControls nLoops, 5, AControlX, AControlY
SetupControls nLoops, 4, BControlX, BControlY
SetupControls nLoops, 3, CControlX, CControlY
SetupControls nLoops, 2, DControlX, DControlY
SetupControls nLoops, 1, EControlX, EControlY

For Variable_E = EControlX To EControlY
 For Variable_D = DControlX To DControlY
  For Variable_C = CControlX To CControlY
   For Variable_B = BControlX To BControlY
    For Variable_A = AControlX To AControlY

     Total = Total + MyFunction(Variable_A) _
                   + MyFunction(Variable_B) _
                   + MyFunction(Variable_C) _
                   + MyFunction(Variable_D) _
                   + MyFunction(Variable_E)

    Next Variable_A
   Next Variable_B
  Next Variable_C
 Next Variable_D
Next Variable_E

MsgBox Total
End Sub

Sub SetupControls(ByVal Loops As Integer, ByVal Level As Integer, _
                  ByRef X As Integer, ByRef Y As Integer)
  If Loops < Level Then
    X = 0
    Y = 0
  Else
    X = 1
    Y = 10
  End If
End Sub

Function MyFunction(AVariable As Integer)
  If AVariable > 0 Then
    MyFunction = 1
  Else
    MyFunction = 0
  End If
End Function

Run with different values for nLoops.
Note that I changed the "Total" calculation to be cumulative. There would be no reason for looping if only the last value is all that is wanted.

 

[GlennUK]

Hi f64,

it sounds like doing some recursive calling might work for your solution. I've done that for combination calculations before, the recursion level being equivalent to the number of nested loops, but of course being variable in this case.

What kind of combination results are you aiming for?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Zathras]

Glenn, I agree. Recursion is the way to go when the number of loops is variable. I was trying to keep it to a structure of nested "For" loops because that is what was asked, and I don't know whether f64 (a "TechnicalUser" and not "Programmer" nor "MIS") has the background to understand the concept.

f64: The difficulty I am having is understanding the application. In what way does it make sense to include Variable_A (or Variable_E depending on which way you look at it) in the inner calculation if only 4 nested loops are wanted?

 

[TonyJollans]

Hi f64,

I'm sure recursion is the proper way to go but, sticking with the format of the original, what about ..

nVars = 5

If nVars >= 5 Then Variable_E = 1
Do
    If nVars >= 4 Then Variable_D = 1
    Do
        If nVars >= 3 Then Variable_C = 1
        Do
            If nVars >= 2 Then Variable_B = 1
            Do
                If nVars >= 1 Then Variable_A = 1
                Do

                    Total = Function (Variable_A) _
                            + Function (Variable_B) _
                            + Function (Variable_C) _
                            + Function (Variable_D) _
                            + Function (Variable_E)

                    If nVars >= 1 Then Variable_A = Variable_A + 1 Else Exit Do
                Loop Until Variable_A > 10
                If nVars >= 2 Then Variable_B = Variable_B + 1 Else Exit Do
            Loop Until Variable_B > 10
            If nVars >= 3 Then Variable_C = Variable_C + 1 Else Exit Do
        Loop Until Variable_C > 10
        If nVars >= 4 Then Variable_D = Variable_D + 1 Else Exit Do
    Loop Until Variable_D > 10
    If nVars >= 5 Then Variable_E = Variable_E + 1 Else Exit Do
Loop Until Variable_E > 10

This does require that all the variables exist and that appropriate code in "Function" deals with the case of the unused variables.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

seems like you lot are more interested than f64 ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

As is so often the case - OPs are fickle

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[N1GHTEYES]

If you really wanted to get exotic you could also do the For looping in a sub in a separate module which you write at run time. That way you are actually doing the loops or not - as requested in the original mail.

XL will let you do that, but I believe it gets a bit sniffy if you re-write a module's code using other code which is currently running in the same module.

I'm not suggesting that this method is better than the suggestions given above, but it is an interesting alternative.

 

[rmikesmith]

Hey N1GHTEYES, et. al.

Without the OP to constrain us, nothing is too exotic


Mike

 

[f64]

I think Select Case can be used to execute the correct number of levels:

Given that Run_Number is the number of variables to be evaluated;

----------------------------------------------------------
Public Sub Optimum_Schedule()

Select Case Run_Number
Case 2: GoTo Two_Runs
Case 3: GoTo Three Runs
Case 4: GoTo Four_Runs
Case 5: GoTo Five_Runs
End Select

Five_Runs:
For Variable_E = 1 To 10
Four_Runs:
For Variable_D = 1 To 10
Three_Runs:
For Variable_C = 1 To 10
Two_Runs:
For Variable_B = 1 To 10

Select Case Run_Number
Case Is 2: Total = Function(A) + Function(B)
Case Is 3: Total = Function(A) + Function(B)_
+ Funtion(C)
Case Is 4: Total = Function(A) + Function(B)_
+ Function(C) + Function(D)
Case Is 5: Total = Function(A)+ Function(B)_
+ Function(C) + Function(D)_
+ Function(E)
End Select

Next Variable_B
If Run_Number = 2 Then GoTo Optimum_Complete
Next Variable_C
If Run_Number = 3 Then GoTo Optimum_Complete
Next Variable_D
If Run_Number = 4 Then GoTo Optimum_Complete
Next Variable_E

Optimum_Complete:
End Sub
-----------------------------------------------------------
If there is only one variable (A), then there are no combinations to evaluate, and this subroutine is not invoked.

The first Select Case chooses the appropriate starting loop, the interior Select Case executes the function with correct variables, and the If statements at the end prevent an error when a loop variable is not initialized.

This post generated the most responses of any question I ever posted. Thanks everyone for your help.