Excel VBA公式中工作表名前自动插入@符号的问题求助

问题背景 #

作为VBA新手，复制工作表到新工作簿后，用替换操作移除公式里的原工作簿名称，大部分单元格正常，但C37:E37的SUMPRODUCT公式部分，工作表名前自动出现@符号，普通跨表求和公式无此问题。直接替换@符号、硬编码公式都无效。

原始代码 #

Windows("CFD_AOPSep2023.xlsm").Activate
Sheet2.Activate
ActiveSheet.Copy Before:=Workbooks("NewWB.xlsx").Sheets(1)

Sheets("Dashboard").Range("A1:Q38").Select
Selection.Replace What:="[CFD_AOPSep2023.xlsm]STEP 1 Current Items", Replacement:="STEP 1 Current Items", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Sheets("Dashboard").Range("A1:Q38").Select
Selection.Replace What:="[CFD_AOPSep2023.xlsm]STEP 2 New SOW", Replacement:="STEP 2 New SOW", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Sheets("Dashboard").Range("A1:Q38").Select
Selection.Replace What:="[CFD_AOPSep2023.xlsm]STEP 3 Non-Inventory Charges", Replacement:="STEP 3 Non-Inventory Charges", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Sheets("Dashboard").Range("A1:Q38").Select
Selection.Replace What:="[CFD_AOPSep2023.xlsm]STEP 4 Inactive Items", Replacement:="STEP 4 Inactive Items", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

尝试过的无效修复 #

1. 直接替换@符号：

Sheets("Dashboard").Range("C37:E37").Select
Selection.Replace What:="@", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

2. 硬编码单元格公式：

Sheets("Dashboard").Range("C37").Select
Selection.Formula = "=(((('STEP 1 Current Items'!S11-SUMPRODUCT(IF('STEP 1 Current Items'!$I$17:$I$1048576='STEP 1 Current Items'!$I$19,1,0),'STEP 1 Current Items'!S$17:S$1048576,OFFSET('STEP 1 Current Items'!$E$17:$E$1048576,-2,)))/'STEP 1 Current Items'!S11)*'STEP 1 Current Items'!S11)+(IFERROR(('STEP 4 Inactive Items'!S9-SUMPRODUCT(IF('STEP 4 Inactive Items'!$I$14:$I$1048576='STEP 4 Inactive Items'!$I$15,1,0),'STEP 4 Inactive Items'!S$14:S$1048576,OFFSET('STEP 4 Inactive Items'!$E$14:$E$1048576,0,)))/'STEP 4 Inactive Items'!S9,0)*'STEP 4 Inactive Items'!S9)+('STEP 3 Non-Inventory Charges'!N11*$L$32))/C35"

Sheets("Dashboard").Range("D37").Select
Selection.Formula = "=(((('STEP 1 Current Items'!T11-SUMPRODUCT(IF('STEP 1 Current Items'!$I$17:$I$1048576='STEP 1 Current Items'!$I$19,1,0),'STEP 1 Current Items'!T$17:T$1048576,OFFSET('STEP 1 Current Items'!$E$17:$E$1048576,-2,)))/'STEP 1 Current Items'!T11)*'STEP 1 Current Items'!T11)+(IFERROR(('STEP 4 Inactive Items'!T9-SUMPRODUCT(IF('STEP 4 Inactive Items'!$I$14:$I$1048576='STEP 4 Inactive Items'!$I$15,1,0),'STEP 4 Inactive Items'!T$14:T$1048576,OFFSET('STEP 4 Inactive Items'!$E$14:$E$1048576,0,)))/'STEP 4 Inactive Items'!T9,0)*'STEP 4 Inactive Items'!T9)+('STEP 3 Non-Inventory Charges'!O11*$L$32))/D35"

Sheets("Dashboard").Range("E37").Select
Selection.Formula = "=(((('STEP 1 Current Items'!U11-SUMPRODUCT(IF('STEP 1 Current Items'!$I$17:$I$1048576='STEP 1 Current Items'!$I$19,1,0),'STEP 1 Current Items'!U$17:U$1048576,OFFSET('STEP 1 Current Items'!$E$17:$E$1048576,-2,)))/'STEP 1 Current Items'!U11)*'STEP 1 Current Items'!U11)+(IFERROR(('STEP 4 Inactive Items'!U9-SUMPRODUCT(IF('STEP 4 Inactive Items'!$I$14:$I$1048576='STEP 4 Inactive Items'!$I$15,1,0),'STEP 4 Inactive Items'!U$14:U$1048576,OFFSET('STEP 4 Inactive Items'!$E$14:$E$1048576,0,)))/'STEP 4 Inactive Items'!U9,0)*'STEP 4 Inactive Items'!U9)+('STEP 3 Non-Inventory Charges'!P11*$L$32))/E35"

解决方案 #

原因分析 #

@符号是Excel的隐式交集运算符，当公式从旧工作簿复制到新工作簿后，SUMPRODUCT结合IF数组运算时，Excel自动将公式识别为动态数组公式，插入@来兼容旧版数组行为，但导致公式异常。直接替换@或用.Formula赋值无效，因为Excel会自动重新添加。

修复代码 #

改用.FormulaArray属性赋值数组公式，同时取消冗余的Select操作：

' 定义工作簿对象，避免依赖激活状态
Dim srcWB As Workbook, destWB As Workbook
Set srcWB = Workbooks("CFD_AOPSep2023.xlsm")
Set destWB = Workbooks("NewWB.xlsx")

' 复制工作表到目标工作簿
srcWB.Sheet2.Copy Before:=destWB.Sheets(1)

' 批量完成公式替换操作
With destWB.Sheets("Dashboard").Range("A1:Q38")
    .Replace What:="[CFD_AOPSep2023.xlsm]STEP 1 Current Items", Replacement:="STEP 1 Current Items", _
        LookAt:=xlPart, MatchCase:=False
    .Replace What:="[CFD_AOPSep2023.xlsm]STEP 2 New SOW", Replacement:="STEP 2 New SOW", _
        LookAt:=xlPart, MatchCase:=False
    .Replace What:="[CFD_AOPSep2023.xlsm]STEP 3 Non-Inventory Charges", Replacement:="STEP 3 Non-Inventory Charges", _
        LookAt:=xlPart, MatchCase:=False
    .Replace What:="[CFD_AOPSep2023.xlsm]STEP 4 Inactive Items", Replacement:="STEP 4 Inactive Items", _
        LookAt:=xlPart, MatchCase:=False
End With

' 给C37:E37设置数组公式
With destWB.Sheets("Dashboard")
    ' C37数组公式
    .Range("C37").FormulaArray = "=(((('STEP 1 Current Items'!S11-SUMPRODUCT(IF('STEP 1 Current Items'!$I$17:$I$1048576='STEP 1 Current Items'!$I$19,1,0),'STEP 1 Current Items'!S$17:S$1048576,OFFSET('STEP 1 Current Items'!$E$17:$E$1048576,-2,)))/'STEP 1 Current Items'!S11)*'STEP 1 Current Items'!S11)+(IFERROR(('STEP 4 Inactive Items'!S9-SUMPRODUCT(IF('STEP 4 Inactive Items'!$I$14:$I$1048576='STEP 4 Inactive Items'!$I$15,1,0),'STEP 4 Inactive Items'!S$14:S$1048576,OFFSET('STEP 4 Inactive Items'!$E$14:$E$1048576,0,)))/'STEP 4 Inactive Items'!S9,0)*'STEP 4 Inactive Items'!S9)+('STEP 3 Non-Inventory Charges'!N11*$L$32))/C35"
    
    ' D37数组公式
    .Range("D37").FormulaArray = "=(((('STEP 1 Current Items'!T11-SUMPRODUCT(IF('STEP 1 Current Items'!$I$17:$I$1048576='STEP 1 Current Items'!$I$19,1,0),'STEP 1 Current Items'!T$17:T$1048576,OFFSET('STEP 1 Current Items'!$E$17:$E$1048576,-2,)))/'STEP 1 Current Items'!T11)*'STEP 1 Current Items'!T11)+(IFERROR(('STEP 4 Inactive Items'!T9-SUMPRODUCT(IF('STEP 4 Inactive Items'!$I$14:$I$1048576='STEP 4 Inactive Items'!$I$15,1,0),'STEP 4 Inactive Items'!T$14:T$1048576,OFFSET('STEP 4 Inactive Items'!$E$14:$E$1048576,0,)))/'STEP 4 Inactive Items'!T9,0)*'STEP 4 Inactive Items'!T9)+('STEP 3 Non-Inventory Charges'!O11*$L$32))/D35"
    
    ' E37数组公式
    .Range("E37").FormulaArray = "=(((('STEP 1 Current Items'!U11-SUMPRODUCT(IF('STEP 1 Current Items'!$I$17:$I$1048576='STEP 1 Current Items'!$I$19,1,0),'STEP 1 Current Items'!U$17:U$1048576,OFFSET('STEP 1 Current Items'!$E$17:$E$1048576,-2,)))/'STEP 1 Current Items'!U11)*'STEP 1 Current Items'!U11)+(IFERROR(('STEP 4 Inactive Items'!U9-SUMPRODUCT(IF('STEP 4 Inactive Items'!$I$14:$I$1048576='STEP 4 Inactive Items'!$I$15,1,0),'STEP 4 Inactive Items'!U$14:U$1048576,OFFSET('STEP 4 Inactive Items'!$E$14:$E$1048576,0,)))/'STEP 4 Inactive Items'!U9,0)*'STEP 4 Inactive Items'!U9)+('STEP 3 Non-Inventory Charges'!P11*$L$32))/E35"
End With

额外优化点 #

• 取消Select/Activate操作，直接通过对象引用操作工作表和单元格，避免因窗口激活状态导致的错误。
• 使用With语句简化重复的范围引用，提升代码可读性和执行效率。

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