如何在Excel单元格中插入引用指定区域的SUMPRODUCT公式？

问题根源 #

你当前的VBA代码直接将区域名称以字符串形式拼接进公式，Excel会将其识别为普通文本，而非实际的单元格区域引用，导致公式无法正确解析。

解决方案 #

通过Range对象的Address属性获取区域的实际单元格引用地址，再拼接成SUMPRODUCT公式，以下分两种场景给出代码：

场景1：range_new_table1/2是VBA中定义的Range变量

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Theme Capacity")

' 假设已将range_new_table1、range_new_table2定义为ws工作表上的Range对象
Dim targetCell As Range
Set targetCell = ws.Range("A1") ' 替换为你要插入公式的目标单元格

' 生成带正确区域引用的SUMPRODUCT公式
Dim formulaText As String
formulaText = "=SUMPRODUCT(" & range_new_table1.Address(External:=True) & "," & range_new_table2.Address(External:=True) & ")"

' 将公式写入目标单元格
targetCell.Formula = formulaText

场景2：range_new_table1/2是Excel的命名区域

Dim formulaText As String
' 通过命名区域的RefersToRange属性获取实际单元格地址
formulaText = "=SUMPRODUCT('Theme Capacity'!" & ThisWorkbook.Names("range_new_table1").RefersToRange.Address & _
              ",'Theme Capacity'!" & ThisWorkbook.Names("range_new_table2").RefersToRange.Address & ")"

' 写入目标单元格（替换为你的目标位置）
ThisWorkbook.Worksheets("目标工作表").Range("B2").Formula = formulaText

关键说明 #

• Address(External:=True)会生成包含工作表名称的完整引用，避免跨表引用时的歧义；若不需要跨表标识，可省略该参数
• 确保range_new_table1和range_new_table2是有效的Range对象或命名区域，否则会触发运行时错误

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