为何工作表级Calculate后Application.CalculationState始终为xlPending及解决方法

电脑运行缓慢，打开Excel文件时出现公式未计算的情况，为此编写了如下VBA代码：

Public LOCAL_PARAMETERS_WORKBOOK As Workbook
Public LOCAL_PARAMETERS_WORKSHEET As Worksheet

application.ScreenUpdating = False
application.DisplayAlerts = False
application.EnableEvents = True
application.Calculation = xlCalculationAutomatic

Set LOCAL_PARAMETERS_WORKBOOK = Workbooks.Open(StrPathFile, True, True)
Set LOCAL_PARAMETERS_WORKSHEET = LOCAL_PARAMETERS_WORKBOOK.Sheets("Business Process Data Flow")

LOCAL_PARAMETERS_WORKBOOK.Worksheets("DynamicPath").Calculate
If application.CalculationState <> xlDone Then CalculateFormulas

LOCAL_PARAMETERS_WORKSHEET.Calculate
If application.CalculationState <> xlDone Then CalculateFormulas

代码要求先计算「DynamicPath」工作表，再计算依赖它的「Business Process Data Flow」工作表。测试发现：

• 使用application.Calculate时，所有打开的工作簿会完成计算，application.CalculationState会变为xlDone；
• 使用Worksheet.Calculate时，application.CalculationState始终为xlPending。

CalculateFormulas是一个带计数器的函数，会等待直到计算状态变为xlDone。现需解决两个问题：

1. 为何工作表级Calculate后CalculationState不改变？
2. 有哪些其他修复方案？

一、工作表级Calculate后CalculationState不变的原因 #

• Worksheet.Calculate是同步执行的，仅触发当前工作表的公式计算，不会触发依赖该工作表的其他工作簿/工作表计算，也不会更新application.CalculationState——这个属性反映的是Excel应用级别的计算队列状态，工作表级计算不会写入全局状态。
• 即便当前工作表计算完成，若存在外部依赖（比如其他工作簿的单元格引用了此工作表），CalculationState会保持xlPending；若无外部依赖，它也不会自动切换到xlDone，因为该属性的更新仅与应用级计算触发相关。

二、修复方案 #

方案1：调用应用级等待方法更新状态 #

在工作表计算后，主动调用application.CalculateUntilAsyncQueriesDone，该方法会等待所有异步计算完成并同步更新CalculationState：

LOCAL_PARAMETERS_WORKBOOK.Worksheets("DynamicPath").Calculate
application.CalculateUntilAsyncQueriesDone
If application.CalculationState <> xlDone Then CalculateFormulas

LOCAL_PARAMETERS_WORKSHEET.Calculate
application.CalculateUntilAsyncQueriesDone
If application.CalculationState <> xlDone Then CalculateFormulas

方案2：改用工作簿级计算+顺序控制 #

直接按顺序触发工作表计算，搭配CalculateUntilAsyncQueriesDone确保每一步计算完成：

' 先计算DynamicPath
LOCAL_PARAMETERS_WORKBOOK.Worksheets("DynamicPath").Calculate
application.CalculateUntilAsyncQueriesDone

' 再计算依赖的工作表
LOCAL_PARAMETERS_WORKSHEET.Calculate
application.CalculateUntilAsyncQueriesDone

方案3：手动计算模式下按顺序触发 #

先将应用计算模式设为手动，避免自动计算干扰，完成指定工作表计算后再恢复原模式：

Dim originalCalcMode As XlCalculation
originalCalcMode = application.Calculation
application.Calculation = xlCalculationManual

application.ScreenUpdating = False
application.DisplayAlerts = False
application.EnableEvents = True

Set LOCAL_PARAMETERS_WORKBOOK = Workbooks.Open(StrPathFile, True, True)
Set LOCAL_PARAMETERS_WORKSHEET = LOCAL_PARAMETERS_WORKBOOK.Sheets("Business Process Data Flow")

' 按顺序计算目标工作表
LOCAL_PARAMETERS_WORKBOOK.Worksheets("DynamicPath").Calculate
LOCAL_PARAMETERS_WORKSHEET.Calculate

' 等待所有计算完成
application.CalculateUntilAsyncQueriesDone

' 恢复原设置
application.Calculation = originalCalcMode
application.ScreenUpdating = True
application.DisplayAlerts = True
application.EnableEvents = True

方案4：优化CalculateFormulas函数逻辑 #

修改函数不再依赖application.CalculationState，转而针对目标工作表的状态判断，同时增加超时避免无限等待：

Sub CalculateFormulas(targetSheet As Worksheet)
    Dim startTime As Double
    startTime = Timer
    Const TIMEOUT_SECONDS As Double = 30 ' 设置30秒超时
    
    Do While targetSheet.Cells(1).Dirty And (Timer - startTime < TIMEOUT_SECONDS)
        DoEvents ' 释放系统资源
        targetSheet.Calculate
    Loop
End Sub

' 调用示例
LOCAL_PARAMETERS_WORKBOOK.Worksheets("DynamicPath").Calculate
CalculateFormulas LOCAL_PARAMETERS_WORKBOOK.Worksheets("DynamicPath")

LOCAL_PARAMETERS_WORKSHEET.Calculate
CalculateFormulas LOCAL_PARAMETERS_WORKSHEET

内容的提问来源于stack exchange，提问作者Desk