Excel中SUMPRODUCT无法识别VBA自定义变量致#NAME错误求助

嘿，刚上手VBA踩这个坑太正常啦！你的问题核心很明确：Excel的工作表公式根本不知道你在VBA代码里定义的firstArray和secondArray是什么——这俩是VBA专属的变量，不是Excel公式能直接识别的命名区域或函数，所以才会抛出#NAME?错误。

错误原因拆解 #

当你执行emptyCells.Formula = "=SUMPRODUCT(firstArray, secondArray)"时，Excel会把引号里的内容当成纯工作表公式去解析。它在当前工作表（Sheet3）里找不到叫firstArray或secondArray的东西，自然就报错了。

修正方案：把VBA变量转成Excel能识别的区域引用 #

我们需要把VBA中的Range对象，转换成Excel公式能看懂的完整单元格地址（带工作表名），同时还要改掉没必要的Activate操作（VBA里直接通过工作表对象引用区域更高效，也不容易出莫名其妙的问题）。

这里有两种靠谱的写法：

写法1：用Address(External:=True)生成完整引用

这种方式会自动带上工作簿名、工作表名和区域地址，适合跨工作簿引用的场景：

Public Sub myMacro()
    Dim firstArray As Range
    ' 直接指定工作表，无需Activate
    Set firstArray = Worksheets("Sheet1").Range("A2:C40")
    
    Dim secondArray As Range
    Set secondArray = Worksheets("Sheet2").Range("A2:C40") ' 注意：这里我把区域改成了和firstArray同维度，否则SUMPRODUCT会报错
    
    Dim emptyCells As Range
    Set emptyCells = Worksheets("Sheet3").Range("A1:Z1")
    
    ' 将VBA Range对象转为Excel公式能识别的完整引用
    emptyCells.Formula = "=SUMPRODUCT(" & firstArray.Address(External:=True) & ", " & secondArray.Address(External:=True) & ")"
End Sub

写法2：手动拼接工作表名和区域地址

如果是同一个工作簿内的引用，这种写法更简洁：

Public Sub myMacro()
    Dim firstArray As Range
    Set firstArray = Worksheets("Sheet1").Range("A2:C40")
    
    Dim secondArray As Range
    Set secondArray = Worksheets("Sheet2").Range("A2:C40") ' 必须保证两个区域维度一致！
    
    Dim emptyCells As Range
    Set emptyCells = Worksheets("Sheet3").Range("A1:Z1")
    
    ' 手动拼接工作表名（处理带空格的表名需要加单引号）和区域地址
    emptyCells.Formula = "=SUMPRODUCT('" & firstArray.Parent.Name & "'!" & firstArray.Address & _
                        ", '" & secondArray.Parent.Name & "'!" & secondArray.Address & ")"
End Sub

额外注意点！ #

SUMPRODUCT函数要求两个参数的维度完全匹配（行数和列数都要一样）。你原来的代码里，firstArray是39行3列，secondArray是39行4列，就算解决了#NAME?错误，也会触发#VALUE!错误，所以一定要调整两个区域的大小让它们一致哦！

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