Populating collection with arrays

Question

Dim A As Collection
Set A = New Collection

Dim Arr2(15, 5)
Arr2(1,1) = 0
' ...

A.Add (Arr2)

How can I access the Arr2 through A? For example, I want to do the following:

A.Item(1) (1,1) = 15

so the above would change the first element of the first two-dimensional array inside the collection...

Solution 1

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) = b
a(2)(1, 1) = 42
ReDim 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.

Solution 2

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

Solution 3

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 Collection
Dim a(10, 10) As Integer

a(3, 3) = 20
c.Add(a)
c(1)(3, 3) = 5

Comments

[vmasule]

I don't think the Arr2(1, 1) = 11 actually does change the array in the collection. See my edited answer, but it looks like VBA makes a copy of the array when adding to and reading from the collection.

[vmasule]

Maybe I'm just misled by your comment? That line does alter Arr2, but not the array stored in the collection. I see it changing Arr2, but not A.Item(1), which is just a copy of Arr2 made by A.Add(Arr2).

[Mitch Wheat]

@jtolle: apologies, I just rechecked and you are absolutely correct. I will update the code above. It is a copy of Arr2 in the collection.