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

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