CODEWITHSUNDEEP
vbaexcelexcel-2007user-defined-functions
arnodys
arnodys

Reputation: 21

Excel Automatic Calculation Setting

I have a complex project in `Microsoft Office Excel 2007' which utilises a large number of UDFs. Through VBA in a Workbook_Open Event, I set Excel Automatic calculation to OFF and a strategically placed Calculate method to manually calculate the cells whenever I need it so that the UDF doesn't perform recalculation unintentionally.

If the workbook is the ONLY one opened (or the first to open) in an Excel instance, everything works perfect. Only when it's opened AFTER another workbook (within the same instance), my project will inherit the Automatic calculation setting from the FIRST 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 NOT 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 BEFORE the Workbook_Open event is executed.

So the question I have obviously relates to the project being opened AFTER another workbook is opened with automatic calculation turned ON:

  1. How do I force my project to disable 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) OR...
  2. How do I get the project to open in ANOTHER INSTANCE (when double clicked) to avoid inheriting automatic calculation setting from the previous workbook.

Either way, the answer I'm seeking is for the project to open without performing the calculation first.

Thanks

Upvotes: 2

Views: 1755

Answers (2)

shA.t
shA.t

Reputation: 16968

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.

Upvotes: 0

Charles Williams
Charles Williams

Reputation: 23540

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

Upvotes: 1

Related Questions