vba - range.Address 和 Range.Address(,,xlR1c1) 之间的区别

Question

这个问题在这里已经有了答案:





How to retrieve range.address which is longer than 255 character?

(2 个回答)


5年前关闭。




觉得很奇怪，但是第一行的输出和第二行的输出是有区别的。请注意，R1C1 格式中遗漏了第 641 行和第 641 行。

有什么线索吗？

PS:为了便于阅读，手动添加了空格。

Debug.Print rngVisibleRange.Address

$A$1:$XEW$1, $A$226:$XEW$226, $A$239:$XEW$239, $A$370:$XEW$370, $A$373:$XEW$374, $A$462:$XEW$462, $A$474:$XEW$474, $A$479:$XEW$481, $A$491:$XEW$491, $A$523:$XEW$524, $A$560:$XEW$560, $A$582:$XEW$584, $A$590:$XEW$591, $A$637:$XEW$639, $A$641:$XEW$643, $A$648:$XEW$648

Debug.Print rngVisibleRange.Address(, , xlR1C1)

R1C1:R1C16377, R226C1:R226C16377, R239C1:R239C16377, R370C1:R370C16377, R373C1:R374C16377, R462C1:R462C16377, R474C1:R474C16377, R479C1:R481C16377, R491C1:R491C16377, R523C1:R524C16377, R560C1:R560C16377, R582C1:R584C16377, R590C1:R591C16377, R637C1:R639C16377

可验证示例:

Public Sub test()
    Dim r As Range
    Set r = [A1:XEW1,A226:XEW226,A239:XEW239,A370:XEW370,A373:XEW374,A462:XEW462,A474:XEW474,A479:XEW481,A491:XEW491,A523:XEW524,A560:XEW560,A582:XEW584,A590:XEW591,A637:XEW639,A641:XEW643,A648:XEW648]
    Debug.Print r.Areas.Count                              ' 16
    Debug.Print UBound(Split(r.Address, ","))              ' 15
    Debug.Print UBound(Split(r.Address(0, 0), ","))        ' 15
    Debug.Print UBound(Split(r.Address(, , xlR1C1), ","))  ' 13 (2 areas missing)
End Sub

Answer 1

为了避免 255 个字符的限制，以下两个函数可能会有所帮助:

Public Function SetRange(s As String) As Range

Dim i As Long

For i = LBound(Split(s, ",")) To UBound(Split(s, ","))
    If SetRange Is Nothing Then
        Set SetRange = Worksheets(1).Range(Split(s, ",")(i))
    Else
        Set SetRange = Union(SetRange, Range(Split(s, ",")(i)))
    End If
Next i

End Function

Public Function GetRangeAddress(r As Range) As String

Dim i As Range

For Each i In r.Areas
    GetRangeAddress = GetRangeAddress & "," & i.Address(, , xlR1C1)
Next i

GetRangeAddress = Mid(GetRangeAddress, 2)

End Function

这是一个简短的 sub这两个功能发挥作用:

Option Explicit

Private Sub tmpSO()

Dim r As Range, s As String

s = "$A$1:$XEW$1, $A$226:$XEW$226, $A$239:$XEW$239, $A$370:$XEW$370, $A$373:$XEW$374, $A$462:$XEW$462, $A$474:$XEW$474, $A$479:$XEW$481, $A$491:$XEW$491, $A$523:$XEW$524, $A$560:$XEW$560, $A$582:$XEW$584, $A$590:$XEW$591, $A$637:$XEW$639, $A$641:$XEW$643, $A$648:$XEW$648"

Set r = SetRange(s)

Debug.Print r.Address
Debug.Print r.Address(, , xlR1C1)
Debug.Print GetRangeAddress(r)

End Sub

请注意，这些函数中有验证，可确保传递给 SetRange 的字符串实际上可以用来指向一个范围。此外， SetRange函数自动假定 Worksheet(1)在任何给定的 Excel 文件中。当然，这可以很容易地调整，并且两个功能都可以得到很大改进:可选参数，例如 SetRange 使用哪个工作表或是否 GetRangeAddress是否应该以 xlR1C1 样式返回地址等。

然而，我想保持简单，只解决最初的问题。