Excel VBS执行内存溢出问题排查与规避方案咨询

核心问题原因 #

• 直接用ws.UsedRange.Value = ws.UsedRange.Value赋值时，Excel会错误破坏**命名表（ListObject）**的结构，导致跨表引用该表的公式触发#REF!错误，进而引发内存溢出提示（本质是Excel处理表结构时的异常，并非真的内存不足）。
• 手动全选-复制-粘贴值的操作，Excel会保留表结构仅替换单元格内容，因此不会出现问题。

优化后的公式转值代码 #

改用和手动操作一致的复制粘贴逻辑，单独处理命名表区域，确保表结构不被改动：

Option Explicit

Public origCalculation As XlCalculation
Public origEnableEvents As Boolean
Public origDisplayAlerts As Boolean
Public origScreenUpdating As Boolean

Sub RemoveExpressionsFromWorkbook()
    Dim ws As Worksheet
    Dim targetRange As Range
    
    Call TurnEverythingOff
    
    For Each ws In ActiveWorkbook.Worksheets
        ' 处理工作表中的命名表
        If ws.ListObjects.Count > 0 Then
            Dim tbl As ListObject
            For Each tbl In ws.ListObjects
                ' 替换表内数据区域的公式为值
                tbl.DataBodyRange.Copy
                tbl.DataBodyRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            Next tbl
            ' 处理表外的其他使用区域
            Set targetRange = ws.UsedRange
            For Each tbl In ws.ListObjects
                Set targetRange = Application.Subtract(targetRange, tbl.Range)
            Next tbl
            If Not targetRange Is Nothing Then
                targetRange.Copy
                targetRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            End If
        Else
            ' 无命名表的工作表直接处理
            ws.UsedRange.Copy
            ws.UsedRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        End If
        Application.CutCopyMode = False
    Next
    
    Call RestoreEverything
    MsgBox "公式已全部转为值，命名表结构保留", vbInformation
End Sub

Sub TurnEverythingOff()
    With Application
        origCalculation = .Calculation
        origEnableEvents = .EnableEvents
        origDisplayAlerts = .DisplayAlerts
        origScreenUpdating = .ScreenUpdating

        .Calculation = xlCalculationManual
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
End Sub

Sub RestoreEverything()
    With Application
        .Calculation = origCalculation
        .EnableEvents = origEnableEvents
        .DisplayAlerts = origDisplayAlerts
        .ScreenUpdating = origScreenUpdating
    End With
End Sub

处理条件格式公式的方法 #

若要消除条件格式公式带来的卡顿，可在每个工作表的公式转值完成后，添加以下代码：将条件格式的效果固化为单元格直接格式，再删除条件规则：

' 插入到每个工作表处理逻辑的末尾（Application.CutCopyMode = False之后）
' 固化条件格式为单元格直接格式
ws.Cells.Copy
ws.Cells.PasteSpecial Paste:=xlPasteFormats
' 删除所有条件格式规则
ws.Cells.FormatConditions.Delete
Application.CutCopyMode = False

关键说明 #

• 使用xlPasteValuesAndNumberFormats参数，确保替换值的同时保留单元格的格式设置。
• 单独处理命名表的DataBodyRange，既替换表内公式为值，又完整保留表结构。
• 固化条件格式后删除规则，既能保留原有的视觉效果，又彻底消除条件格式公式的计算开销。

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