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