vba - 应用程序.根据工作簿进行计算

Question

我正在管理一个包含超过 200 000 个公式(一些非常复杂的数组公式)的工作簿，这意味着我无法让 Excel 在每次单击某处时自动计算所有单元格(计算所有内容大约需要 8 小时) )。

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

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

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

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

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

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

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

我知道我可以在关闭之前使用 Worksheets.Range.Calculate 方法计算我的 Values.xlsx 工作簿，但如果我在新实例中打开它，计算仍将设置为手动之后的 Excel。

---

编辑下午 3:20:不确定我是否足够清楚，英语不是我的母语。简而言之，我有 Calculation.xlsm，其中 VBA 和 Calculation 设置为手动。我有 Values.xlsx ，没有 VBA 并且计算设置为自动。如果我在 Calculation.xlsm 中使用以下 VBA 代码打开 Values.xlsx，Excel 会自动将我的 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:40:我可以将我的大工作簿与 Application.Calculation 设置为手动，将我需要的所有数据放入剪贴板中(我只需要值，而不需要公式) ，关闭它(即使我关闭执行它的工作簿，VBA 是否仍会继续执行？)，将 Application.Calculation 设置为“自动”(因为没有打开的工作簿)，然后打开目标工作簿以粘贴值(由于另一个工作簿已关闭，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 属性与应用程序的实例相关，而不是其他实例。

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

从 this very similar question 稍作修改(它还询问有条件地禁用应用程序级属性)。

如果:

I was just worrying about if the code would still be executed if I close the workbook from which it is launched

然后只需使用正常的 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

这只会在打开特定工作簿时创建事件处理程序，并且每当您将 View 切换到其他工作簿时，处理程序都会切换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