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, 2) To UBound(vaData, 2), _
LBound(vaData,1) To UBound(vaData,1)) As Variant
For i = LBound(vaData,1) To UBound(vaData,1)
For j = LBound(vaData, 2) To UBound(vaData, 2)
vaTransposed(j, i) = vaData(i, j)
Next j
Next i
Transpose2D = vaTransposed
End Function

--
regards,
Tom Ogilvy


"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