Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays/Redim/Help!
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! ;) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays/Redim/Help!
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 -- HTH... Jim Thomlinson "LarryP" wrote: 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! ;) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays/Redim/Help!
Had not realized that restriction on Redim. Your alternate approach appears
to work fine, many thanks. "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "LarryP" wrote: 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! ;) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why use redim when creating an array of arrays? | Excel Programming | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
Dim and Redim | Excel Programming | |||
ReDim, Preserve and Multidimensional arrays | Excel Programming |