Named non-contiguous range cannot referred to via Name.RefersToRange - why & how to overcome (elegantly)?

user_1675073 0
Viewed 111

I got stumped by a rather weired behaviour of Excel (tested on Office Pro 2016 and Office 365). It appears as if Name.RefersToRange breaks when referring to a non-contiguous range.

See this test procedure

Public Sub test()
  Dim n As Name
  With ActiveWorkbook
    For Each n In .Names                                  ' remove all preexisting names
      n.Delete
    Next n
    
    Call .Names.Add("rPass", "=Sheet1!$A$1:$C$3")         ' create a new contiguous named range
    Call .Names.Add("rFail", "=Sheet1!$A$1,Sheet1!$C$3")  ' create a new non-contigous named range

    Debug.Print .Names("rPass").RefersTo                  ' runs fine Output: =Sheet1!$A$1:$C$3
    Debug.Print .Names("rPass").RefersToRange.Address     ' runs fine Output: $A$1:$C$3
    
    Debug.Print .Names("rFail").RefersTo                  ' runs fine Output: =Sheet1!$A$1,Sheet1!$C$3
    Debug.Print .Names("rFail").RefersToRange.Address     ' crashes with Error 1004
  End With
End Sub

I found a clumsy workaround like this

Public Function FunkyRefersToRange(rng As Name) As Range
  Dim r As Range
  Set r = Range(Mid(rng, 2))    ' create a local range by stripping the leading equal sign of the reference
  Set FunkyRefersToRange = r
End Function

With that you can now write (as long the correct worksheet is selected)

Debug.Print FunkyRefersToRange(.Names("rFail")).Address

But I'd like to understand why non-contiguous ranges cannot be referenced via Name.RefersToRange and how to overcome that limitation more elegantely than shown above.

vba excel
1 Answers

It should work like this:

Sub WhatsInaName()
    Dim disJoint As Range, N As Name, addy As String
    
    Set disJoint = Range("A1,B9")
    disJoint.Name = "jim"
    Set N = disJoint.Name
    addy = N.RefersToRange.Address
    
    MsgBox disJoint.Address & vbCrLf & addy
End Sub

enter image description here

Can you replicate my result on your computer?

user_24746560
Related