时间戳

首页 > 解决方案 > 范围集合

arrays - 范围集合

问题描述

有没有办法通过使用 Collection 或 Array 使这段代码更好？

我想将所有这些 Range 放在某种字典中以便于使用它，你能给我一个解决方案，并举例说明如何访问数据吗？

Dim A01 As Range, A02 As Range, A03 As Range, A04 As Range, B01 As Range, B02 As Range, B03 As Range, B04 As Range, C01 As Range, C02 As Range, C03 As Range, C04 As Range, D01 As Range, D02 As Range, D03 As Range, D04 As Range, E01 As Range, E02 As Range, E03 As Range, E04 As Range, F01 As Range, F02 As Range, F03 As Range, F04 As Range

Set A01 = Range("B5:B51")
Set A02 = Range("E5:E51")
Set A03 = Range("H5:H51")
Set A04 = Range("K5:K51")

Set B01 = Range("O5:O51")
Set B02 = Range("R5:R51")
Set B03 = Range("U5:U51")
Set B04 = Range("X5:X51")

Set C01 = Range("AB5:AB51")
Set C02 = Range("AE5:AE51")
Set C03 = Range("AH5:AH51")
Set C04 = Range("AK5:AK51")

Set D01 = Range("AO5:AO51")
Set D02 = Range("AR5:AR51")
Set D03 = Range("AU5:AU51")
Set D04 = Range("AX5:AX51")

Set E01 = Range("BB5:BB51")
Set E02 = Range("BE5:BE51")
Set E03 = Range("BH5:BH51")
Set E04 = Range("BK5:BK51")

Set F01 = Range("BO5:BO51")
Set F02 = Range("BR5:BR51")
Set F03 = Range("BU5:BU51")
Set F04 = Range("BX5:BX51")

Dim cell As Range

For Each cell In A01
    Debug.Print cell.Value
Next cell

编辑：例如，我想访问 MyArray[A02] 之类的数据...

标签： arraysexcelvbacollectionsrange

解决方案

您可以使用这样的集合：

Sub NameMyRange()

    Dim oCol As New Collection
    Dim oCell As Range

    With ThisWorkbook.Worksheets("Sheet4")  ' <-- Change sheet name
        oCol.Add .Range("B2:B5"), "AO1"     ' <-- Set all your range like this
        oCol.Add .Range("C2:C5"), "AO2"
    End With

    ' Loop through any range like so
    For Each oCell In oCol.Item("AO2")
        MsgBox oCell.Value2
    Next

End Sub

推荐阅读