使用在VBA范围的阵列 - Excel的 [英] Using an Array of Ranges in VBA - Excel

问题描述

使用范围的数组变量是否VBA支持？

Does VBA support using an array of range variables?

dim rangeArray() as range
dim count as integer
dim i as integer

count = 3

redim rangeArray(1 to count)

for i = 1 to count
  msgbox rangeArray(i).cells(1,1).value
next

我不能让它在这种类型的应用工作。我想以某种顺序存储一系列范围为主副本。然后，我可以添加，删除，排序或做任何此数组，然后就打印出来了一系列在Excel范围。它似乎并不像Excel支持这 - 它只是强迫你存储在S preadsheet你的数据，你必须重读它，才能使用它。

I can't get it to work in this type of application. I want to store a series of ranges in a certain order as a "master copy". I can then add, delete, sort or do whatever to this array and then just print it out to a series of ranges in excel. It doesn't seem like excel supports this - it just forces you to store your data in the spreadsheet and you have to reread it in order to use it.

推荐答案

<击>不，数组不能持物。但是，0 对象可以持有的对象。我想你可能想要的是一个Range对象，由各种特定范围的其他对象。在这个例子中，rMaster是我的阵持有三个单元格。

No, arrays can't hold objects. But oObjects can hold objects. I think what you may want is a Range object that consists of various specific other Range object. In this example, rMaster is my "array" that holds three cells.

Sub StoreRanges()

    Dim rMaster As Range
    Dim rCell As Range

    Set rMaster = Sheet1.Range("A1")
    Set rMaster = Union(rMaster, Sheet1.Range("A10"))
    Set rMaster = Union(rMaster, Sheet1.Range("A20"))

    For Each rCell In rMaster
        MsgBox rCell.Address
    Next rCell

End Sub

使用我的新发现的知识，数组可以容纳的范围（日Thnx jtolle），这里是你将如何存储在范围内的数组和排序他们一个例子

With my new found knowledge that arrays can hold ranges (thnx jtolle), here's an example of how you would store ranges in an array and sort them

Sub UseArray()

    Dim aRng(1 To 3) As Range
    Dim i As Long

    Set aRng(1) = Range("a1")
    Set aRng(2) = Range("a10")
    Set aRng(3) = Range("a20")

    BubbleSortRangeArray aRng

    For i = LBound(aRng) To UBound(aRng)
        Debug.Print aRng(i).Address, aRng(i).Value
    Next i

End Sub

Sub BubbleSortRangeArray(ByRef vArr As Variant)

    Dim i As Long, j As Long
    Dim vTemp As Variant

    For i = LBound(vArr) To UBound(vArr) - 1
        For j = i To UBound(vArr)
            If vArr(i).Value > vArr(j).Value Then
                Set vTemp = vArr(i)
                Set vArr(i) = vArr(j)
                Set vArr(j) = vTemp
            End If
        Next j
    Next i

End Sub

这篇关于使用在VBA范围的阵列 - Excel的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！