vba - 如何更改命名范围的 RefersTo？

Question

我有课ValidationChanger , 使用方法 changeNamedRangeAddress那应该改变RefersTo命名范围的地址。但是，我的代码意外地将新地址用双引号括起来。
ValidationChanger 的类定义这里:

'ValidationChanger

Sub changeNamedRangeAddress(bk As Workbook, rangeName As String, newAddress As String)
 bk.Names(rangeName).RefersTo = newAddress
End Sub

我在一个名为 TestRange 的范围内对其进行测试，它指的是工作表中的地址 Instructions : Instructions!$A$133:$A$138 .我的测试应该将地址更改为 Instructions!$A$133:$A$139具有以下内容:

Sub testValidationChanger()

Dim vc As New ValidationChanger
Dim bk As Workbook

Set bk = Workbooks("test.xlsm")
Debug.Print bk.Names("TestRange").RefersTo
vc.changeNamedRangeAddress bk, "TestRange", "Instructions!$A$133:$A$139"
Debug.Print bk.Names("TestRange").RefersTo

End Sub

输出是:

=Instructions!$A$133:$A$138
="Instructions!$A$133:$A$139"

知道为什么新地址用双引号括起来(这使它用作文本字符串而不是地址)？

Answer 1

将新范围作为范围对象传递，而不是作为字符串:

vc.changeNamedRangeAddress bk, "TestRange",[Instructions!$A$133:$A$139]