Excel prevent recalc on opening 2

[RobBroekhuis]

I have a workbook containing numerous function calls to an AddIn function, each of which takes some time to process. Since for the most part the inputs don't change, there is hardly ever a need to recalculate the majority of the cells. However, when I first open the workbook, Excel insists on recalculating all cells - which takes several minutes. I can prevent this by setting the calculation mode for the entire application to manual before opening the workbook, but that's not a workable approach for me. As far as I know, there is no workbook-level calculation setting. Is there anything I can do (e.g., in workbook_open) to prevent the recalc? It would be nice if the calculationstate property were writable...


Rob

 

[GlennUK]

If you switch calculation mode to Manual, save the workbook and close it, then the next time you open it the calculation mode will still be manual but only if the workbook is the first one to be opened in the Excel workspace.

So, having the calculation mode set to manual in the workbook close event might be worth doing if this sounds useful to you.

I hope that's of some help to you.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[RobBroekhuis]

Glenn - thanks, I wasn't aware of that feature. But I'm usually juggling several Excel tasks at once, seldom have a clean slate to start from - and frankly, I hate working in manual calc mode, and most likely would be frustrated by the need to turn auto calc back on when I'd open the next workbook.
So I'm still interested in hearing about other possible solutions.

Rob

 

[jcrater]

In the Workbook_Open event, use
Application.Calculation = xlManual

I haven't tested this. You might need to put it in each of your wookbooks. The other calculation properties are xlCalculationAutomatic and xlCalculationSemiautomatic.

 

[xlbo]

'lo Rob

Unfortunately, from XL help:

Excel also calculates workbooks each time they are opened.

I would be tempted to utilise Glenn's suggestion but with a slight twist.

Simply use the workbook Before Close event to set calc to manual, then save the workbook, then set back to automatic and then (finally) close the workbook WITHOUT saving

This should save the wb as manual calc but with the advantage that after closing it, the xl gets set back to auto calc automatically

I've looked into opening switches and none of them seem to do the job - other than that, you may get some joy out of
Sendkeys "{ESC}"
in the wb open event to INTERUPT the calculation rather than setting it to manual - you may then be able to get away with SHIFT+F9 for a sheet level recalc which should be quicker

My final query wiould be to do with your Addin and in particular its function(s) - does it have
Application.volatile
as a loine and does it need it - it may be that the function won't be calced on open if you don't use the Application.volatile line...

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[RobBroekhuis]

Geoff,
Thanks for your comments - your twist on Glenn's approach makes sense, and that may just be what I have to do. Unfortunately, the AddIn is not mine, so I can't control its behavior. Also unfortunately, this doesn't do what I really want it to do, which is tell Excel that "really, the calculations are up to date, relax!". Even with the manual recalc approach, any time I really need to update (a single cell), Excel feels the need to recalculate ALL the cells (the first time after opening the workbook, anyway).
Mr Gates, can we have a switch or writable workbook property?


Rob

 

[xlbo]

You may get away with SHIFT+F9 which calcs the active sheet only.....but yes more calc control would be useful

I think it is an intrinsic part of the app that it calculates on open whether or not it needs to so it is that causing the issue rather than the calculation properties within excel itself.

If the workbook didn't recalc ALL cells on open, there would be no issue I imagine ?!?

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question