I have a complex project in `Microsoft Office Excel 2007' which utilises a large number of UDFs. Through VBA in a <strong>Workbook_Open</strong> Event, I set Excel Automatic calculation to OFF and a strategically placed <strong>Calculate</strong> method to manually calculate the cells whenever I need it so that the UDF doesn't perform recalculation unintentionally.
If the workbook is the <strong>ONLY</strong> one opened (or the <strong>first</strong> to open) in an Excel instance, everything works perfect. Only when it's opened <strong>AFTER</strong> another workbook (within the same instance), my project will inherit the Automatic calculation setting from the <strong>FIRST</strong> workbook and perform endless calculation on my UDFs. The disable code placed in the Workbook_Open event isn't executed until the UDF finishes the calculation (which can take forever). This only happens if my project is <strong>NOT</strong> the one opened first.
Through http://www.decisionmodels.com/calcsecretse.htm, I discover that it is the nature of Excel to perform the calculation process <strong>BEFORE</strong> the Workbook_Open event is executed.
So the question I have obviously relates to the project being opened <strong>AFTER</strong> another workbook is opened with automatic calculation turned ON:<ol> <li>How do I force my project to <strong>disable</strong> automatic calculation without it performing recalculation first (remember, problem only occurs when the project not the first one to be opened since it will follow settings from previously opened workbook) <strong>OR</strong>...</li> <li>How do I get the project to open in ANOTHER INSTANCE (when double clicked) to avoid inheriting automatic calculation setting from the previous workbook.</li> </ol>
Either way, the answer I'm seeking is for the project to open without performing the calculation first.
One way is to use a different workbook (Opener.xls) to initiate opening the UDF workbook (udf.xls)
in Opener.xls the Workbook_Open code - sets calculation to manual - opens udf.xls
In your question I don't recognize the way you use to change and inherit that option to your workbooks, But I answer it as a solution:
Use VBA and running VBA macros to change that option for just your active sheet as soon as you need to calculate; by using it like this:
With ActiveSheet .EnableCalculation = False .EnableCalculation = True .Calculate End With
In another ways that may you need, you can read this part of MSDN article.