How can I "ReDim Preserve" a 2D Array in Excel 2007 VBA so that I can add rows, not columns, to the array?

Question

I'm working with a dynamic array in Excel VBA. The number of columns (m) is fixed, however, I do not know how many rows (n) will be required.

The help documents state that ReDim Preserve myArray(n, m) allows me to make m larger, but not n. However, I need to increase the number of rows (n) while preserving my data, not columns (m)!

For example, I may have a (5,20) array that I would like to expand to (10,20) while preserving my data.

It seems that if there were some way to transpose my array, do a ReDim Preserve to expand the number of "columns", then re-transpose my array, I could accomplish what I want.

Is this the correct way to do this? If so, how can I do that?

Is there a better way to accomplish what I want?

Answer 1

No way to determine the number of elements in the first dimension? Bummer. For a two-dimensional array with a fixed second dimension, you might want to consider making it an array of Types ("structs" in other languages) instead. That will allow you to use Redim Preserve, and still leaves you with a reasonable way to add and access values, though you'll now be accessing the second dimension as named members of the Type rather than is index values.

Answer 2

Solved my own question; here's how I got around my problem. I created a temporary array, copied the contents of myArray to the temporary Array, resized myArray, then copied the contents back from the temp array to myArray.

tempArray = myArray
ReDim myArray(1 To (UBound(myArray()) * 2), 1 To m)
For i = 1 To n
     For j = 1 To m
          myArray(i, j) = tempArray(i, j)
     Next j
Next i

If anyone can suggest a more efficient way to do this, I'd love to hear it.

Answer 3

One way to do what you want is to use a 1-D array that contains 1-D arrays instead of a 2-D array. Then you can ReDim Preserve the outer array all you want. If you're returning the outer array from a function, Excel will do the right thing and coerce it to a 2-D array.

For example, the function below will return a 3x2 array to the cells it's called from:

Public Function nested()
    Dim outer
    outer = Array(Array(1, 2), Array(3, 4))

    ReDim Preserve outer(1 To 3)

    outer(3) = Array(5, 6)

    nested = outer
End Function

My answer to these questions might also be useful to you: http://stackoverflow.com/questions/3428811/pass-multidimensional-array-into-excel-udf-in-vba/3430854#3430854 and http://stackoverflow.com/questions/3073486/vba-pasting-3-dimensional-array-into-sheet/3074024#3074024

Of course, if you're not returning this from a UDF, you'll have to coerce it yourself. An easy way to do that without writing looping code is to do this:

Dim coerced
coerced = Application.Index(outer, 0, 0)

This is just calling Excel's built-in INDEX function, and the zeros mean that you want back all of your rows and all of your columns. Excel will coerce your 1-D array of 1-D arrays to a 2-D array automatically. (Caveat: there are some size limitations, but they are much bigger than 10x20.)