0 votes
in VBA by Beginner (4 points)
I'm using nested for loops to do an optimization of 4 variables.

I've coded 4 For Loops, but I want the code to be dynamic and enable me to do any number of For Loops

rather than just the hard coded 4.
by Skilled (673 points)
Can you post the code and what is the purpose? More details will be useful for solving any problem.

1 Answer

+2 votes
by Skilled (279 points)
edited by

First, we must recognize that loop constructs are merely pointer-based flow controllers. In other words, every FOR...NEXT loop can be rewritten as an IF-THEN.

In the old days (circa 1980), we used FORTRAN, and made extensive use of labels and branching. You can do the same thing with VBA. Once you've correctly mapped the flow controllers with labels and branching, you can optimize it with FOR-NEXT.

The only real challenge is dynamic evaluation of the current variables, which is the whole point of using nested FOR...NEXT loops in the first place.

The attached code shows one way to handle the looping constructs. I leave it as an exercise for you to determine how to manipulate the current values of the variables -- I'm not even sure you can.

Here is the Sample File (XLSM). It has additional illustrations on the worksheet tab.

Cheers,

 

Mitch

 

[edited for additional info]

1. I didn't implement SystemLimit, once I realized that my formula for determining the number of iterations depends on minvalue, too. You can always create a simple counter that could stop the Iterate Subroutine if the SystemLimit is reached.

2. SystemLimit is arbitrary, but we all know Excel can hang with very large nested loops. Beware! (Don't try doing Factorials with this! LOL)

3. The real value of a Benchmark test is in verifying your transformations. In your case, Steve, you'd want to do your variable optimizations in the Benchmark subroutine, record the results as normal, then try to achieve the same thing in the Iterate and/or YourFunction procedures.

4. Instead of using Redim vars (1 to 3) as VarDef, use Redim vars(1 to MaxVariables)

Option Explicit

' Dynamically nested iterations

    Const MaxVariables = 4 ' Presumably, each variable has its own lower and upper limits, which will be defined later
    Const SystemLimit = 25000 ' If the combined iterations exceed this limit, a warning will be displayed
    Public vars() As VarDef
    
    Type VarDef
        minvalue As Long
        maxvalue As Long
        ptr As Long ' Used to track current value
        stepvalue As Long ' Amount by which to increment pointer each interval
    End Type

Public Sub Nest3()

    ReDim vars(1 To 3) As VarDef
        
        ' Define Variable 1
        vars(1).minvalue = 1
        vars(1).maxvalue = 4
        vars(1).stepvalue = 1
        
        ' Define Variable 2
        vars(2).minvalue = 1
        vars(2).maxvalue = 5
        vars(2).stepvalue = 1
        
        ' Define Variable 3
        vars(3).minvalue = 10
        vars(3).maxvalue = 50
        vars(3).stepvalue = 12
        
        ' Uncomment next line to compare your dynamic variables with a hard-coded benchmark.
        ' If you need to compare with different levels, write separate Benchmark subroutines.
        
        ' Benchmark3
        
        Iterate
        
End Sub


Public Sub Iterate()

    ' This procedure iterates over every variable in the array, using min, max and step to control interval and limit
    ' To run tests, place cursor somewhere in this procedure and press the F5 key.
    
    Dim FirstVar As Long
    Dim LastVar As Long
    Dim IterationLimit As Long
    Dim i As Long ' internal loop counter
    
    Dim Done As Boolean ' Tells this procedure when all variables have been completed
    Dim Result As Long ' Evaluation of current value of current variable (change to Double if needed)
    Dim SaveValue As Long ' Cumulative value of results over all variables (change to Double if needed)
    Dim StepOut As Long ' Tracks the nesting level
    
    ' Set first and last variable pointers
    FirstVar = 1
    LastVar = UBound(vars)
    
    ' Reset all pointers to respective minvalues
    For i = FirstVar To LastVar
        vars(i).ptr = vars(i).minvalue
    Next i
    Done = False
    
    Do While Not Done
    
        ' Initiate each level of the iteration
        For i = LastVar To FirstVar Step -1
            Result = YourFunction(vars(i).ptr)
            
            ' Do something with the result
            ' This example attempt to preserve the pointers in a large number, so that you can easily see the changes.
            If i = LastVar Then
                SaveValue = Result
            Else
                SaveValue = (Result * 10 ^ ((LastVar + 1) - i)) + SaveValue
            End If
        Next i
        
        ' Display saved value, or store it somewhere (cell reference, text file, etc.)
        ' This example uses the Immediate window (Ctrl+G)
        Debug.Print SaveValue
        
        ' Increment the innermost loop pointer, cascading outward as each loop completes.
        StepOut = 0
        For i = LastVar To FirstVar Step -1
            vars(i).ptr = vars(i).ptr + vars(i).stepvalue
            If vars(i).ptr <= vars(i).maxvalue Then
                Exit For
            Else
                vars(i).ptr = vars(i).minvalue
                StepOut = StepOut + 1
            End If
            
        Next i
        
        ' "Done" is the internal iteration control flag, "StepOut" is a level counter
        ' StepOut will equal LastVar if all ptrs have returned to minvalue, indicating that iteration is complete.
        If StepOut = LastVar Then
            Done = True
        End If
    Loop
    
End Sub

Public Function YourFunction(x As Long)
    
    ' Here is where you process your variable.
    ' This is a very simple version.
    ' You may want to pass other pointers in x, before calling the function.
    
    ' Additionally, you may want to post-process the result in the SaveValue variable in the Iterate procedure.
    
    YourFunction = x

End Function


Public Sub Benchmark3()

    ' This generates the output of a 3-level nested loop, using hard-coded FOR...NEXT structures.
    ' Compare this against the output of Iterate, to ensure proper functioning of dynamic iteration.
    
    Dim i As Long
    Dim j As Long
    Dim k As Long
    
    For i = vars(1).minvalue To vars(1).maxvalue Step vars(1).stepvalue
        For j = vars(2).minvalue To vars(2).maxvalue Step vars(2).stepvalue
            For k = vars(3).minvalue To vars(3).maxvalue Step vars(3).stepvalue
                Debug.Print i, j, k
            Next k
        Next j
    Next i
                
End Sub

 

by Beginner (4 points)
Thanks so much, Mitch.  There's a lot here that is new to me so

it's going to take some time for me to follow and implement.  I

really appreciate your time on this.
by Skilled (279 points)
You're welcome, Steve. I hope it inspires some creative solutions to your challenge!

 

Cheers,

 

Mitch
by Skilled (279 points)
+1
Hi Steve,

Ryan showed me how to upload the sample file. I edited the answer to include the link "Sample File" near the top. Hopefully, it will illuminate the concepts more clearly.

 

Cheers,

 

Mitch

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...