Hi,

I've got a horrible Excel / vba problem. I run a simulation model
which basically loops through some code several thousand times - each
time doing a calculation of a few worksheets. Its important to
optimise the calculations, as they take forever otherwise. The
optimisation process went like this:

(1)
start loop
application.calculate
end loop

this became:

(2)
start loop
calculate only the sheets needed
end loop

this became

(3)
start loop
calculate only the ranges needed on the sheets needed
end loop


approach (3) was orders of magnitude faster, however it appears to
have issue that it breaks application.calculate, which is need
elsewhere in the code (after the loop). Code snippet below:

***
Start Loop

' useful code
'calculate only needed ranges
.Worksheets("Sim Curves").Range("B6:CH34").Calculate
.Worksheets("Sim Curves").Range("B39:CH66").Calculate

' useful code
End loop

application.calculate ' <--- this calculate works
application.calculate ' <--- this one doesn't
***


The second application.calculate doesn't work. In addition, a
sheets.calculate also doesn't work, though a range.calculate does.

It gets worse. Consider the following code snippet:

***
Start Loop

' useful code
'calculate only needed ranges
.application.calculate ' calc(1)
.Worksheets("Sim Curves").Range("B6:CH34").Calculate
.Worksheets("Sim Curves").Range("B39:CH66").Calculate
. application.calculate ' calc(2)
' useful code
End loop

application.calculate ' <--- this calculate works
application.calculate ' <--- this one also works
***

In this case, application.calculate doesn't break. If you try to
remove calc(1) however, it would. Obviously if both (1) and (2) are
removed, we're in the same position as earlier and calculation breaks.

I have no idea where to begin with this, it seems totally illogical.
Any help would be very greatfully received.

Cheers

Chris