Excel计算仅在事件工作簿上，寻找解决方法

Question

当您打开多个 Excel 文件时，并且您的 VBA/VSTO 代码调用计算函数，或打开自动计算，Excel 将痛苦地重新计算所有打开的工作簿，不仅仅是事件工作簿。

这是一个众所周知且得到广泛报道的问题，已经存在多年，但微软似乎对修复它不感兴趣。

Calculate only the active workbook before saving

Microsoft Excel wishlist: Workbook level calculation

可笑的是，在 VBA 和 VSTO 中，Microsoft 使我们能够:

• 重新计算特定工作表
• 重新计算所有打开的工作簿

...但是没有选项可以只重新计算一个特定的工作簿。

在我工作的金融公司，这是一个大问题。我们的精算师拥有又大又笨重的 Excel 文件，其中充满了公式，当他们在一个工作簿上单击“计算”或我们在保存文件之前执行计算时，他们必须等待几分钟才能让所有其他打开的 Excel 文件也能计算出来 进行计算。

有两种方法可以解决这个问题。

您可以像这样运行一些 VBA:

Application.Calculation = xlManual 
For Each sh In ActiveWorkbook.Worksheets 
    sh.Calculate 
Next sh 

..但这并不能保证有效。您的“Sheet1”可能包含指向“Sheet2”中单元格的公式，但“Sheet2”中的其他单元格可能依赖于“Sheet1”。因此，计算每个工作表一次可能不足以对您的工作簿执行完整的计算。

或者，您可以在单独的实例中打开每个 Excel 文件(通过在打开 Excel 图标时按住 ALT 键)。但是，您将失去完整的 Excel 剪切和粘贴功能，如下所述:

Can't fully cut'n'paste between Excel instances

所以，我的问题是...有人找到解决方法来解决这个问题吗？

我只是想要重新计算事件 Excel 工作簿中的单元格。

我想知道是否可以添加一些 VBA 或 VSTO，在我启动事件工作簿上的计算之前将所有非事件工作簿设置为“只读”，从而防止其他工作簿重新计算。但这是不可能的。 “Workbook.ReadOnly”只能读取，不能以编程方式设置。

或者也许向 Worksheet_Calculate 事件添加一个处理程序，该处理程序检查正在运行的 VBA 代码是否属于事件工作簿，如果不属于，它会中止尝试计算...？但该事件实际上是在工作表计算完成后开始的，所以为时已晚。

我们公司不可能是唯一遭受此问题困扰的公司...

有什么建议(除了升级到 Lotus 1-2-3 之外)吗？

Answer 1

此方法使用 Excel 的另一个实例来避免多个工作簿计算。使用新实例的几行代码取自 this SO question ，它涉及类似的主题，您可能会感兴趣。

您必须在具体情况下测试速度，因为关闭/打开时间可能不会超过避免的计算!

宏步骤

• 将计算设置为手动
• 保存并退出所需的工作簿
• 在新的 Excel 实例中打开它
• 重新计算
• 在原始 Excel 实例中保存、关闭并重新打开。

运行此脚本的要点:

• 宏不能存在于要重新计算的工作簿中，因为它在此过程中被关闭(两次)。它应该放在其他一些“实用程序”工作簿中。

代码 - 有关详细信息，请参阅评论

Sub CalculateWorkbook(WB As Workbook)
    ' Store path of given workbook for opening and closing
    Dim filepath As String
    filepath = WB.FullName
    ' Turn off calculation before saving
    Dim currentCalcBeforeSave As Boolean
    currentCalcBeforeSave = Application.CalculateBeforeSave
    Application.CalculateBeforeSave = False
    ' Store current calculation mode / screen update and then set it to manual
    Dim currentCalcMode As Integer, currentScreenUpdate As Integer
    currentCalcMode = Application.Calculation
    currentScreenUpdate = Application.ScreenUpdating
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ' Close and save the given workbook
    WB.Close savechanges:=True
    ' Open a new INSTANCE of Excel - meaning seperate calculation calls
    Dim newExcel As Excel.Application
    Set newExcel = CreateObject("Excel.Application")
    ' Could make it visible so that any problems don't leave it hidden in the background
    ' newExcel.Visible = False
    ' Set the calculation mode to manual in the new instance sothat the workbook isn't calculated on opening.
    ' This can't be done without an existing workbook object.
    Dim tempWB As Workbook
    Set tempWB = newExcel.Workbooks.Add
    newExcel.Calculation = xlCalculationManual
    newExcel.CalculateBeforeSave = False
    ' Open the workbook in the new instance of Excel
    Dim newWB As Workbook
    Set newWB = newExcel.Workbooks.Open(filepath)
    ' Calculate workbook once
    newExcel.Calculate
    ' Close and save the workbook, tempworkbook and quit new instance
    newWB.Close savechanges:=True
    tempWB.Close savechanges:=False
    newExcel.Quit
    ' Re-open in the active instance of Excel
    Application.Workbooks.Open filepath
    ' Reset the application parameters
    Application.CalculateBeforeSave = currentCalcBeforeSave
    Application.ScreenUpdating = currentScreenUpdate
    Application.Calculation = currentCalcMode
End Sub

通过向其传递您希望重新计算的工作簿对象来调用上面的子程序(这可以通过按钮等完成)。

这已在非常简单的示例工作簿上进行了测试，并且该概念有效。但是，请先在工作簿的副本上进行测试，因为它尚未经过完全的稳健性测试，并且没有错误处理。