Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a robust transpose(array) function
Figured it out....
UBound(vaData) _and_ UBound(vaData,1) both are the same... to get the 807 result for the ReDim I needed UBound(vaData,2) I guess variant array bounds are automatically option base 1 no matter what option base the module uses. Is that correct? -- Regards, John "John Keith" wrote: I am trying to load an ADO record set to a Listbox on a form. By using... vadata = rs.GetRows The records set is loaded into a variant array, but I need to transpose the array so my records go left-to-right instead of top-down. I found this gem: Application.WorksheetFunction.Transpose(vaData) but when the record set has more then 255 rows, it gets a type mismatch. I think this is because excel won't handle that many columns. Here is what I have attempted (unsuccessfully): Function Transpose2D(vaData) As Variant 'Transpose the input array swapping rows for columns Dim i As Long, j As Long Dim vaTransposed As Variant ReDim vaTransposed(LBound(vaData, 1) To UBound(vaData, 1), _ LBound(vaData) To UBound(vaData)) As Variant For i = LBound(vaData) To UBound(vaData) For j = LBound(vaData, 1) To UBound(vaData, 1) vaTransposed(j, i) = vaData(i, j) Next j Next i Transpose2D = vaTransposed Set vaTransposed = Nothing End Function vaData is reported as "Variant/Variant(0 to 4, 0 to 807)" but the results of the reDim are (0 to 4, 0 to 4). The function returns the first 5 rows, but the rest are dropped. I seem to recall that ReDim can not handle ReDim'ing multi-dim arrays. How do I get around this ReDim limitation, or is there a better way to transpose large arrays? -- Regards, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a robust transpose(array) function
No, Ubound and Lbound are not arrays. the second argument tells them which
dimension you want. 1st dimension, 2nd dimension, There is no 0th dimension. If you don't specify a dimension, it defaults to 1st. -- Regards, Tom Ogilvy "John Keith" wrote: Figured it out.... UBound(vaData) _and_ UBound(vaData,1) both are the same... to get the 807 result for the ReDim I needed UBound(vaData,2) I guess variant array bounds are automatically option base 1 no matter what option base the module uses. Is that correct? -- Regards, John "John Keith" wrote: I am trying to load an ADO record set to a Listbox on a form. By using... vadata = rs.GetRows The records set is loaded into a variant array, but I need to transpose the array so my records go left-to-right instead of top-down. I found this gem: Application.WorksheetFunction.Transpose(vaData) but when the record set has more then 255 rows, it gets a type mismatch. I think this is because excel won't handle that many columns. Here is what I have attempted (unsuccessfully): Function Transpose2D(vaData) As Variant 'Transpose the input array swapping rows for columns Dim i As Long, j As Long Dim vaTransposed As Variant ReDim vaTransposed(LBound(vaData, 1) To UBound(vaData, 1), _ LBound(vaData) To UBound(vaData)) As Variant For i = LBound(vaData) To UBound(vaData) For j = LBound(vaData, 1) To UBound(vaData, 1) vaTransposed(j, i) = vaData(i, j) Next j Next i Transpose2D = vaTransposed Set vaTransposed = Nothing End Function vaData is reported as "Variant/Variant(0 to 4, 0 to 807)" but the results of the reDim are (0 to 4, 0 to 4). The function returns the first 5 rows, but the rest are dropped. I seem to recall that ReDim can not handle ReDim'ing multi-dim arrays. How do I get around this ReDim limitation, or is there a better way to transpose large arrays? -- Regards, John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose array sum | Excel Worksheet Functions | |||
transpose - array problem | Excel Discussion (Misc queries) | |||
Transpose Array | Excel Discussion (Misc queries) | |||
Conditional transpose to Array | Excel Discussion (Misc queries) | |||
Transpose Function not Working with Long Array Elements | Excel Programming |