Excel VBA复制工作表后公式出现@符号的原因及解决方法

问题背景 #

我使用Excel VBA将外部Excel文件的工作表自动复制到主工作簿，其中cubus工作表包含引用current工作表的公式，示例如下：

=IFERROR(INDEX(current!$B$12:$Z$50,MATCH($A12,current!$A$12:$A$51,0),MATCH(B$10&B$11,current!$B$10:$Z$10&current!$B$11:$Z$11,0)),)

替换新的current工作表前，我会临时将cubus中所有=替换为#%以避免引用失效，替换完成后再将#%换回=。但宏运行后，公式出现异常：

=IFERROR(INDEX(current!$B$12:$Z$50,MATCH($A12,current!$A$12:$A$51,0),MATCH(B$10&B$11,@current!$B$10:$Z$10&@current!$B$11:$Z$11,0)),)

仅最后两个current引用前被自动添加了@符号，需要排查原因并解决。

附完整VBA代码：

Sub ReplaceSheetsAndProtectCubusFixed()
    Dim olderFile As String
    Dim newerFile As String
    Dim wbSource As Workbook
    Dim wbMaster As Workbook
    Dim wsCubus As Worksheet
    Dim wsTarget As Worksheet

    ' Paths to source files (update these)
    olderFile = "C:\...07.09.2025.xlsx"
    newerFile = "C:\...07.10.2025.xlsx"

    ' Set reference to master workbook and Cubus sheet
    Set wbMaster = ThisWorkbook
    Set wsCubus = wbMaster.Sheets("cubus")

    ' Step 1: Protect formulas by converting them to text
    wsCubus.Cells.Replace What:="=", Replacement:="#%", LookAt:=xlPart, _
                           SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    ' Step 2: Replace "Current" sheet contents
    Set wbSource = Workbooks.Open(newerFile)
    Set wsTarget = wbMaster.Sheets("current")
    wsTarget.Cells.Clear
    wbSource.Sheets(1).Cells.Copy Destination:=wsTarget.Cells(1, 1)
    wbSource.Close False

    ' Step 3: Restore formulas by converting text back to formulas
    wsCubus.Cells.Replace What:="#%", Replacement:="=", LookAt:=xlPart, _
                           SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    MsgBox "Sheets replaced and Cubus formulas restored successfully!", vbInformation
End Sub

原因分析 #

出现@符号是Excel动态数组兼容性机制导致的：

• 公式中current!$B$10:$Z$10&current!$B$11:$Z$11属于区域间的数组运算，在旧版Excel中需要按Ctrl+Shift+Enter作为数组公式输入；
• 支持动态数组的Excel版本（如365/2021）会自动添加@（隐式交集运算符），将数组运算结果转为单个值，避免非数组上下文出现溢出错误；
• 你用Replace方法将公式转成文本再恢复时，Excel会重新解析公式：前两个引用是单个区域的常规引用，不会触发隐式交集；但最后两个是通过&连接的区域运算，Excel会按当前版本的兼容性规则自动添加@。

解决办法 #

放弃文本替换的方式，改为直接存储单元格的公式文本，替换完成后重新写入，避免Excel重新解析时修改公式。修改后的代码如下：

Sub ReplaceSheetsAndProtectCubusFixed()
    Dim olderFile As String
    Dim newerFile As String
    Dim wbSource As Workbook
    Dim wbMaster As Workbook
    Dim wsCubus As Worksheet
    Dim wsTarget As Worksheet
    Dim formulaCells As Range
    Dim cell As Range
    Dim formulaCache As Object

    ' 配置文件路径
    olderFile = "C:\...07.09.2025.xlsx"
    newerFile = "C:\...07.10.2025.xlsx"

    ' 初始化对象
    Set wbMaster = ThisWorkbook
    Set wsCubus = wbMaster.Sheets("cubus")
    Set formulaCache = CreateObject("Scripting.Dictionary")

    ' 步骤1：缓存所有含公式的单元格内容
    On Error Resume Next ' 处理无公式单元格的情况
    Set formulaCells = wsCubus.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If Not formulaCells Is Nothing Then
        For Each cell In formulaCells
            formulaCache.Add cell.Address, cell.Formula
            cell.Value = cell.Value ' 临时转为值，避免引用失效
        Next cell
    End If

    ' 步骤2：替换current工作表内容
    Set wbSource = Workbooks.Open(newerFile)
    Set wsTarget = wbMaster.Sheets("current")
    wsTarget.Cells.Clear
    wbSource.Sheets(1).Cells.Copy Destination:=wsTarget.Cells(1, 1)
    wbSource.Close False

    ' 步骤3：恢复缓存的公式
    If Not formulaCells Is Nothing Then
        For Each cell In formulaCells
            cell.Formula = formulaCache(cell.Address)
        Next cell
    End If

    MsgBox "工作表替换完成，Cubus公式已恢复！", vbInformation
End Sub

其他可选方案 #

1. 强制转为数组公式：如果你的公式原本就是数组公式，可在恢复后添加以下代码，强制将目标区域转为数组公式：

wsCubus.Range("A12:Z50").FormulaArray = wsCubus.Range("A12:Z50").Formula ' 替换为你的公式实际范围

2. 禁用隐式交集：全局关闭隐式交集功能（不推荐，可能影响其他工作簿）：

Application.UseImplicitIntersection = False

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