How to change RefersTo for named range?


April 2019


4k time


I have a class ValidationChanger, with a method changeNamedRangeAddress that should change the RefersTo address for a named range. However, my code is unexpectedly wrapping the new address in double quotes.

Class definition for ValidationChanger here:


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

I test it on a range named TestRange, which refers to an address in sheet Instructions: Instructions!$A$133:$A$138. My test should change the address to Instructions!$A$133:$A$139 with the following:

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

The output is:


Any idea why the new address is wrapped in double quotes (which makes it function as a text string instead of an address)?

2 answers


Pass the new range as a range object, and not as a string:

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

The reason you got a text string is that you did not preface the text string with an = sign (so that Excel would know it was supposed to be a formula)