API reference · Arrays

Transpose

Swap the two dimensions of a 2D array using explicit VBA loops.

FunctionArrays→VariantSource L111

Signature#

Function Transpose(ByVal vArr As Variant) As Variant

Parameters#

Parameter	VBA type	Default	Description
`vArr`	`Variant`ByVal	Required	Allocated 2D array. A scalar, a 1D array, or another rank returns Empty.

Returns#

Variant containing a 2D array. Output row bounds equal the source column bounds, and output column bounds equal the source row bounds. Returns Empty for unsupported inputs.

Example#

SEE THE TRANSFORMATIONSwap rows and columns

Before

quarterly2D · 3 × 1

Rows 1..3 · Columns 1..1

row / col	1
1	100
2	120
3	140

After

horizontal2D · 1 × 3

Rows 1..1 · Columns 1..3

row / col	1	2	3
1	100	120	140

Both dimension lengths and their bounds exchange places.

Shaded headers are array indices, not data. Quoted cells are text. Values shown are derived from the source, not an Excel execution.

Sub ExampleTranspose()
    Dim a As New Arrays
    Dim quarterly As Variant, horizontal As Variant

    ReDim quarterly(1 To 3, 1 To 1)
    quarterly(1, 1) = 100
    quarterly(2, 1) = 120
    quarterly(3, 1) = 140
    horizontal = a.Transpose(quarterly)
    Debug.Print horizontal(1, 1), horizontal(1, 3) ' Expected: 100, 140
End Sub

Behavior and edge cases#

The output element at (row, column) receives the input element at (column, row). This is a new array and leaves the source unchanged.
Nonstandard lower bounds move with their dimensions. Transposing an array bounded (5 To 7, -2 To -1) produces bounds (-2 To -1, 5 To 7).
A single-row or single-column input remains 2D. Use DimReduce when a 1D result is required.
The implementation does not call Excel's TRANSPOSE function. Values, including Empty, Null and error values, are copied without conversion.

Performance#

O(r × c) time and O(r × c) output storage for r rows and c columns.

View implementation

Function Transpose(ByVal vArr As Variant) As Variant
    'Transposes a two-dimensional array by swapping its rows and columns

    Dim vArr_Out        As Variant
    Dim iRow            As Long
    Dim iCol            As Long

    If Not IsArray(vArr) Then Exit Function
    If Me.DimCounter(vArr) <> 2 Then Exit Function
    ReDim vArr_Out(LBound(vArr, 2) To UBound(vArr, 2), LBound(vArr, 1) To UBound(vArr, 1))
    For iRow = LBound(vArr_Out, 1) To UBound(vArr_Out, 1)
        For iCol = LBound(vArr_Out, 2) To UBound(vArr_Out, 2)
            vArr_Out(iRow, iCol) = vArr(iCol, iRow)
        Next iCol
    Next iRow

    Transpose = vArr_Out
End Function