Solved: Creating a Dynamic Nested ForLoop (ie.variable number of nests) Recursive functions?

[kemos]

Hi everyone,

I need to solve a mathematical problem by numerical methods, unfortunately, I am still new to VBA and the Solver add-in has been of no use to me.

I have 3 possible variables, each variable is a percentage (in actual fact I have 12 variables, 3 just looks neater in the code below). Sometimes I have less than this. As a result, I might need 3 For...Loops or I might need 1.

I need to run through every combinationof variables, until a condition on my spreadsheet is satisfied. For example, cell A1 = 0

Below is essence of my idea, but it doesn't work as it keeps jumping between the loops. The array intIsPercetangeZero() tells me how many variables there are.

I'm not sure if this is possible on VBA as it is. Is there a way to do this using a recursive function?

Thanks in advance!




Dim intStart (1 to 3) as integer
Dim intEnd(1 to 3) as integer
Dim intIsPercetangeZero(1 to 3) as integer
Dim p1 as integer
Dim p2 as integer
Dim p3 as integer
Dim intMyInteger as integer

For intMyInteger = 1 to 3
If intIsPercetangeZero(intMyinteger) = 0 Then
intStart(intMyInteger)=0
intEnd(intMyInteger)=0
Else
intStart(intMyInteger)=0
intEnd(intMyInteger)=100
End If
Next intMyInteger






For p1 = intStart(1) to intEnd(1)
For p2 = intStart(2) to intEnd(2)
For p3 = intStart(3) to intEnd(3)
If (p1/100)+(p2/100)+(p3/100) = 1 Then 'The sum of percentages must always equal 100%

Range("B1").value = p1/100

Range("C1").value = p2/100

Range("D1").value = p3/100


If Range("A1").value = 0 Then
Exit For
End If
End If






Next p3



Next p2



Next p1

[p45cal]

Recursion probably will be the way to go.
A few points first though:
1. As it stands, intStart and intEnd arrays are all initialised to have values of 0 throughout, so not may iterations (none) at the moment.
2. If (p1 / 100) + (p2 / 100) + (p3 / 100) = 1 can probably be simplified to If p1 + p2 + p3 = 100
3. What's the formula in A1? I'd be concerned, if the worksheet takes significant tme to recalculate, that the value in A1 won't be there in time for vba to look at it. It would be preferable to get vba to calculate it, otherwise (if it's very complex) we'll have to make sure we wait for calculation to finish before assessing A1.
4. The innermost loop (p3) can be eliminated by setting p3 to 100-p1-p2. This will cut down execution time. in fact, when you have more variables, as you're about to go into each next nested loop down, you can test for their sum exceeding 100 to allow you to jump out of a loop to the loop above which will save significant execution time too, since with 12 variables, it might take a long time.

ps. is here a typo in this line?:
For p1 = intStart(1) To intEnd(2)

[kemos]

Hi p45cal. Thanks for replying.

I'm not sure how to build a recursive function. It was only mentioned to me by a friend. There's not much on Google on building recursive functions for dynamic nested for loops.

1. My array intIsPercetangeZero() will have values depending on the situation. So a non-zero element of intIsPercetangeZero() will cause intEnd()=100 creating a loop.

2. Good point!

3. I see. Would the line "If Range("A1").value = 0 Then" execute before the sheet is calculating? It is actually very complex, the whole spreadsheet is about 30megs with minimal formatting. I am actually in the process of putting it all on VBA though and the calculation time would be greatly reduced.
4. Noted. Another good point.

PS: Yeh typo. Will edit it out ;)

Any pointers on how I could get this beast into a recursive function?

[p45cal]

3. Yes, I've come across this a few times, there is a command which holds up code execution until a sheet/workbook has finiished calculating but I'd have to seek it out.

Recursion - I can't do this for you right now, but the essentials to a recursive sub are:

Sub recurse(a,b,c)
Test for early exit
action (usually changing all or some of a,b,c)
call recurse (a,b,c)
end sub

Ie. two ways of exiting the sub and a line calling itself. a,b and c are variables, but versions of them will be retained for each call.
The 'action' and 'call' lines don't need to be in that order.

[kemos]

Thanks p45cal . I'll give this a go in the morning, my brain is dead now and other work is calling. It seems point 3. could be an issue until everything is up in VBA.

I'll post some code up in the morning, whether or not it works is another matter!

[p45cal]

As a taster, below are three routines; the recursive sub, a test and a print routine. Run the test sub and observe the immediate pane. (Ctrl + Break to halt)
Adjust depth in the test sub to see the effect.

Sub RecurseMe(a, v, depth)
If a > depth Then
PrintV v
Exit Sub
End If
For x = 1 To 10
v(a) = x
a = a + 1
RecurseMe a, v, depth
a = a - 1
Next x
End Sub

Sub PrintV(v)
For j = 1 To UBound(v):Debug.Print v(j) & " ";:Next j
Debug.Print
End Sub

Sub test()
Dim v()
depth = 5 'adjust
a = 1
ReDim v(1 To depth)
RecurseMe a, v, depth
End Sub

[kemos]

Thanks a lot p45cal.

I've had a look at the code and the first sub is more complicated than it looks! Very elegant!

I haven't had time to tweak it to my needs as yet but I will need to soon. Will post my code here when I do.

Thanks again! :D

[Paul_Hossler]

A cautionary note, (OK 2)

1. Always make sure that you have a way to stop recurrsing, or you're be cursing. That's what the depth variable does in p45cal's example

2. It is possible to run out of stack space if there is too many recursive calls.

In my experience, #1 always causes #2, but #2 happens.

Paul

[kemos]

Hi Paul,

Yes, i've realised the importance of that, albeit the hard way. I got stuck in an infinite loop a couple of times while playing around with the code!

Unfortunately, I have to abandon this method of solving my initial problem due to the fact that VBA and my PC are not fast enough. (I will pick it up again when I learn C++ and get a super computer ;-))

Instead I have switched to optimisation methods such as hill-climbing.
[en.wikipedia.org/wiki/Hill_climbing]

Thanks though for everyone's help. It was much appreciated and I have saved this useful bit of code for future reference. Consider this case solved!