Prevent Excel Calculating:...

Drofidnas

Board Regular
Joined
Jun 30, 2004
Messages
125
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:

Some videos you may like

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.

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

Watch MrExcel Video

Forum statistics

Threads
1,108,509
Messages
5,523,313
Members
409,511
Latest member
hitesh222002

This Week's Hot Topics

Top