如何用VBA创建带工作表名的仅锁列混合引用命名范围

根因分析 #

直接将Range对象传入RefersTo参数时，VBA会自动将其转换为默认的绝对引用格式，覆盖之前在Address方法中设置的混合引用规则；首次代码生成的引用不含工作表路径，是因为Address方法默认仅返回单元格相对位置，不包含父对象路径。

修复代码 #

Sub test()
    Dim i As Long
    i = 1
    Do Until Cells(1, i) = "Created Date"
        i = i + 1
    Loop
    
    ' 生成带工作表路径、行相对列绝对的混合引用地址
    Dim NR1 As String
    NR1 = Cells(1, i).Offset(1, 0).Address( _
        RowAbsolute:=False, _
        ColumnAbsolute:=True, _
        External:=True _
    )
    
    ActiveWorkbook.Names.Add Name:="Created_Date", RefersTo:="=" & NR1
    ActiveWorkbook.Names("Created_Date").Comment = ""
End Sub

修改说明 #

• 为Address方法新增External:=True参数，生成的引用地址会自动携带当前工作表路径，无需额外关联ActiveSheet对象
• 直接向RefersTo传入拼接好的带等号的引用字符串，即可完整保留行相对、列绝对的混合引用规则
• 补充变量声明，避免隐式变量带来的潜在报错

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