VBA隐藏工作表中Cells.Formula无法更新值的问题求助

核心原因 #

Excel默认会延迟或跳过隐藏工作表的自动计算，哪怕临时取消再隐藏，宏执行时的计算上下文也可能没触发隐藏表的公式刷新，导致A列的MATCH公式无法返回正确值，后续替换公式的逻辑自然失效。

最优解决方案：绕过隐藏表公式，用VBA直接判断工作表存在性 #

放弃依赖隐藏表A列的公式判断物料表是否存在，直接在VBA里遍历工作表集合，完全避开隐藏表计算限制，逻辑更稳定：

Sub UpdateHiddenSheets()
    Dim wsHiddenInfo As Worksheet
    Dim wsTarget As Worksheet
    Dim wsItem As Worksheet
    Dim lastRow As Long
    Dim a As Long
    Dim strItem As String
    
    ' 步骤1：写入所有NEW ITEM开头的工作表名到Hidden Info的A100:A200
    Set wsHiddenInfo = ThisWorkbook.Worksheets("Hidden Info")
    wsHiddenInfo.Range("A100:A200").ClearContents
    lastRow = 100
    For Each wsItem In ThisWorkbook.Worksheets
        If Left(wsItem.Name, 10) = "NEW ITEM (" Then
            wsHiddenInfo.Cells(lastRow, 1).Value = wsItem.Name
            lastRow = lastRow + 1
        End If
    Next wsItem
    
    ' 步骤2+3：批量处理三个隐藏工作表
    For Each wsTarget In ThisWorkbook.Worksheets(Array("SINFO", "PINFO", "DINFO"))
        ' 临时取消隐藏，避免权限和计算上下文限制
        wsTarget.Visible = xlSheetVisible
        
        ' 遍历需要处理的行（假设从第2行开始，可根据实际需求调整）
        lastRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row
        For a = 2 To lastRow
            strItem = "NEW ITEM (" & (a - 1) & ")"
            ' 直接判断目标工作表是否存在，不用依赖A列公式
            On Error Resume Next
            Set wsItem = ThisWorkbook.Worksheets(strItem)
            On Error GoTo 0
            
            If Not wsItem Is Nothing Then
                ' 替换公式中的固定引用
                wsTarget.Cells(a, 2).Formula2 = Replace(wsTarget.Cells(a, 2).Formula, "NEW ITEM (1)", strItem)
                ' 同步更新A列值
                wsTarget.Cells(a, 1).Value = a - 1
            Else
                wsTarget.Cells(a, 1).Value = "-"
            End If
        Next a
        
        ' 重新隐藏工作表
        wsTarget.Visible = xlSheetHidden
    Next wsTarget
    
    ' 强制全局计算，确保所有公式生效
    Application.CalculateFull
End Sub

关键优化点 #

• 直接通过Worksheets(strItem)判断物料表存在性，彻底绕开隐藏表公式不更新的问题
• 临时取消隐藏后直接操作单元格，避免Excel对隐藏表的权限或计算限制
• 最后执行CalculateFull强制全局计算，确保所有修改后的公式都刷新

备选方案：强制触发隐藏表计算 #

如果一定要保留原有的A列公式逻辑，可在临时取消隐藏后，强制刷新该工作表的计算：

' 在临时取消隐藏后添加这行代码
wsTarget.Calculate

或者在宏开头设置全局计算模式为自动：

Application.Calculation = xlCalculationAutomatic
' 宏结束后可按需恢复原计算模式
' Application.Calculation = xlCalculationManual

但这种方法不如直接用VBA判断工作表存在性可靠，Excel对隐藏表的计算优化仍可能导致延迟。

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