Run Macro to Update Cells

Question

I have quite a few cells that link to other worksheets/books, etc with standard excel formulae. However, is there a macro I can run that will only allow these cells to be updated when it is run? I'd like them to retain their previous values until I want them to update.

Answer 1

There are some options you can set in Tools -> Options -> Calculation tab (this for Excel 2003, not sure about 2007). I'm not sure if there's any way you can edit them in VBA or not...

The settings affect workbooks that are open at the time of the settings change. If you set to Manual Calculation, the formulae only update when you press F9, or you can update the current sheet with Shift + F9.

Hope this helps.

[edit] Did some more reading, looks like you can adjust this using VBA:

Application.Calculation = (xlCalculationAutomatic | xlCalculationManual | xlCalculationSemiAutomatic)

There is also a CalculateBeforeSave property that does what it sounds like it does...

Answer 2

That's great, thanks! I've got it to set the automatic calculation to 'manual', then simply sun the command 'calculate' to update the cells.

Cheers, Jon