如何用VBA修改公式中的文本字符串？批量替换Excel公式内容失败

问题场景 #

已实现批量替换目录中Excel文件的单元格值，但无法修改公式内的文本字符串（例如公式 =@SUBNM("eh_operating_budget_2022-23":Budget Adjustment Number","","") 中的 eh_operating_budget_2022-23），调整MatchCase、LookAt参数或移除FormulaVersion均无效。

现有代码 #

'# USED TO FIND AND REPLACE VALUES IN EACH WORKBOOK IN A DIRECTORY
Dim ws2 As Worksheet
Dim findList As Variant, replaceList As Variant
Dim x As Long
Dim find1 As String, find2 As String, find3 As String
Dim replace1 As String, replace2 As String, replace3 As String

    find1 = ws.Cells(4, 2)
    find2 = ws.Cells(7, 2)
    find3 = ws.Cells(10, 2)
        replace1 = ws.Cells(5, 2)
        replace2 = ws.Cells(8, 2)
        replace3 = ws.Cells(11, 2)

            If find2 = "" Then
                findList = Array(find1)
                replaceList = Array(replace1)
                
            ElseIf ws.Cells(10, 2) = "" Then
                findList = Array(find1, find2)
                replaceList = Array(replace1, replace2)
                
            Else
                findList = Array(find1, find2, find3)
                replaceList = Array(replace1, replace2, replace3)
            End If

    '# Loop through each item in Array lists
        For x = LBound(findList) To UBound(findList)
            For Each ws2 In ActiveWorkbook.Worksheets
              ws2.Cells.Replace What:=findList(x), Replacement:=replaceList(x), _
                LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            Next ws2
        Next x

解决方案 #

核心问题在于原代码未明确针对公式内容进行搜索，且xlReplaceFormula2参数可能干扰普通公式的替换逻辑。以下是两种可行修改方案：

方案1：明确区分值与公式的替换

通过SearchFormula:=True指定搜索公式内容，同时用xlPart匹配子字符串：

'# Loop through each item in Array lists
For x = LBound(findList) To UBound(findList)
    For Each ws2 In ActiveWorkbook.Worksheets
        ' 替换单元格值（保持原xlWhole匹配逻辑）
        ws2.Cells.Replace What:=findList(x), Replacement:=replaceList(x), _
            LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
        ' 替换公式中的目标文本（用xlPart匹配子串，指定搜索公式）
        ws2.Cells.Replace What:=findList(x), Replacement:=replaceList(x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False, SearchFormula:=True
    Next ws2
Next x

方案2：仅遍历含公式的单元格（提升效率）

用SpecialCells(xlCellTypeFormulas)定位有公式的单元格，减少不必要的遍历：

'# Loop through each item in Array lists
For x = LBound(findList) To UBound(findList)
    For Each ws2 In ActiveWorkbook.Worksheets
        ' 替换单元格值
        ws2.Cells.Replace What:=findList(x), Replacement:=replaceList(x), _
            LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
        
        ' 处理公式单元格
        Dim rng As Range
        On Error Resume Next ' 避免无公式单元格时触发错误
        Set rng = ws2.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        
        If Not rng Is Nothing Then
            rng.Replace What:=findList(x), Replacement:=replaceList(x), _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
        End If
    Next ws2
Next x

关键说明 #

• 移除FormulaVersion:=xlReplaceFormula2：该参数专为动态数组公式设计，会干扰普通公式的替换逻辑
• 使用xlPart：因为要替换的是公式中的子字符串，而非整个公式或单元格内容
• SearchFormula:=True（方案1）：明确告诉Excel搜索公式内部的文本内容
• SpecialCells（方案2）：仅处理有公式的单元格，提升批量处理的效率

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