Visual Basic > Applications-VBA

Transposing a 2d array

Transposing a 2d array

The code below can be used to transpose (or invert) a 2d variant array. Eg a 2d array with 2 columns and 6 rows, would become a 2d array with 6 columns and 2 rows:

'Purpose   :    Transposes a 2D array
'Inputs    :    avValues                    The array to transpose.
'Outputs   :    Returns the array transposed, or empty if an error occurs


Function Array2DTranspose(avValues As Variant) As Variant
    Dim lThisCol As Long, lThisRow As Long
    Dim lUb2 As Long, lLb2 As Long
    Dim lUb1 As Long, lLb1 As Long
    Dim avTransposed As Variant
    
    If IsArray(avValues) Then
        On Error GoTo ErrFailed
        lUb2 = UBound(avValues, 2)
        lLb2 = LBound(avValues, 2)
        lUb1 = UBound(avValues, 1)
        lLb1 = LBound(avValues, 1)
        
        ReDim avTransposed(lLb2 To lUb2, lLb1 To lUb1)
        For lThisCol = lLb1 To lUb1
            For lThisRow = lLb2 To lUb2
                avTransposed(lThisRow, lThisCol) = avValues(lThisCol, lThisRow)
            Next
        Next
    End If
    
    Array2DTranspose = avTransposed
    Exit Function

ErrFailed:
    Debug.Print err.description
    Debug.Assert False
    Array2DTranspose = Empty
    Exit Function
    Resume
End Function