在 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 似乎不支持这一点 - 它只是迫使您将数据存储在电子表格中，并且您必须重新阅读才能使用它.

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.

推荐答案

不，数组不能容纳对象.但是o对象可以容纳对象.我认为您可能想要的是一个 Range 对象，它由各种特定的其他 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屋！