在 VBA 中使用范围数组 - Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3630704/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using an Array of Ranges in VBA - Excel
提问by GenericJam
Does VBA support using an array of range variables?
VBA 是否支持使用范围变量数组？
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
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.
我无法让它在这种类型的应用程序中工作。我想以特定顺序存储一系列范围作为“主副本”。然后我可以添加、删除、排序或对该数组执行任何操作，然后将其打印到 excel 中的一系列范围。excel 似乎不支持这一点——它只是强制您将数据存储在电子表格中，并且您必须重新阅读它才能使用它。
回答by Dick Kusleika
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.
不，数组不能容纳对象。但是 o对象可以容纳对象。我认为您可能想要的是一个由各种特定的其他 Range 对象组成的 Range 对象。在这个例子中，rMaster 是我的“数组”，它包含三个单元格。
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
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
根据我新发现的数组可以保存范围的知识（thnx jtolle），这里有一个示例，说明如何在数组中存储范围并对它们进行排序
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
回答by Marc Thibault
It's not entirely clear what you want to do, but...
目前还不完全清楚你想做什么，但是......
If you want a collection, why not use a VBA Collection Object?
如果你想要一个集合，为什么不使用 VBA 集合对象呢？
Dim myRanges as New Collection
A Collection.Item can be any object, including a Range.
Collection.Item 可以是任何对象，包括 Range。
A Range object doesn't hold data; it holds a reference to worksheet cells. If you want the Range contentsin your collection, you'll have to copy them to and from the worksheet.
Range 对象不保存数据；它包含对工作表单元格的引用。如果您想要集合中的 Range内容，则必须将它们复制到工作表中或从工作表中复制它们。
As with Java, your VBA variables are ephemeral, whether in an Array or Collection. If you want to close the file and have the data there when you open it again, you have to have it in worksheet cells. The worksheets are your persistence mechanism.
与 Java 一样，您的 VBA 变量是短暂的，无论是在数组中还是在集合中。如果要关闭文件并在再次打开文件时将数据保存在其中，则必须将其保存在工作表单元格中。工作表是您的持久性机制。
I'm going to take a big leap here so if I'm way off, ignore me. What I think you're looking for suggests setting up a separate worksheet as your "database", populated with List/Table objects holding your raw data. In front of that, is your "user sheet" where you do the interesting stuff, referring to the data in the database sheet. Name everything.
我要在这里大飞跃，所以如果我走远了，请忽略我。我认为您正在寻找的建议设置一个单独的工作表作为您的“数据库”，其中填充了包含原始数据的 List/Table 对象。在此之前，是您的“用户表”，您可以在其中执行有趣的操作，参考数据库表中的数据。命名一切。
回答by GSerg
It's not completely clear for me what you're talking about.
我不太清楚你在说什么。
If you're asking about an ability to create Ranges that map to nothing and exist on their own, then no, there's no way. A Rangeobject is just something that refers to a certain area of a worksheet. It doesn't have any storage of its own or something. Several different instances of Rangeclass can refer to the same worksheet area, too.
如果您问的是创建Range映射到虚无并独立存在的 s的能力，那么不，没有办法。一个Range对象就是一些涉及到工作表的特定区域。它没有任何自己的存储或其他东西。类的几个不同实例Range也可以引用同一个工作表区域。
And if you just want to store some references in an array, then that's fine, go for it. The only problem with your code is that you don't initialize the array elements before using them: as the Rangeis a reference type, all elements get initialized with Nothings by default.
如果你只是想在一个数组中存储一些引用，那就没问题了，去吧。您的代码的唯一问题是您在使用数组元素之前没有初始化它们：因为Range是引用类型，所以Nothing默认情况下所有元素都使用s 进行初始化。
