vba 用数组填充集合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1402876/
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
Populating collection with arrays
提问by
Dim A As Collection
Set A = New Collection
Dim Arr2(15, 5)
Arr2(1,1) = 0
' ...
A.Add (Arr2)
How can I access the Arr2through A? For example, I want to do the following:
我怎样才能访问Arr2通过A？例如，我想执行以下操作：
A.Item(1) (1,1) = 15
so the above would change the first element of the first two-dimensional array inside the collection...
所以上面会改变集合内第一个二维数组的第一个元素......
回答by jtolle
Hmmm...the syntax looks legal enough without having VBA in front of me. Am I right that your problem is that your code "compiles" and executes without raising an error, but that the array in the collection never changes? If so, I think that's because your A.Item(1) might be returning a copyof the array you stored in the collection. You then access and modify the chosen element just fine, but it's not having the effect you want because it's the wrong array instance.
嗯...语法看起来足够合法，而无需在我面前使用 VBA。你的问题是你的代码“编译”并执行而不会引发错误，但集合中的数组永远不会改变，我对吗？如果是这样，我认为这是因为您的 A.Item(1) 可能会返回您存储在集合中的数组的副本。然后您可以很好地访问和修改所选元素，但它没有达到您想要的效果，因为它是错误的数组实例。
In general VBA Collections work best when storing objects. Then they'll work like you want because they store references. They're fine for storing values, but I think they always copy them, even "big" ones like array variants, which means you can't mutate the contents of a stored array.
一般来说，VBA 集合在存储对象时效果最好。然后他们会像你想要的那样工作，因为他们存储引用。它们适用于存储值，但我认为它们总是复制它们，即使是像数组变体这样的“大”值，这意味着你不能改变存储数组的内容。
Consider this answer just a speculation until someone who knows the underlying COM stuff better weighs in. Um...paging Joel Spolsky?
考虑这个答案只是一个猜测，直到有人更好地了解底层的 COM 内容。嗯...寻呼 Joel Spolsky？
EDIT: After trying this out in Excel VBA, I think I'm right. Putting an array variant in a collection makes a copy, and so does getting one out. So there doesn't appear to be a direct way to code up what you have actually asked for.
编辑：在 Excel VBA 中尝试这个之后，我认为我是对的。将数组变体放入集合会生成一个副本，取出一个也是如此。因此，似乎没有直接的方法来对您实际要求的内容进行编码。
It looks like what you actually want is a 3-D array, but the fact that you were tyring to use a collection for the first dimension implies that you want to be able to change it's size in that dimension. VBA will only let you change the size of the last dimension of an array (see "redim preserve" in the help). You canput your 2-D arrays inside a 1-D array that you can change the size of, though:
看起来您真正想要的是一个 3-D 数组，但是您正在为第一个维度使用集合这一事实意味着您希望能够在该维度中更改它的大小。VBA 只会让您更改数组最后一维的大小（请参阅帮助中的“redim 保留”）。您可以将二维数组放在可以更改大小的一维数组中，但是：
ReDim a(5)
Dim b(2, 2)
a(2) = b
a(2)(1, 1) = 42
ReDim Preserve a(6)
Note that a is declared with ReDim, not Dim in this case.
请注意，在这种情况下， a 是用 ReDim 声明的，而不是 Dim。
Finally, it's quite possible that some other approach to whatever it is you're trying to do would be better. Growable, mutable 3-D arrays are complex and error-prone, to say the least.
最后，很可能采用其他方法来处理您正在尝试做的事情会更好。至少可以说，可增长、可变的 3-D 数组是复杂且容易出错的。
回答by Mitch Wheat
@jtolle is correct. If you run the code below and inspect the values (Quick Watch is Shift-F9) of Arr2 and x you will see that they are different:
@jtolle 是正确的。如果您运行下面的代码并检查 Arr2 和 x 的值（Quick Watch 是 Shift-F9），您会发现它们是不同的：
Dim A As Collection
Set A = New Collection
Dim Arr2(15, 5)
Arr2(1, 1) = 99
' ...
A.Add (Arr2) ' adds a copy of Arr2 to teh collection
Arr2(1, 1) = 11 ' this alters the value in Arr2, but not the copy in the collection
Dim x As Variant
x = A.Item(1)
x(1, 1) = 15 ' this does not alter Arr2
回答by xpda
Maybe VBA makes a copy of the array when it assigns it to the collection? VB.net does not do this. After this code, a(3,3) is 20 in vba and 5 in vb.net.
也许 VBA 在将数组分配给集合时会复制它？VB.net 不这样做。在此代码之后，a(3,3) 在 vba 中为 20，在 vb.net 中为 5。
Dim c As New Collection
Dim a(10, 10) As Integer
a(3, 3) = 20
c.Add(a)
c(1)(3, 3) = 5
回答by xpda
I recently had this exact issue. I got round it by populating an array with the item array in question, making the change to this array, deleting the item array from the collection and then adding the changed array to the collection. Not pretty but it worked....and I can't find another way.
我最近有这个确切的问题。我通过用有问题的项目数组填充一个数组，对该数组进行更改，从集合中删除项目数组，然后将更改后的数组添加到集合中来解决它。不漂亮，但它有效......而且我找不到其他方法。
回答by 42LeapsOfFaith
If you want the collection to have a copy of the array, and not a reference to the array, then use the array.clone method:-
如果您希望集合具有数组的副本，而不是对数组的引用，请使用 array.clone 方法：-
Dim myCollection As New Collection
Dim myDates() as Date
Dim i As Integer
Do
i = 0
Array.Resize(myDates, 0)
Do
Array.Resize(myDates, i + 1)
myDates(i) = Now
...
i += 1
Loop
myCollection.Add(myDates.Clone)
Loop
At the end, myCollection will contain the accumulative collection of myDates().
最后，myCollection 将包含 myDates() 的累积集合。
