Excel Transpose function

[AlexanderBB]

Google says If you want to redim the first dimension of an 2 dimensional array, you can use a trick: Transpose the array, change the second dimension and transpose the array again.
Then shows the worksheet function transpose as an example.
Is it possible to do this with code in a module instead, as the platform I want to use this on doesn't have a worksheet ?

 

[RoryA]

Rather than trying to hack an Excel hack, it would probably be better to explain what you are doing, and in which application.

 

[AlexanderBB]

I did find something. Should work in any VBA app.

'@Description("Transposes the values in a 2d array. Rows become columns, columns become rows.")
Private Function Transpose2DArray(ByRef sourceArray() As Variant) As Variant()
    Dim currentRow As Long
    Dim LowerBoundRow As Long
    Dim UpperBoundRow As Long
    Dim currentColumn As Long
    Dim LowerBoundCol As Long
    Dim UpperBoundCol As Long
    Dim result() As Variant
    
    LowerBoundCol = LBound(sourceArray, 1)
    UpperBoundCol = UBound(sourceArray, 1)
    LowerBoundRow = LBound(sourceArray, 2)
    UpperBoundRow = UBound(sourceArray, 2)
    ReDim result(LowerBoundRow To UpperBoundRow, LowerBoundCol To UpperBoundCol)
    For currentRow = LowerBoundRow To UpperBoundRow
        For currentColumn = LowerBoundCol To UpperBoundCol
            result(currentRow, currentColumn) = sourceArray(currentColumn, currentRow)
        Next
    Next
   Transpose2DArray= result

End Function