recursive nested loops

[iknowu99]

Hello MrExcellers, How would you create function that takes the parameter to make calculation of nested loops. Specifically the repetition of "for " line reproduced inside function?

For ex:

for a=1 to 10
for b=1 to 10
for c=1 to 10
for d=1 to 10

'calculations

next d
next c
next b
next a


as function would be
f(loops, ubound)
f(4, 10)

 

[Peter_SSs]

Something like this?
Run the 'test' sub

Sub test()
  MsgBox f(4, 10)
End Sub

Function f(loops As Long, ub As Long)
  Dim i As Long, j As Long
  
  For i = 1 To loops
    For j = 1 To ub
      f = f + 1
    Next j
  Next i
End Function

 

[Eric W]

Basically, you need to build an odometer. Build an array of indexes, one each for each level (4 in your example). Increment the one on the end. If it's under the upper bound, go to your main loop and do something. If it exceeds the upper bound, set it to the lower bound, and increment the next one. Here's a skeleton version:

ub LoopTest()
Dim ix() As Long, i as Long
Dim numloops as long, lowerbound as long, upperbound as long

    numloops = 4
    lowerbound = 1
    upperbound = 10
    
    ReDim ix(numloops)
    
    For i = 1 To numloops
        ix(i) = lowerbound
    Next i
    
Iterate:

    ' do something
    ' ix(1) has the first index, ix(2) the second, etc.
    ' This just prints the indexes:
    For i = 1 to numloops
        Debug.Print ix(i),
    Next i
    Debug.Print

Increment:    
    For i = 1 To numloops
        ix(i) = ix(i) + 1
        If ix(i) <= upperbound Then GoTo Iterate:
        ix(i) = lowerbound
    Next i
    
    ' We're done
End Sub

When you set the index on the other extreme to the lower bound, you're all done. There are lots of ways to adapt this. You can use a different increment than 1, the upper bounds for each level can be different, etc. You may want to use

For i = numloops to 1 Step -1

instead, depends on the rest of your code.

Hope this helps!