Excel Choking on my Script

[DarkF1ame]

Hello All,

When I run this particular script with small values on one side of the equation it normally takes a lot longer to process than usual seeing as the right side of the equation has a 5th power in it. I recently switched from Excel 2007 where I codes this to Excel 2010 on my new computer, so if there is something hindering it with that change please let me know. The problem I am getting is that Excel stalls when I have a small value on the left and I get a "Run-time error 1004. Method 'Range' of object '_Worksheet' failed" error. I am assuming that reporting the values after every iteration the way it does is the culprit behind this, or just the sheer computing power. Any help would be greatly appreciated.

Please Login or Register  to view this content.

It seems to be choking on this line of code:

Please Login or Register  to view this content.

Thank you all again for your help!

DarkF1ame

[TMS]

I'd guess, and it is only a guess, that because the starting value is small, each iteration is adjusted by a very small value, perhaps giving something like an exponential curve approaching the target very, very slowly.

Perhaps you need to add a limit to the number of iterations ... on the basis that if you've gone through the loop a million times and it isn't getting much closer, that's gonna be close enough.


Regards, TMS

[Richard Buttrey]

Hi,

What's the value of Abs(d_computed) when you get the error message?

Are you able to upload the workbook here?

[DarkF1ame]

Pipe_Calculator_Blank_Advanced.xlsm
Attached is the workbook. The macros are password protected but I have already pasted the applicable sections above.

[DarkF1ame]

Hello All,

I still have yet to find a solution, does anyone know why this only happens in excel 2010 and not 2007?

Thanks,

[Richard Buttrey]

Hi,

Are 2010 and 2007 on the same PC? If not are there differences in the processor / ram?

You need to definitively show that it's Excel software and not hardware and the only way is to run the process on the same machine with both 2007 & 2010. I'm not aware of any inherent differences between 2007 & 2010 in any of the areas your macro uses.

[DarkF1ame]

I was running 2007 on a crappy laptop and I'm running 2010 on my new beastly tower. If there are any hardware changes they are all for the better. Thanks, I was thinking maybe the office suite had changed subtly. My 2007 machine will keep cycling through variables for as long as 10 minutes before freezing/crashing, and my new 2010 machine can't last more than 30 seconds. It seems odd. Thanks for the response though.

[TMS]

I'm testing your script on a 64 bit Windows 7, 32 bit Excel 2007 on a Sony Laptop.

I've modified your code to put an exit counter into each loop (as I suggested earlier). For the sake of testing, each loop will drop out at 20,000 iterations. It produces Debug output every 1000 iterations.

This is the amended code:

Please Login or Register  to view this content.

and this is the output in the Immediate Window:

Please Login or Register  to view this content.

As you can see, I get no output from the second loop and only one entry for the third loop. However, loops one and four go all the way ... and would, no doubt, go a lot further. In fact, I stopped the first loop after 30,000 iterations in early testing.

I don't understand the calculations so I can't interpret the result. But, they are oscillating and, possibly, may never meet the exit criterion. Hence the reason for the "get out of jail" Exit Counter. This works but I really don't know if the results are then valid.

See the amended workbook.

Regards, TMS

[Richard Buttrey]

I'm testing your script on a 64 bit Windows 7, 32 bit Excel 2007 on a Sony Laptop.

I've modified your code to put an exit counter into each loop (as I suggested earlier). For the sake of testing, each loop will drop out at 20,000 iterations. It produces Debug output every 1000 iterations.

This is the amended code:


Regards, TMS

That's a nice analysis. I like it and the logical analysis that stems from it. Well done.

[TMS]

@Richard: thanks for the feedback and rep