Make the number of nested for loops a variable - VBA

Question

I have developed a code like this

For i = 1 To 50

    For j = i + 1 To 50

        For k = j + 1 To 50

            ..........................
            'Here I have someother code
            ..........................

        Next k

    Next j

Next i

So this is a nested for loop. And there are 3 for loops in this code. Now what I want is, I want to make the number of for loops a variable. For an example I have variable call NumberOfForLoops. So if NumberOfForLoops=3 then there should be 3 for loops. If NumberOfForLoops=8 then there should be 8 for loops. I can't find how to do this.

I think it is better to explain what I was trying to accomplish.

I'm trying to find possible combinations

For 3 combinations I wrote this code

    Dim WS_Data As Worksheet
    Dim WS_Result As Worksheet

    Set WS_Data = Worksheets("Data")
    Set WS_Result = Worksheets("Result")

    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim EnteringRow As Long

    EnteringRow = 1

    For i = 1 To 5
        For j = i + 1 To 5
            For k = j + 1 To 5
                WS_Result.Range("A" & EnteringRow).Value = WS_Data.Range("A" & i).Value
                WS_Result.Range("B" & EnteringRow).Value = WS_Data.Range("A" & j).Value
                WS_Result.Range("C" & EnteringRow).Value = WS_Data.Range("A" & k).Value
                EnteringRow = EnteringRow + 1
            Next k
        Next j
    Next i

It gave me below result

Input and Outcome

But now what I need is I want to make the number of items I select from the list dynamic. According to the anwers I understood that I need to use a recursion concept. So I modified the code according to h2so4's answer.

This is the modified code

    Sub test()
        Dim WS_Data As Worksheet
        Dim WS_Result As Worksheet
        Dim WS_Temp As Worksheet

        Set WS_Data = Worksheets("Data")
        Set WS_Result = Worksheets("Result")
        Set WS_Temp = Worksheets("Temp")

        ResultRow = 1
        NofL = 3
        Nestedloop WS_Data, WS_Result, WS_Temp, ResultRow, NofL, 1, 5, 1

    End Sub
    Sub Nestedloop(WS_Data, WS_Result, WS_Temp, ResultRow, NofL, jmin, jmax, level)

        For j = jmin To jmax
            WS_Temp.Cells(1, level) = j
            'your code when a value of j is set
            If level < NofL Then
                Nestedloop WS_Data, WS_Result, WS_Temp, ResultRow, NofL, jmin + 1, jmax, level + 1
            Else
                'your code when the number of loops is reached

                For i = 1 To NofL
                    WS_Result.Cells(ResultRow, 0 + i).Value = WS_Data.Range("A" & WS_Temp.Cells(1, i).Value).Value
                Next i
                ResultRow = ResultRow + 1
            End If
        Next j
    End Sub

I used a tempory sheet. This is the result I got.

Result got

Difficult to understand what is wrong.

I think you what you want is a recursive function. They are possible in VBa but take some three dimensional thinking. — Scott Craner, Oct 09 '16 at 04:24
This is similar to the "create all possible combinations" questions which turn up here from time to time: see here for example https://stackoverflow.com/questions/19780016/vba-write-all-possible-combinations-of-4-columns-of-data/19780307#19780307 Which approach is best for your purposes would depend on exactly what your "some other code" needs to do. — Tim Williams, Oct 09 '16 at 06:45
Thank you everyone for the answers. Yes I need somekind of recursive function like this https://www.youtube.com/watch?v=MboLHJMmLsQ — Roshantha De Mel, Oct 10 '16 at 18:53
I have been thinking today how to avoid nested loops, because of the question here - https://stackoverflow.com/questions/49486537/highest-possible-sum/49486650#49486650 and the only question in StackOverflow I found was this one. You may decide checking my answer, the code is written twice - with nested loops and without nested loops with recursion - https://stackoverflow.com/a/49486650/5448626 — Vityata, Mar 26 '18 at 15:08

h2so4 · Answer 1 · 2016-10-09T19:22:44.027

2

another proposal with recursive sub that simulates nested loops

Sub test()
    NofL = 4
    Nestedloop NofL, 1, 50, 1
End Sub
Sub Nestedloop(NofL, jmin, jmax, level)
    For j = jmin To jmax
        Cells(1, level) = j
        'your code when a value of j is set
        If level < NofL Then
            Nestedloop NofL, jmin + 1, jmax, level + 1
        Else
        'your code when the number of loops is reached
        End If
    Next j
End Sub

edited Oct 09 '16 at 19:22

answered Oct 09 '16 at 14:55

h2so4

1,559
1
10
11

1

Thank you for the quick answer. I tried to modify this code to suit with my requirement. But was unable to get final result I need. I added more information to the original post. – Roshantha De Mel Oct 10 '16 at 18:47

score 0 · Answer 2 · answered Oct 09 '16 at 15:34

Your question can be read in more than one way. If you are looking for a tool to help in generating boiler-plate code, perhaps something like this:

'In the following code, if vars is missing, successive loop indices are "i", "j", "k", etc
'otherwise, vars is treated as the loop vars and should be passed as a 0-based array with
'depth strings, where depth is how deeply nested the loops are

Function NestedFors(lim As Long, depth As Long, Optional vars As Variant) As String
    Dim i As Long, n As Long
    Dim codeShell As String

    If IsMissing(vars) Then
        vars = Split("i j k l m n o p q r s t u v w x y z") 'should be overkill -- if not, you deserve a runtime error!
    End If

    codeShell = "For " & vars(0) & " = 1 To " & lim & vbCrLf

    For i = 1 To depth - 1
        codeShell = codeShell & String(i, vbTab)
        codeShell = codeShell & "For " & vars(i) & " = " & vars(i - 1) & " + 1 To " & lim & vbCrLf
    Next i

    codeShell = codeShell & String(depth, vbTab) & "'----- Insert code here ------" & vbCrLf

    For i = depth - 1 To 1 Step -1
        codeShell = codeShell & String(i, vbTab) & "Next " & vars(i) & vbCrLf
    Next i

    codeShell = codeShell & "Next " & vars(0) & vbCrLf

    NestedFors = codeShell
End Function

Then, for example if you type

?nestedfors(50,4)

in the Immediate Window you get the following (which can be copy-pasted to a code window above:

For i = 1 To 50
    For j = i + 1 To 50
        For k = j + 1 To 50
            For l = k + 1 To 50
                '----- Insert code here ------
            Next l
        Next k
    Next j
Next i

Thank you for your answer. I think this is like code generator. But this is not what I need. — Roshantha De Mel, Oct 10 '16 at 18:50

user3598756 · Answer 3 · 2016-10-21T17:32:46.703

-2

edited to produce nested loops

Option Explicit

Sub main()
    Dim NumberOfForLoops As Long

    NumberOfForLoops = 3
    ForLoops NumberOfForLoops, 1, 50
End Sub

Sub ForLoops(nLoops As Long, jMin As Long, jMax As Long, Optional level As Long)
    Dim j As Long
    If level = 0 Then level = 1
    For j = jMin To jMax
        If level < nLoops Then
            ForLoops nLoops, jMin + 1, jMax, level + 1
        Else
            'your "someother" code
        End If
    Next j
End Sub

edited Oct 21 '16 at 17:32

answered Oct 09 '16 at 07:49

user3598756

28,893
4
18
28

this will not produce nested loops – h2so4 Oct 09 '16 at 18:53
@h2so4, you are right. Sorry for not having tested it and relying on "mental calculations" only. See revised code – user3598756 Oct 21 '16 at 17:34

score -3 · Answer 4 · answered Oct 09 '16 at 03:25

-3

If you want to run the first nested loop "NumberOfForLoops" times, just change the "To" part to NumberOfForLoops.

For i = 1 To 50

    For j = 1 To NumberOfForLoops


        For k = j + 1 To 50

            ..........................
            'Here I have someother code
            ..........................

        Next k

    Next j

Next i

answered Oct 09 '16 at 03:25

dya

190
1
13

this will not produce nested loops – h2so4 Oct 09 '16 at 18:52

Make the number of nested for loops a variable - VBA

4 Answers4