There is a new article on the same subject here – https://www.vitoshacademy.com/vba-avoid-nested-loops-with-recursion-part-2/
Everyone knows how ugly it looks from a programming point of view to have 5 or 6 nested loops. But what can you do to avoid this? The answer is pretty simple – try to use recursion.
Imagine that you have to show all four digit combinations of the digits from 1 to 6. Something like this:
The trivial method is to do 4 nested loops and to run them. That is probably the only one I knew before. However, if you would like to use recursion to do it, the code becomes better. And if you want to use N nested loops, the code makes it possible.
Here it comes:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
Option Explicit Sub Main() Static size As Long Static c As Variant Static arr As Variant Static n As Long size = 2 c = Array(1, 2, 3, 4, 5, 6) n = UBound(c) + 1 ReDim arr(size - 1) EmbeddedLoops 0, size, c, n, arr End Sub Function EmbeddedLoops(index, k, c, n, arr) Dim i As Variant If index >= k Then PrintArrayOnSingleLine arr Else For Each i In c arr(index) = i EmbeddedLoops index + 1, k, c, n, arr Next i End If End Function Public Sub PrintArrayOnSingleLine(myArray As Variant) Dim counter As Integer Dim textArray As String For counter = LBound(myArray) To UBound(myArray) textArray = textArray & myArray(counter) Next counter Debug.Print textArray End Sub |
Also in GitHub.
Enjoy it! 😀