Application.Calculation = xlCalculationManual not working

[Guest]

Hello,

I've got a bit of an Excel programming problem. I really need to avoid
having the worksheet recalculate until the macro I'm running is done. To do
so, I've been using the command:
Application.Calculation = xlCalculationManual
It has been working fine, too, until I added more calculated data (using a
UDF) to the worksheet. Now the function just does not work. Adding a watch to
Application.Calculation shows that the value does not change after it is
explicitly set to xlCalculationManual. And adding "Debug.Assert
Application.Calculation = xlCalculationManual" after setting the Calculation
mode always causes that line to trigger the debugger.

Does anyone have any idea what can be causing this?
Does Excel have any conditions that must be satisfied before the calculation
mode can be toggled?

Thanks,
David

 

[gootman]

Hello,

I've got a bit of an Excel programming problem. I really need to avoid
having the worksheet recalculate until the macro I'm running is done. To do
so, I've been using the command:
Application.Calculation =xlCalculationManual
It has been working fine, too, until I added more calculated data (using a
UDF) to the worksheet. Now the function just does not work. Adding a watch to
Application.Calculation shows that the value does not change after it is
explicitly set toxlCalculationManual. And adding "Debug.Assert
Application.Calculation =xlCalculationManual" after setting the Calculation
mode always causes that line to trigger the debugger.

Does anyone have any idea what can be causing this?
Does Excel have any conditions that must be satisfied before the calculation
mode can be toggled?

Thanks,
David

Feel free to disregard this call for help. I looked at some other
newgroup discussions about this topic and looks like nobody has any
idea whats going on with regards to this.

 

[Niek Otten]

Hi David,

I don't understand your question. If Calculation is set to Manual, then it is normal that the functions are not calculated, isn't
it?
What exactly do you mean with "the function doesn't work"?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
|
| I've got a bit of an Excel programming problem. I really need to avoid
| having the worksheet recalculate until the macro I'm running is done. To do
| so, I've been using the command:
| Application.Calculation = xlCalculationManual
| It has been working fine, too, until I added more calculated data (using a
| UDF) to the worksheet. Now the function just does not work. Adding a watch to
| Application.Calculation shows that the value does not change after it is
| explicitly set to xlCalculationManual. And adding "Debug.Assert
| Application.Calculation = xlCalculationManual" after setting the Calculation
| mode always causes that line to trigger the debugger.
|
| Does anyone have any idea what can be causing this?
| Does Excel have any conditions that must be satisfied before the calculation
| mode can be toggled?
|
| Thanks,
| David

 

[gootman]

Hello Niek,

The problem is that the statement "Application.Calculation =
xlCalculationManual" doesn't seem to do what it was supposed to.
For example, the following code:
Application.Calculation = xlCalculationManual
Debug.Assert Application.Calculation = xlCalculationManual
Would always cause the debugger to come up on the second statement.
It appears as though the statement 'Application.Calculation =
xlCalculationManual' does nothing for this case or doesn't get
executed appropriately. The calculation mode is not changed to Manual
after the statement is executed. This only happens sometimes, though,
and I can't determine the conditions involved in causing this problem.

David

 

[MagooChris]

Dear anyone who can help,

I have the same problem but in reverse. (using Excel 2003)

I need to make sure that Application.Calculation =
xlCalculationAutomatic

But every time I change the value, in the very next line,
Application.Calculation is back to the value it was before.

Any help will be greatly appreciated,
MagooChris

 

[David G]

How "complex" is the spreadsheet you are experiencing the
CalculationMode problem with?
I started to get the feeling that this problem is due to too many
dependencies between cells.
I still never got a straight answer regarding this issue.

David