将列向量转置为行向量

由头文字D发布于 2020-05-18 04:11:04

我本应该面对一个简单的任务，但是我发现了一些问题，无法将一维数组/列向量[0..n，0..0]转换为一维数组/行向量[0..0，0..n ]。

我尝试使用Application.WorksheetFunction.Transpose内置函数没有成功。它似乎只适用于nD数组/矩阵。

上下文是： -一维数组/列向量来自Recordset.GetRows方法（如果Recordset.Recordcount = 1 =>数组是一维数组/列向量） -一维数组/行向量（通过转置函数获得）用于填充列表框对象的listbox.list属性

有没有一种聪明的方法来转置一维数组（从列向量到行向量，反之亦然）？

预先感谢您的任何帮助

Viola

2020-05-18 04:11:04

转置零基数组

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.

编码

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

头文字D

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3299
注册排名
2487