Populating collection with arrays

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 copy of 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.

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.

Consider this answer just a speculation until someone who knows the underlying COM stuff better weighs in. Um...paging 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.

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 can put your 2-D arrays inside a 1-D array that you can change the size of, though:

ReDim a(5)Dim b(2, 2)a(2) = ba(2)(1, 1) = 42ReDim Preserve a(6)

Note that a is declared with ReDim, not Dim in this case.

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.

@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:

Dim A As CollectionSet A = New CollectionDim Arr2(15, 5)Arr2(1, 1) = 99' ...A.Add (Arr2) ' adds a copy of Arr2 to teh collectionArr2(1, 1) = 11  ' this alters the value in Arr2, but not the copy in the collectionDim x As Variantx = A.Item(1)x(1, 1) = 15  ' this does not alter Arr2

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.

Dim c As New CollectionDim a(10, 10) As Integera(3, 3) = 20c.Add(a)c(1)(3, 3) = 5