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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

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