vba - 如何将地址的范围引用拆分为一维数组的元素

Question

我有一个变量类型变量存储在它的单元格地址和范围地址中

例如:

Dim cells_addresses as variant
Cells_addresses="$A$1,$C$3:$F$3,$F$4"

现在我想使用拆分函数或 VBA 中的任何其他子函数或函数将这些单元格地址拆分为一维数组，如下所示:

Output_Array="$A$1","$C$3","$D$3","$E$3","$F$3","$F$4"

任何人都知道如何将地址范围拆分为数组的各个元素？

Answer 1

这是一种方法:

Function SplitRanges(RangeString As Variant) As Variant
    Dim ranges As Variant, v As Variant
    Dim c As Range
    Dim i As Long, j As Long, n As Long
    ranges = Split(RangeString, ",")
    For i = 0 To UBound(ranges)
        n = n + Range(ranges(i)).Cells.Count
    Next i
    ReDim v(1 To n)
    i = 0
    For j = 0 To UBound(ranges)
        For Each c In Range(ranges(j)).Cells
            i = i + 1
            v(i) = c.Address
        Next c
    Next j
    SplitRanges = v
End Function

测试如下:

Sub test()
    Dim s As String
    s = "$A$1,$C$3:$F$3,$F$4"
    Debug.Print Join(SplitRanges(s), ",")
End Sub

输出:

$A$1,$C$3,$D$3,$E$3,$F$3,$F$4