CODEWITHSUNDEEP
vbaexcel
quant
quant

Reputation: 23042

How do I resize an array in a loop?

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?

Upvotes: 3

Views: 11808

Answers (2)

user4039065
user4039065

Reputation:

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.

Upvotes: 3

bassfader
bassfader

Reputation: 1356

Calling this method in the direct window threw and index out of bounds error in my test. Then I stepped through the code, the problem is the following line:

ReDim Preserve V(1 To R, 1 To C)

For the first run in the For R loop (when R is 1) this is no problem, the array will be sized as (1 to 1, 1 to 4) when the inner For C loop ends.

But as soon as the For R enters the second loop (when R is 2) it will be resized to (1 to 2, 1 to 1), so effectively trying to reduce the size of the second dimension, which throws an error due to the Preserve statement. Removing that Preserve statement gets rid of the error, but will also cause data loss within the second dimension.

To fix this, you'd have to set the final size of the arrays dimensions before starting to loop over the array and filling it (so you'll never reduce the size). But the size of the dimensions can be dynamic, for example provided as method parameters like this:

Function Test(MaxR As Long, MaxC As Long) As Variant
    Dim V() As Variant
    Dim N As Long
    Dim R As Long
    Dim C As Long

    'Set array size
    ReDim Preserve V(1 To MaxR, 1 To MaxC)

    'Then start filling array
    For R = 1 To MaxR
        For C = 1 To MaxC
            N = N + 1
            V(R, C) = N
        Next C
    Next R
    Test = V
End Function

Then you can use that method in an array formula (ctrl+shift+enter) for a 3x4 range of cells by using this formula:

=Test(3; 4)

Upvotes: 2

Related Questions