excel - 命名的非连续范围不能通过 Name.RefersToRange 引用 - 为什么以及如何(优雅地)克服？

Question

我对 Excel 的一个相当奇怪的行为感到困惑(在 Office Pro 2016 和 Office 365 上测试)。
看起来好像 Name.RefersToRange引用非连续范围时中断。
请参阅此测试程序

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

我发现了一个像这样笨拙的解决方法

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

有了它，您现在可以编写(只要选择了正确的工作表)

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

但我想了解 为什么无法通过 Name.RefersToRange 引用非连续范围以及如何比上面显示的更优雅地克服这个限制。

Answer 1

它应该像这样工作:

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

你能在你的电脑上复制我的结果吗？