How do I resize an array in a loop?

Question

I am trying to return an array in excel. I have been using this site as a reference: http://www.cpearson.com/excel/returningarraysfromvba.aspx

So I started with this basic example script which returns a matrix of values (taken from the above site):

Function Test() As Variant
    Dim V() As Variant
    Dim N As Long
    Dim R As Long
    Dim C As Long
    ReDim V(1 To 3, 1 To 4)
    For R = 1 To 3
        For C = 1 To 4
            N = N + 1
            V(R, C) = N
        Next C
    Next R
    Test = V
End Function

When I type =Test() into 3x4 range of cells with ctrl+shift+enter I get the expected result:

1  2  3  4
5  6  7  8
9  10 11 12

So suppose now that I want to resize the array as I populate it. I tried changing the function to this:

Function Test() As Variant
    Dim V() As Variant
    Dim N As Long
    Dim R As Long
    Dim C As Long
    For R = 1 To 3
        For C = 1 To 4
            N = N + 1
            ReDim Preserve V(1 To R, 1 To C)
            V(R, C) = N
        Next C
    Next R
    Test = V
End Function

As I'm using the vba IDE I don't think I have any way of diagnosing why the above just returns #VALUE. What am I doing wrong?

Answer 1

From the MSDN page on the ReDim statement:

• Resizing with Preserve. If you use Preserve, you can resize only the last dimension of the array. For every other dimension, you must specify the bound of the existing array.
  For example, if your array has only one dimension, you can resize that dimension and still preserve all the contents of the array, because you are changing the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension if you use Preserve.

So the problem is trying to redim the first rank (the R or first dimension). You cannot do this with Preserve. However, you can redim the second rank (the C or second dimension) with Preserve all you want.

ReDim Preserve V(1 To 3, 1 To 1)
For R = 1 To 3
    For C = 1 To 4
        N = N + 1
        ReDim Preserve V(LBound(V, 1) To UBound(V, 1), 1 To C)
        V(R, C) = N
    Next C
Next R

If it was mission critical to redim the first rank of a two-dimensioned array, then transpose it first.

ReDim Preserve V(1 To 1, 1 To 1)
For R = 1 To 3
    V = application.Transpose(V)
    ReDim Preserve V(LBound(V, 1) To UBound(V, 1), LBound(V, 2) To R)
    V = application.Transpose(V)
    For C = 1 To 4
        N = N + 1
        ReDim Preserve V(LBound(V, 1) To UBound(V, 1), LBound(V, 2) To C)
        V(R, C) = N
    Next C
Next R

I've seen this used in a loop to put more 'rows' into a two-dimensioned array but transpose has limits and it is extra processing, albeit in-memory.