Excel Range calculation breaking application.calculate - horrifyingproblem
 

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

Excel Range calculation breaking application.calculate - horrifying problem
 

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
|
|
|
|
|

Excel Range calculation breaking application.calculate - horrifying problem
 

What do you mean by:
"Does not work"

Does it crash with/without an error message?
Does it not do something you expect?
If so: what does it not do?

What version and update level of Excel are you using?

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"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

Excel Range calculation breaking application.calculate -horrifying problem
 

Hi, sorry, that may not have been helpful.

I've not been able to pin down the responses - there appear to be two.
The first response is on the second application.calcation (the one
that doesn't work), the CPU rams up to 100% and stays there until
excel crashes. Another behaviour that happens is that the code simply
skips over the calculation *without actually calculating* anything.

I acn't say for sure what causes the difference in the responses - it
appears that the skipping response happens if the second
application.calculate is placed later in the code (for example outside
of the loop). Like I say, I can't say for sure.

Thanks for the responses.

Chris


On Apr 30, 12:26*pm, "Charles Williams"
wrote:
What do you mean by:
"Does not work"

Does it crash with/without an error message?
Does it not do something you expect?
If so: what does it not do?

What version and update level of Excel are you using?

regards
Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

"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- Hide quoted text -

- Show quoted text -

Excel Range calculation breaking application.calculate -horrifying problem
 

sorry again - excel 2003 SP3, VBA 6.3


On Apr 30, 12:26*pm, "Charles Williams"
wrote:
What do you mean by:
"Does not work"

Does it crash with/without an error message?
Does it not do something you expect?
If so: what does it not do?

What version and update level of Excel are you using?

regards
Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

"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- Hide quoted text -

- Show quoted text -

Excel Range calculation breaking application.calculate - horrifying problem
 

sounds like the dependency trees may be getting corrupted.

(I am assuming you understand the different way Range.Calculate,
sheet.calculate and application.calculate work, and the limitations of
Range.Calculate, if not check out
http://www.decisionmodels.com/calcsecretsg.htm
http://www.decisionmodels.com/calcsecretsh.htm
etc.)

I would try an Application.Calculatefull or Application.Calculatefullrebuild
before you start and after you have finished.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"christian_spaceman" wrote in message
...
Hi, sorry, that may not have been helpful.

I've not been able to pin down the responses - there appear to be two.
The first response is on the second application.calcation (the one
that doesn't work), the CPU rams up to 100% and stays there until
excel crashes. Another behaviour that happens is that the code simply
skips over the calculation *without actually calculating* anything.

I acn't say for sure what causes the difference in the responses - it
appears that the skipping response happens if the second
application.calculate is placed later in the code (for example outside
of the loop). Like I say, I can't say for sure.

Thanks for the responses.

Chris


On Apr 30, 12:26 pm, "Charles Williams"
wrote:
What do you mean by:
"Does not work"

Does it crash with/without an error message?
Does it not do something you expect?
If so: what does it not do?

What version and update level of Excel are you using?

regards
Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

"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- Hide quoted text -

- Show quoted text -

Excel Range calculation breaking application.calculate -horrifying problem
 

Thanks Charles,

there's certainly something unpleasant going on. I had a look through
your site and found the following:

Dim oSht As Worksheet

For Each oSht In ValuationWorkbook.Worksheets
oSht.EnableCalculation = False
oSht.EnableCalculation = True
Next oSheet

Calling this before every application.calculate appears to work nicely
- though it seems like a bit of a hack!. I'll have a look through
those other pages you link and see if I can't think of something more
elegant before settling on this.

Many thanks for your help,

Chris


On 30 Apr, 13:13, "Charles Williams"
wrote:
sounds like the dependency trees may be getting corrupted.

(I am assuming you understand the different way Range.Calculate,
sheet.calculate and application.calculate work, and the limitations of
Range.Calculate, if not check outhttp://www.decisionmodels.com/calcsecretsg.htmhttp://www.decisionmodels.com/calcsecretsh.htm
etc.)

I would try an Application.Calculatefull or Application.Calculatefullrebuild
before you start and after you have finished.

Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

"christian_spaceman" wrote in message

...
Hi, sorry, that may not have been helpful.

I've not been able to pin down the responses - there appear to be two.
The first response is on the second application.calcation (the one
that doesn't work), the CPU rams up to 100% and stays there until
excel crashes. Another behaviour that happens is that the code simply
skips over the calculation *without actually calculating* anything.

I acn't say for sure what causes the difference in the responses - it
appears that the skipping response happens if the second
application.calculate is placed later in the code (for example outside
of the loop). Like I say, I can't say for sure.

Thanks for the responses.

Chris

On Apr 30, 12:26 pm, "Charles Williams"
wrote:



What do you mean by:
"Does not work"

Does it crash with/without an error message?
Does it not do something you expect?
If so: what does it not do?

What version and update level of Excel are you using?

regards
Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

"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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Excel Range calculation breaking application.calculate - horrifying problem
 

You can probably get the same effect with Application.CalculateFull

"christian_spaceman" wrote in message
...
Thanks Charles,

there's certainly something unpleasant going on. I had a look through
your site and found the following:

Dim oSht As Worksheet

For Each oSht In ValuationWorkbook.Worksheets
oSht.EnableCalculation = False
oSht.EnableCalculation = True
Next oSheet

Calling this before every application.calculate appears to work nicely
- though it seems like a bit of a hack!. I'll have a look through
those other pages you link and see if I can't think of something more
elegant before settling on this.

Many thanks for your help,

Chris


On 30 Apr, 13:13, "Charles Williams"
wrote:
sounds like the dependency trees may be getting corrupted.

(I am assuming you understand the different way Range.Calculate,
sheet.calculate and application.calculate work, and the limitations of
Range.Calculate, if not check
outhttp://www.decisionmodels.com/calcsecretsg.htmhttp://www.decisionmodels.com/calcsecretsh.htm
etc.)

I would try an Application.Calculatefull or
Application.Calculatefullrebuild
before you start and after you have finished.

Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

"christian_spaceman" wrote in message

...
Hi, sorry, that may not have been helpful.

I've not been able to pin down the responses - there appear to be two.
The first response is on the second application.calcation (the one
that doesn't work), the CPU rams up to 100% and stays there until
excel crashes. Another behaviour that happens is that the code simply
skips over the calculation *without actually calculating* anything.

I acn't say for sure what causes the difference in the responses - it
appears that the skipping response happens if the second
application.calculate is placed later in the code (for example outside
of the loop). Like I say, I can't say for sure.

Thanks for the responses.

Chris

On Apr 30, 12:26 pm, "Charles Williams"
wrote:



What do you mean by:
"Does not work"

Does it crash with/without an error message?
Does it not do something you expect?
If so: what does it not do?

What version and update level of Excel are you using?

regards
Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

"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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Excel Range calculation breaking application.calculate -horrifying problem
 

Your're right - I stuck the application.calculatefullrebuild straight
after the loop (with the range calculations) and it appears to now be
going on with no problems.

Again many thanks for your help




On 30 Apr, 14:55, "Charles Williams"
wrote:
You can probably get the same effect with Application.CalculateFull

"christian_spaceman" wrote in message

...



Thanks Charles,

there's certainly something unpleasant going on. I had a look through
your site and found the following:

* *Dim oSht As Worksheet

* *For Each oSht In ValuationWorkbook.Worksheets
* * * *oSht.EnableCalculation = False
* * * *oSht.EnableCalculation = True
* *Next oSheet

Calling this before every application.calculate appears to work nicely
- though it seems like a bit of a hack!. I'll have a look through
those other pages you link and see if I can't think of something more
elegant before settling on this.

Many thanks for your help,

Chris

On 30 Apr, 13:13, "Charles Williams"
wrote:
sounds like the dependency trees may be getting corrupted.

(I am assuming you understand the different way Range.Calculate,
sheet.calculate and application.calculate work, and the limitations of
Range.Calculate, if not check
outhttp://www.decisionmodels.com/calcsecretsg.htmhttp://www.decisionmode...
etc.)

I would try an Application.Calculatefull or
Application.Calculatefullrebuild
before you start and after you have finished.

Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

"christian_spaceman" wrote in message

....
Hi, sorry, that may not have been helpful.

I've not been able to pin down the responses - there appear to be two.
The first response is on the second application.calcation (the one
that doesn't work), the CPU rams up to 100% and stays there until
excel crashes. Another behaviour that happens is that the code simply
skips over the calculation *without actually calculating* anything.

I acn't say for sure what causes the difference in the responses - it
appears that the skipping response happens if the second
application.calculate is placed later in the code (for example outside
of the loop). Like I say, I can't say for sure.

Thanks for the responses.

Chris

On Apr 30, 12:26 pm, "Charles Williams"
wrote:

What do you mean by:
"Does not work"

Does it crash with/without an error message?
Does it not do something you expect?
If so: what does it not do?

What version and update level of Excel are you using?

regards
Charles
__________________________________________________
The Excel Calculation Sitehttp://www.decisionmodels.com

"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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -