Hi Chris,
Check Charles William's site:
www.decisionmodels.com, especially the part about Application.CalculationState in the Calculating
from VBA part on the Calculation secrets chapter on the Excel Pages tab (pfffffff........)
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"christian_spaceman" wrote in message
...
| 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
|
|
|
|
|