VBA数组公式分段替换在非调试模式下失效问题

我有一个长度超过255字符的数组公式，已将其拆分为多个短文本片段。当前遇到的问题是：通过按钮触发宏执行时，公式替换无法按预期完成，但在调试代码时，替换操作完全正常。生成的公式本身无问题，但运行模式下替换失效，无法定位原因。

相关VBA代码如下：

Sub FreezeCellsWith_NA_Tgt(Optional ByVal skillType As String = Constants.ALL)
 
    Sheets("Sheet1").Protect Password:="Pass", UserInterfaceOnly:=True
   
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
   
    Dim searchRange As Range
    Dim textToFind As String, dq As String, sq As String, amp As String
    Dim fullFormula As String, splitFormulaStr1 As String, splitFormulaStr2 As String
    Dim firstTxtFoundCellAddr As String
    Dim lngMatches As Long, LastColumn As Long
    Dim LastTechnicalSkillsRow As Long, LastFunctionalSkillsRow As Long, LastBehaviouralSkillsRow As Long
    Dim txtFoundRange As Range, iCell As Range
           
    dq = Chr(34) ' double quote as a variable
    amp = Chr(38) ' ampersand as a variable
    sq = Chr(39) ' apostrophe or single quote as variable
 
    With Worksheets("Sheet1")
   
        LastColumn = .Cells(4, Columns.Count).End(xlToLeft).Column
        LastTechnicalSkillsRow = .Range("A3:A250").Find("% TECHNICAL GAP", , xlValues).Offset(-3, 0).Row
        LastFunctionalSkillsRow = .Range("A3:A250").Find("% FUNCTIONAL GAP", , xlValues).Offset(-3, 0).Row
        LastBehaviouralSkillsRow = .Range("A3:A250").Find("% BEHAVIOURAL GAP", , xlValues).Offset(-3, 0).Row
       
        Set searchRange = .Range("A4:" & Col_Letter(LastColumn) & 4)
        textToFind = "Target"
       
        Set txtFoundRange = searchRange.Find(What:=textToFind, LookIn:=xlValues, LookAt:=xlPart)
       
        If Not txtFoundRange Is Nothing Then
            firstTxtFoundCellAddr = txtFoundRange.Address
            Do
                Debug.Print txtFoundRange.Address
                If skillType = Constants.ALL Or skillType = Constants.TECHNICAL Then
                    If LastTechnicalSkillsRow >= 6 Then
                        For Each iCell In .Range(Col_Letter(txtFoundRange.Column) & 6 & ":" & Col_Letter(txtFoundRange.Column) & LastTechnicalSkillsRow)
                                Debug.Print "[SM] Unfreezing range : " & iCell.Address & ":" & iCell.Offset(0, 2).Address
                               
                                fullFormula = "=IFNA(VLOOKUP(VLOOKUP($A" & iCell.Row & ", TXT1, TXT2, FALSE), SkillLevel!$B$3:$C$7, 2, FALSE), ""NA"")"
                                splitFormulaStr1 = "INDIRECT(""'"" & INDEX(SkillSheetNames, MATCH(1, --(COUNTIF(INDIRECT(""'"" & SkillSheetNames & ""'!$B$2:$B$100""), $A" & iCell.Row & ")>0), 0)) & ""'!$B$2:$F$100""))"
                                splitFormulaStr2 = "MATCH(" & Col_Letter(iCell.Column) & "$3, INDIRECT(""'"" & INDEX(SkillSheetNames, MATCH(1, --(COUNTIF(INDIRECT(""'"" & SkillSheetNames & ""'!$B$2:$B$100""), $A" & iCell.Row & ")>0), 0)) & ""'!$B$1:$Z$1""), 0)"
 
                                .Range(iCell.Address).FormulaArray = fullFormula
                                .Range(iCell.Address).Replace "TXT1", splitFormulaStr1, xlPart
                                .Range(iCell.Address).Replace "TXT2", splitFormulaStr2, xlPart
                            End If
                        Next iCell
                    End If
                End If
                Set txtFoundRange = searchRange.Find(What:=textToFind, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, after:=txtFoundRange)
            Loop While Not txtFoundRange Is Nothing And txtFoundRange.Address <> firstTxtFoundCellAddr
        End If
    End With
   
    Calculate
   
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
End Sub

核心问题出在数组公式的解析时机和.Replace方法的兼容性：

• 运行模式下代码执行速度快，.FormulaArray赋值后Excel还未完成数组公式的内部解析，就执行了.Replace操作，导致占位符TXT1/TXT2无法被正确识别匹配。
• 数组公式在Excel中以特殊格式存储，直接对其调用.Replace方法本身就存在稳定性问题，不如普通公式的替换逻辑可靠。

提供两种可靠的处理方式，任选其一即可：

方案1：直接拼接完整公式后赋值为数组公式 #

彻底绕开替换步骤，直接拼接出完整的公式字符串，再一次性赋值为数组公式：

' 替换原代码中分步替换的段落
fullFormula = "=IFNA(VLOOKUP(VLOOKUP($A" & iCell.Row & ", " & _
              "INDIRECT(""'"" & INDEX(SkillSheetNames, MATCH(1, --(COUNTIF(INDIRECT(""'"" & SkillSheetNames & ""'!$B$2:$B$100""), $A" & iCell.Row & ")>0), 0)) & ""'!$B$2:$F$100""), " & _
              "MATCH(" & Col_Letter(iCell.Column) & "$3, INDIRECT(""'"" & INDEX(SkillSheetNames, MATCH(1, --(COUNTIF(INDIRECT(""'"" & SkillSheetNames & ""'!$B$2:$B$100""), $A" & iCell.Row & ")>0), 0)) & ""'!$B$1:$Z$1""), 0), FALSE), SkillLevel!$B$3:$C$7, 2, FALSE), ""NA"")"
.Range(iCell.Address).FormulaArray = fullFormula

方案2：先以普通公式完成替换，再转为数组公式 #

如果需要保留分段构建的逻辑，可先将公式设为普通公式完成替换，再转换为数组公式：

' 替换原代码中分步替换的段落
.Range(iCell.Address).Formula = fullFormula ' 先赋值为普通公式
.Range(iCell.Address).Replace "TXT1", splitFormulaStr1, xlPart
.Range(iCell.Address).Replace "TXT2", splitFormulaStr2, xlPart
.Range(iCell.Address).FormulaArray = .Range(iCell.Address).Formula ' 转为数组公式

原代码中重复计算了两次相同的INDEX/MATCH逻辑，可提前计算结果复用，提升代码效率：

' 在构建splitFormulaStr1和splitFormulaStr2前添加
Dim skillSheetPrefix As String
skillSheetPrefix = "INDIRECT(""'"" & INDEX(SkillSheetNames, MATCH(1, --(COUNTIF(INDIRECT(""'"" & SkillSheetNames & ""'!$B$2:$B$100""), $A" & iCell.Row & ")>0), 0)) & ""'!"""

splitFormulaStr1 = skillSheetPrefix & "$B$2:$F$100"""
splitFormulaStr2 = "MATCH(" & Col_Letter(iCell.Column) & "$3, " & skillSheetPrefix & "$B$1:$Z$1""), 0)"

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