Excel自动计算设置 [英] Excel Automatic Calculation Setting

问题描述

我在Microsoft Office Excel 2007中有一个复杂的项目，它利用了大量的UDF。通过VBA在 Workbook_Open 事件中，我将Excel自动计算设置为OFF，并将策略性放置的 Calculate 方法设置为在需要时手动计算单元格，以便UDF不会无意中执行重新计算。

如果工作簿是只有一个打开（或第一个打开） Excel实例，一切都很完美。只有打开另一个工作簿（在同一个实例中）之后，我的项目将继承自第一个工作簿中的自动计算设置，并对我的UDF执行无限计算。在UDF完成计算（可以永久使用）之前，不会执行放置在Workbook_Open事件中的禁用代码。只有当我的项目是不首先打开的项目时，才会发生这种情况。

通过 http://www.decisionmodels.com/calcsecretse.htm ，我发现Excel是执行计算过程的性质 BEFORE 执行Workbook_Open事件。

所以我明确的问题是打开的项目之后打开另一个工作簿，并自动计算ON：

1. 如何强制我的项目禁用自动计算
   ，而不必先执行重新计算（记住，问题只有
   发生在项目不是第一个被打开，因为它将
   遵循以前打开的工作簿的设置） OR ...


2. 如何让项目在ANOTHER INSTANCE中打开（双击时），以避免
   继承自动计算以前的工作簿中的离子设置。

无论哪种方式，我正在寻找的答案是项目打开而不执行计算。

谢谢

解决方案

一种方法是使用不同的工作簿Opener.xls）启动打开UDF工作簿（udf.xls）

在Opener.xls中Workbook_Open代码

- 将计算设置为手动
- 打开udf.xls

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

解决方案

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

这篇关于Excel自动计算设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！