Prevent Excel Calculating:...

Drofidnas

Board Regular
Joined
Jun 30, 2004
Messages
126
Not as easy as it sounds.

I have a large workbook with 60+ sheets.

One sheet pulls info from the others and displays a chart (updating the chart with Worksheet_Change(ByVal Target As Range)) as the data point changes.

I also have a status page which checks for errors and refreshes the result with Worksheet_Activate() every time the sheet is activated.

So there is a lot going on.

I originally had pages of formulas on every sheet and gradually have turned all of these into VBA making the whole workbook operate more quickly. I have macros which zip through every sheet copying data and moving it and all sorts of automations and these move like lightning! But whenever I use the Worksheet_Change(ByVal Target As Range) or _Activate() sheets I get Calculating: x% and it takes about 6 seconds to clear each time.

Weirdly, if I click the mouse, the calculation stops and the result is shown - so the information is there, it is just being held up by this spurious calculating which doesn't appear necessary.

Even with Calculation set to Manual I still get this. What is being calculated?

I have

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

at the start of every macro and the opposite at the end.

Is there something else I can do? Even simulating the mouse click!

Thanks for any help.

Chris
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
With calculation manual, clicking into a cell will recalculate JUST that cell. Are you sure that isn't what's happening?
 

Drofidnas

Board Regular
Joined
Jun 30, 2004
Messages
126
Not as far as I know.
I have a row of 24 values pulled from a particular sheet and they are all present no matter if I click randomly or wait for the calculation to stop.
 

Drofidnas

Board Regular
Joined
Jun 30, 2004
Messages
126
I've just removed a few Application.Calculation = xlCalculationAutomatic statements from key sheets and it seems to have worked.
I don't actually need auto calc because all my calculations are happening in VBA when I import data onto my sheets, I'm just changing a view on one sheet based on the data on the others, so I think I can live without it.

Thanks for your input.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,645
Members
430,150
Latest member
amitk1

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
Top