Application.Calculation取决于工作簿

Question

我正在管理一个包含超过20万个公式(一些非常复杂的数组公式)的工作簿，这意味着我不能让Excel在每次单击某个位置时自动计算所有单元格(计算所有数据需要8小时左右)。

相反，计算被设置为手动，并且在打开Calculation.xlsm时执行以下VBA代码：

With Application
    .CalculateBeforeSave = False
    .Calculation = xlCalculationManual
End With

当需要时，我只使用自定义按钮来计算200 k单元格的某些部分。

我注意到Excel确实在每个工作簿中跟踪该设置，这意味着如果我打开我的Calculation.xlsm，Excel会记住计算被设置为手动。如果我打开我的Values.xlsx，Excel确实记得计算被设置为自动。这是在我尝试将值从Calculation.xlsm复制到Values.xlsx之前。

现在，由于我在Calculation.xlsm中使用VBA将值复制到Values.xlsx，所以Excel也将Application.Calculation设置应用于该工作簿，这意味着如果我使用新的Excel实例打开它，计算仍将设置为手动。

如果我在关闭Application.Calculation = xlCalculationAutomatic工作簿中的VBA之前添加了一个Values.xlsx，它就能工作，但是Excel也会开始计算Values.xlsx工作簿中的200 k单元格，这显然是我不想要的。

因此，我的问题是如何根据特定的工作簿而不是Application对象来实际设置Excel的计算。这是基于以下事实: Excel根据打开的工作簿记录该设置(您只需进行测试并创建两个不同的.xlsx文件，一个已启用计算，另一个已禁用计算，Excel将记住这些设置)。

我知道在关闭Values.xlsx工作簿之前我可以使用它来计算它，但是如果我在之后的一个新的Values.xlsx实例中打开它，计算仍然会被设置为手动。

编辑3:20pm：不确定我是否足够清楚，英语不是我的母语。简而言之，我已经将Calculation.xlsm和VBA和Calculation设置为手动。我有Values.xlsx没有VBA和计算设置为自动。如果我用下面的Values.xlsx代码打开Calculation.xlsm，Calculation.xlsm将自动将我的Values.xlsx工作簿转换为手动计算。

Calculation.xlsm代码：

Private Sub Workbook_Open()
    With Application
        .CalculateBeforeSave = False
        .Calculation = xlCalculationManual
    End With
End Sub

Sub someFunction()
    Set WB = Application.Workbooks.Open("Values.xlsx")
    Set WBws = WB.Sheets("mySheet")
    DoEvents
    wb.Save
    WB.Close
End Sub

在执行someFunction()之后，将Values.xlsx计算设置为手动。这就是问题所在。我希望它保持自动(而且我不能将VBA添加到该文件中，它必须像上面那样从Calculation.xlsm中完成)。

编辑3:40pm：我可以让我的大型工作簿( Application.Calculation )设置为手动，将我需要的所有数据放在剪贴板中(我只需要值，而不是公式)，关闭它(即使我关闭了执行工作簿的工作簿，Application.Calculation仍然会继续执行吗？)，将Application.Calculation设置为Auto (因为没有打开的工作簿)，然后打开目标工作簿粘贴这些值(因为其他工作簿关闭了，Excel仍然将数据保存在剪贴板中吗？)将计算设置为手动(未打开工作簿)并重新打开执行代码的原始工作簿？

Answer 1

其中一种方法是创建一个新的Excel实例。虽然这可能比较慢，而且在函数中没有关闭图书/应用程序的情况下，使用起来可能更困难，但是对于像您的示例这样的简单情况，实现起来可能比较容易：

Sub someFunction()
Dim newExcel as Excel.Application
Set newExcel = CreateObject("Excel.Application")
    
    Set WB = newExcel.Workbooks.Open("Values.xlsx")
    Set WBws = WB.Sheets("mySheet")
    DoEvents
    wb.Save
    WB.Close
    newExcel.Quit
    Set newExcel = Nothing
End Sub

Application.Calculation属性相对于应用程序的实例，而不是其他实例。

Alternatively，您可以使用应用程序级别的事件处理程序。我怀疑这可能更快，但我还没有测试它的速度。

从this very similar question中略作修改(它还要求有条件地禁用应用程序级别的属性)。

如果：

我只是担心如果关闭启动它的工作簿，代码是否还会被执行。

然后，只需使用普通的Workbook_BeforeClose事件处理程序来恢复所需的Application.Calculation属性(对于整个应用程序/所有其他打开的工作簿)。

其余的答案是：

创建一个应用程序级事件处理程序，创建一个名为cEventClass的类模块，并将以下代码放入其中：

Public WithEvents appevent As Application
Dim ret
Private Sub appevent_WorkbookActivate(ByVal wb As Workbook)

    Call ToggleCalculation(wb, ret)
    
End Sub

在名为mod_Caclulate的标准模块中使用以下内容

Option Explicit
Public XLEvents As New cEventClass
Sub SetEventHandler()

If XLEvents.appevent Is Nothing Then
    Set XLEvents.appevent = Application
End If

End Sub

Sub ToggleCalculation(wb As Workbook, Optional ret)
    If wb.Name = ThisWorkbook.Name Then
        ret = xlCalculationManual
    Else
        ret = xlCalculationAutomatic
    End If
    Application.Calculation = ret
End Sub

将其放在工作簿的Workbook_Open事件处理程序中，您一直希望手动计算：

Option Explicit
Private Sub Workbook_Open()
    'Create the event handler when the workbook opens
    Call mod_Caclulate.SetEventHandler
    Call mod_Caclulate.ToggleCalculation(Me)

End Sub

这将只在打开特定工作簿时创建事件处理程序，并且每当您将视图切换到其他工作簿时，处理程序将切换Calculation属性。

注意:如果您“结束”运行时或在调试时执行任何可能导致状态丢失的操作，则会丢失事件处理程序。可以通过调用Workbook_Open过程来恢复这一点，因此，另一种安全措施可能是在ThisWorkbook代码模块中添加这一点：

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
' Additional safeguard in case state loss has killed the event handler:
' use some workbook-level events to re-instantiate the event handler

    Call Workbook_Open
End Sub