Excel VBA中公式引用命名范围返回#NAME?，求原因及VBA创建方法

问题背景 #

工作簿240122 Range Name Scope v01.xlsm包含S1、S2两个工作表，运行指定宏后：

• S1工作表B1单元格写入公式=SUM(r_IN_Wksht_S2)，返回#NAME?错误
• S2工作表A1值为10，期望公式返回10
• 名称管理器中找不到r_IN_Wksht_S1和r_IN_Wksht_S2，手动创建工作簿级命名范围后公式可正常运行，需通过VBA实现命名范围的创建

原代码 #

Sub Scope_Rng_Name_In_2_Wkshts_Insert_Formula()

Dim r_IN_Wksht_S1 As Range 'Named Range in Worksheet S1
Dim r_IN_Wksht_S2 As Range 'Named Range in Worksheet S2

Set r_IN_Wksht_S1 = Worksheets("S1").Range("$A$1")
Set r_IN_Wksht_S2 = Worksheets("S2").Range("$A$1")

r_IN_Wksht_S1.Value = 5
r_IN_Wksht_S2.Value = 10

Worksheets("S1").Cells(1, 2).FormulaR1C1 = "=sum(r_IN_Wksht_S2)"

End Sub

问题原因 #

原代码中的r_IN_Wksht_S1、r_IN_Wksht_S2只是VBA内部的对象变量，并非Excel正式的命名范围，因此Excel公式无法识别这些名称。必须通过VBA的Names.Add方法创建工作表级或工作簿级的正式命名范围。

解决方案 #

方案1：创建工作表级命名范围

工作表级命名范围仅归属对应工作表，跨表引用时需加上工作表名前缀（格式：工作表名!命名范围名称）

修改后的代码：

Sub Scope_Rng_Name_In_2_Wkshts_Insert_Formula()
    Dim r_IN_Wksht_S1 As Range
    Dim r_IN_Wksht_S2 As Range
    
    Set r_IN_Wksht_S1 = Worksheets("S1").Range("$A$1")
    Set r_IN_Wksht_S2 = Worksheets("S2").Range("$A$1")
    
    r_IN_Wksht_S1.Value = 5
    r_IN_Wksht_S2.Value = 10
    
    ' 为S1创建工作表级命名范围
    Worksheets("S1").Names.Add Name:="r_IN_Wksht_S1", RefersTo:=r_IN_Wksht_S1
    ' 为S2创建工作表级命名范围
    Worksheets("S2").Names.Add Name:="r_IN_Wksht_S2", RefersTo:=r_IN_Wksht_S2
    
    ' 跨表引用工作表级命名范围，需指定工作表名前缀
    Worksheets("S1").Cells(1, 2).Formula = "=SUM(S2!r_IN_Wksht_S2)"
End Sub

方案2：创建工作簿级命名范围

工作簿级命名范围属于整个工作簿，可在任意工作表内直接引用，无需添加前缀

修改后的代码：

Sub Scope_Rng_Name_In_2_Wkshts_Insert_Formula()
    Dim r_IN_Wksht_S1 As Range
    Dim r_IN_Wksht_S2 As Range
    
    Set r_IN_Wksht_S1 = Worksheets("S1").Range("$A$1")
    Set r_IN_Wksht_S2 = Worksheets("S2").Range("$A$1")
    
    r_IN_Wksht_S1.Value = 5
    r_IN_Wksht_S2.Value = 10
    
    ' 创建工作簿级命名范围
    ThisWorkbook.Names.Add Name:="r_IN_Wksht_S1", RefersTo:=r_IN_Wksht_S1
    ThisWorkbook.Names.Add Name:="r_IN_Wksht_S2", RefersTo:=r_IN_Wksht_S2
    
    ' 直接引用工作簿级命名范围
    Worksheets("S1").Cells(1, 2).Formula = "=SUM(r_IN_Wksht_S2)"
End Sub

关键说明 #

• 区分VBA变量和Excel命名范围：VBA变量仅在代码执行时有效，Excel命名范围是存储在工作簿中的全局/局部名称，公式可直接识别
• 选择命名范围级别：若命名范围仅在单个工作表内使用，选工作表级；若需跨表复用，选工作簿级

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