Trying to turn off auto-calculation in a macro

JohnnyC

New Member
Joined
Apr 14, 2002
Messages
10
I have a sub routine that runs really slowly unless the calculation method is set to manual. I can run the sub from the VB window OK (the sub is in Module 1) but if I try to run it from a button on a worksheet I get a "Method 'calculation' of object 'application' failed" error message.

The line of code I'm using is:

Application.Calculation = xlCalculationManual

This is really driving me mad. Can anyone help?

JohnnyC
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
It might be that your calculation is already set to manual. Might be worth trying this instead: -

With Application
If .Calculation <> xlCalculationManual Then
.Calculation = xlCalculationManual
End If
End With
 
Upvote 0
...just tried that. Calculation was not already set to manual, it runs through to the next line and gives the same error message.
 
Upvote 0
Sorry, this runs OK for me, whichever way I call it. Could you post the rest of your code, just to see if something else is causing it?
 
Upvote 0
OK, I've just put a line of code in front of the section you sent me which selects a different worksheet.

That was the only change I made and the macro runs fine now...

Thanks for your help anyway.
 
Upvote 0
On 2002-04-26 03:14, JohnnyC wrote:
OK, I've just put a line of code in front of the section you sent me which selects a different worksheet.

That was the only change I made and the macro runs fine now...

Thanks for your help anyway.

Sounds like you have excel97 ??
If so then anothr way around this is to
set your cmd button to;

TakeFocusOnclick = False
 
Upvote 0
Completely right. I am using Excel 97. That button focus thing must have caused me a LOT of problems!

Thanks for the tip.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top