Transpose Column vector to row vector

Question

I supposed to face with a simple task, but I finding some problems to transpose a 1D array/Column vector [0..n, 0..0] to a 1D array/Row vector [0..0, 0..n].

I tried using the Application.WorksheetFunction.Transpose built-in function without success. It only seems to work with nD array/matrix.

The context is: - the 1D array/Column vector comes from a Recordset.GetRows method (if Recordset.Recordcount=1 => the array is a 1D array/Column vector) - the 1D array/Row vector (obtained by the transpose function) is used to populate the listbox.list property of a listbox object

Is there a smart way to transpose a 1D array (from Column vector to Row vector and viceversa) ?

Thanks in advance for any help

Answer 1

Transpose Zero-Based Arrays

• The problem with Application.Transpose is that it transposes a 1D any-based one-row array to a 2D one-based one-column array. Now when you try to transpose back you will end up with a 1D one-based one-row array (see TransposeIssue).
• Toggle Transpose will 'recognize' if the array is vertical or horizontal and will transpose accordingly (see toggleTransposeTest). It will accept only zero-based arrays.

The Code

Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Transposes a 1D zero-based (one-row) array                      '
'                       to a 2D zero-based one-column array and vice versa.    '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function toggleTranspose0(SourceArray As Variant) As Variant
    Dim Transpose, i As Long
    On Error Resume Next
    i = UBound(SourceArray, 2)
    If Err.Number <> 0 Then
        On Error GoTo 0
        If LBound(SourceArray) <> 0 Then Exit Function
        GoSub transposeVertical
    Else
        If i <> 0 Then Exit Function
        GoSub transposeHorizontal
    End If
    toggleTranspose0 = Transpose
Exit Function
transposeVertical:
    ReDim Transpose(UBound(SourceArray), 0)
    For i = 0 To UBound(SourceArray)
        Transpose(i, 0) = SourceArray(i)
    Next i
Return
transposeHorizontal:
    ReDim Transpose(UBound(SourceArray))
    For i = 0 To UBound(SourceArray)
        Transpose(i) = SourceArray(i, 0)
    Next i
Return
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub toggleTransposeTest()
    Dim v, t, i As Long
    ReDim v(9)
    ' Populate data to 1D array.
    For i = 0 To 9
        v(i) = i + 1
    Next i
    ' Transpose to 2D zero-based one-column array.
    t = toggleTranspose0(v)
    For i = 0 To 9
        Debug.Print t(i, 0)
    Next i
    ' Transpose back to 1D array.
    v = toggleTranspose0(t)
    For i = 0 To 9
        Debug.Print v(i)
    Next i
End Sub

Sub TransposeIssue()
    Dim v, t, i As Long
    ReDim v(9)
    ' Populate data to 1D zero-based one-row array.
    For i = 0 To 9
        v(i) = i + 1
        Debug.Print i, v(i)
    Next i
    ' Convert 1D array to a 1D one-based one-row array.
    t = Application.Transpose(Application.Transpose(v))
    For i = 1 To 10
        Debug.Print i, t(i)
    Next
    ' Transpose to 2D one-based one-column array.
    t = Application.Transpose(v)
    For i = 1 To 10
        Debug.Print i, t(i, 1)
    Next
    ' Transpose to 1D one-based one-row array.
    v = Application.Transpose(t)
    For i = 1 To 10
        Debug.Print i, v(i)
    Next

End Sub