VBA - populating collection with arrays

Question

hi i have the foll. code in VBA:

          Dim A As Collection
          Set A = New Collection

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

          A.Add (Arr2)

can you tell me how to 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... Thanks a lot!

Answer 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.

Answer 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

Answer 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

Answer 4

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.