nested nested nested loops

[beetonthebrat]

I'm trying to speed up my code. I understand there are many, MANY combinations that I'm looping through but want to know if there's anything I can do to help.

Sub casey()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Dim a, b, c, d, e, f, g, h, i As Integer
For a = 1 To 500
For b = 1 To 500
For c = 1 To 500
For d = 1 To 500
For e = 1 To 500
For f = 1 To 500
For g = 1 To 500
For h = 1 To 500
For i = 1 To 500
a:

If Evaluate(a + b - c) = Evaluate(d ^ 2 + e - f) And Evaluate(d ^ 2 + e - f) = Evaluate(g + h + i)  Then
Exit Sub
End If
DoEvents
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a
GoTo a
End Sub

I would think using VBA for calculating instead of spreadsheets and the evaluate function would make this go very quickly but it's been running for hours. Any help would be appreciated.

 

[MUKESHY12390]

Sub casey()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Dim a, b, c, d, e, f, g, h, i As Integer
For a = 1 To 500
For b = 1 To 500
For c = 1 To 500
For d = 1 To 500
For e = 1 To 500
For f = 1 To 500
For g = 1 To 500
For h = 1 To 500
For i = 1 To 500
a:

If Evaluate(a + b - c) = Evaluate(d ^ 2 + e - f) And Evaluate(d ^ 2 + e - f) = Evaluate(g + h + i) Then
Exit Sub
End If
DoEvents
Next i
Next h
Next g
Next f
Next e
Next d
Next c
Next b
Next a
GoTo a
End Sub


I think you need to put one exit commond before Goto a line...

 

[Rick Rothstein]

1) Since your a, b, c, etc. variables are all simple numbers, you do not need to slow the calculations down by using the Evaluate function... just remove them and let VBA do the simple math internally.

2) HOWEVER, you should (maybe even must) change each of those variable declarations from Integer to Long as I think you could run into a calculation quirk regarding the upper limit an Integer value when calculating with only Integer declared values.

3) DoEvents slows down loops each time it is executed (because to cedes control back to the computer and lets it execute any pending background operations) and you are executing it with every iteration of the inner loop. Removing it will speeds things up, but you will lose control of your computer while the loop calculates if you remove it. How long will that be?...

4) Even if you do each of the above, you are in for a long, long, long, long (you can put a few dozen more "longs" in there) wait until the calculation finishes. Why? Well, you have nine nested loops of 500 iterations each which means your main calculation will be executed 1,953,125,000,000,000,000,000,000 times! That is a real, real, real (you can put in a few dozen more "reals" in there) lot of times!!!!

5) I also do not understand your GoTo a command... you are jumping into the middle of a nested loop from outside all of the loops?

 

[Eric W]

Welcome to the forum.

A few thoughts:

1) You don't need to add the Evaluate. VBA can calculate a + b - c fine without it, and the other arithmetic expressions.
2) The DoEvents line would be needed if you plan to use the break key, but it would slow down the processing. You could only do the DoEvents every 100 iterations maybe, which would speed up the loop and still provide the option to break the code.
3) The GoTo a line makes no sense. After all the loops are complete, why do you want to jump back into the middle of a loop?
4) If you use Dim a, b as integer, you'll only define b as an integer, a will be defined as Variant. To define all the indices as integers, you need to do:
Dim a as integer, b as integer, c as integer, etc.

5) Unless you're very lucky and hit a match, that loop will NEVER end!! The total number of loops is 500^9, which is an enormous number! I did a quick timing test on my PC, and I ran a simple loop with nothing in it, and timed it for a billion iterations. Taking that time and extrapolating it to 500^9, it worked out to around 372 million years to finish. Your loop with actual calculations would take much longer.

I'd suggest looking up Diophantine equations and try to find the answer that way.

Hope this helps!


Props to Rick for answering first. I think my item 4 hasn't been mentioned yet though.

 

[beetonthebrat]

That was from an earlier edit that I forgot to remove. It shouldn't matter if it's there if my criteria is met. Thanks for your reply

 

[beetonthebrat]

Thanks for all your replies. The goto a is redundant and shouldn't be there. I never realized that separating variables with commas would only dim them and not declare the type! thank you for that!

I'll try moving the doevents after next h. however, I don't think I will gain a significant amount of time by doing so. I guess I'm looking for another way to word this or a different function I could use to cycle through so many variables..

Thanks again!!

 

[Eric W]

a = 4
b = 1
c = 1
d = 2
e = 1
f = 1
g = 2
h = 1
i = 1

I believe that's a solution to your equations. It just took a little observation, and a little algebra. If the variables are unbounded, there will be an infinite number of solutions. If the variables are integers between 1 and 500, there will still be thousands. If you want to enumerate them (why, I don't know), you could iterate on d, e, and f, since the d^2+e-f term appears twice in your equations. d would only need to go up to about 39, since 39^2 = 1521, and if a,b,c are bounded at 500, then no possible values of a,b,c could sum to 39^2 or greater. Then given a value of d^2+e-f, you could run another set of loops for a,b,c. With an upper limit of 500, and a little algebra, you can set the upper and lower bounds of the For statements so that you don't need to look at every combination. Do the same thing for the g,h,i values.

Now instead of having 9 loops of 500, you'd have outer loops of 39, 500, 500, and inner loops bounded by d^2+e-f in some fashion. You'd probably be able to limit the outer loops too.

This is some basic Diophantine analysis.

 

[beetonthebrat]

the equations weren't exactly what I have in my code. Just an example to see if I could be doing something different. It sounds like I may have better odds with a random function. Thanks again for all your replies.