Manual Calculation/Force Calculation Question

BBrandt

Board Regular
Joined
Jul 14, 2008
Messages
155
I have a workbook that periodically sets itself to calculate manually via (Application.Calculation = xlCalculationManual). Basically, users enter data and there are lots (thousands) of cells that are dependent on that data, so in the interest of not having Excel grind to a halt whenever anything changes, so the calculation is basically 'off' and the worksheet will (or rather, is supposed to) calculate as needed when I specify.

An example is that there is a worksheet, Sheet3, that needs to recalculate after the users have finished inputting data, but before a macro runs that does a series of iterative calculations based on the data in tables on Sheet3. What I did was simply to put the following two lines of code at the beginning of the macro:

Sheet1.Calculate 'this is the "input" sheet
Sheet3.Calculate 'this is a sheet that performs calculations based on the inputs in Sheet1, and needs to be updated in order for the rest of the macro to be correct

'rest of code

This doesn't seem to work consistently. Sometimes it works, sometimes it doesn't, and it needs to consistently work. Does Excel have to be in a certain calculation mode in order for that to work? For example, would the application being in xlCalculationAutomatic or xlCalculationManual stop this from firing?

Also, when the code hits that line, does it wait for the worksheet to calculate before moving on to the next line of code? If not, it needs to, so how would I make it do that?

Finally, is there generally a better way to force a worksheet to calculate on-demand?

Really stuck here, and since it doesn't seem to work/not work with any rhyme or reason, I'm getting quite frustrated to say the least. If anyone could share any insight into what's going on here, I'd greatly appreciate it.

Thanks
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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,167,651
Messages
5,854,956
Members
431,689
Latest member
jacker01

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