Arrays/Redim/Help!

[LarryP]

Working with arrays gives me vertigo!

I want to loop through a recordset and store two pieces of information in an
array, re-sizing the array with Redim Preserve as I process each new record
in the recordset. The populated array should look like this:

Thing1, .25
Thing2, 1.43
Thing3, .07
etc.

But I'm not seeing the forest for the trees -- my code throws a "subscript
out of range" on the second Redim. I've tweaked it various ways, most recent
version is

x = 0
Rs.MoveFirst
Do While Not Rs.EOF
ReDim Preserve varPriceTable(x + 1, x + 1)
varPriceTable(x, 0) = Rs!LookupString
varPriceTable(x, 1) = Rs!ZPMILEPrice
x = x + 1
Rs.MoveNext
Loop

Somebody please help me take the blinders off! ;>)

 

[Jim Thomlinson]

The rule with multi dim arrays is that you can only redim the last element in
the array. Once created the first element is permanent. So code like this
will work...

Sub test()
Dim ary() As String

ReDim ary(10, 10)
ary(1, 1) = "Tada"
ReDim Preserve ary(10, 11)
End Sub

But code like this will not work
Sub test()
Dim ary() As String

ReDim ary(10, 10)
ary(1, 1) = "Tada"
ReDim Preserve ary(11, 11)
End Sub

It has to do with the way memory is stored. A multi dim array, like any
array, is stored as a big long memory string. Adding to the final element
adds another entire block to the end of the memory. Adding to the first
element(s) would require adding more memory to each of the already existing
block which it just will not do.

Have you considered use a type something like this...
'**At top of module
Type MyTpye
str as string
dbl as double
end type
'**

x = 0
Rs.MoveFirst
Do While Not Rs.EOF
ReDim Preserve varPriceTable(x+1)
varPriceTable(x).str = Rs!LookupString
varPriceTable(x).dbl = Rs!ZPMILEPrice
x = x + 1
Rs.MoveNext
Loop