Solved: How do you Redim Preserve a 2 dimensional array?

[malik641]

Hey everyone,

This is annoying, and I can't find this on the web...or in help files. Not sure if I'm not looking in the right places. But could someone help me with this code?

[vba]Sub test_Array()
Dim varArray() As Variant
Dim i As Integer

For i = 0 To 9
'ReDim Preserve varArray(UBound(varArray, 1) + 1, 1)
'ReDim Preserve varArray(i, 1)
varArray(i, 0) = i
varArray(i, 1) = i * i
Next
End Sub[/vba]
I keep getting a "Subscript out of range" error with the two commented out lines (just two methods I tried).

Any suggestions?

[Ken Puls]

Hey Joseph,

Try this as an example. You can tweak to suit...
[vba]Sub test_Array()
Dim varArray() As Variant
Dim i As Integer
Dim lrows As Long
Dim lcols As Long

lrows = 5
lcols = 10

ReDim Preserve varArray(0 To lcols, 0 To lrows)

For i = 0 To 9
varArray(i, 0) = i
varArray(i, 1) = i * i
Next
End Sub[/vba]

[Ivan F Moala]

Hey everyone,

This is annoying, and I can't find this on the web...or in help files. Not sure if I'm not looking in the right places. But could someone help me with this code?

[vba]Sub test_Array()
Dim varArray() As Variant
Dim i As Integer

For i = 0 To 9
'ReDim Preserve varArray(UBound(varArray, 1) + 1, 1)
'ReDim Preserve varArray(i, 1)
varArray(i, 0) = i
varArray(i, 1) = i * i
Next
End Sub[/vba]
I keep getting a "Subscript out of range" error with the two commented out lines (just two methods I tried).

Any suggestions?

From help;
If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array.

You are Redim the 1st which is not allowed.

[malik641]

Awesome! Thanks Ken and Ivan!!!!!

I combined your two suggestions to give me what I need.

Here comes another KB baby!!! Oh yeah!

[mdmackillop]

Hi Joseph,
You could also consider using, say a 2 x 10 array, resize the 10 as required, and finally transpose to a 10 x 2 array for placing your data in a spreadsheet or whatever. There is a fairly low limit 5,400 items you can transpose in older versions of Excel, but I don't know what the limit is in 2003.
Regards
Malcolm

[malik641]

Malcom,

The i = 0 To 9 was just a small example. The thing was, that I don't know what the second dimension of the array will be (in my actual code), so that's why I can't define it in the beginning.

I don't have a reason to use the transpose in this case, but I never thought about doing that. Sounds useful, thanks