Transpose rows column in GetRows

Question

Below VBA code does the job, but I'm losing some 3 sec in the transpose part.

Is there a way I can get the same result or in the SQL query or in the getrows process without losing the 3 secs?

Sub LoadData()
    Dim strCon, srtQry As String, tmpArray, tmpArray2, R As Variant, i, j As Long

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    strCon = "DRIVER={MySQL ODBC 5.2 ANSI Driver};" & _
            "SERVER=localhost;" & _
            "DATABASE=tbname;" & _
            "USER=root;" & _
            "PASSWORD=pass;" & _
            "Port=3306;" & _
            "Option=3"
    
    cn.Open strCon

    srtQry = "SELECT * FROM `tbname` WHERE `FileDay` = 20131220"
    
    Set rs = cn.Execute(srtQry)
    
    tmpArray = rs.GetRows
     
    cn.Close
     
    tmpArray2 = TransposeArray(tmpArray)
     
End Sub

TransposeArray:

Public Function TransposeArray(InputArr As Variant) As Variant

    Dim RowNdx, ColNdx, LB1, LB2, UB1, UB2 As Long, tmpArray As Variant

    LB1 = LBound(InputArr, 1)
    LB2 = LBound(InputArr, 2)
    UB1 = UBound(InputArr, 1)
    UB2 = UBound(InputArr, 2)

    ReDim tmpArray(LB2 To LB2 + UB2 - LB2, LB1 To LB1 + UB1 - LB1)

    For RowNdx = LB2 To UB2
        For ColNdx = LB1 To UB1
            tmpArray(RowNdx, ColNdx) = InputArr(ColNdx, RowNdx)
        Next ColNdx
    Next RowNdx

    TransposeArray = tmpArray

End Function

@Doug Glancy. My version of the usual excel-vba array transpose code. Will edit and include. — , Jan 06 '14 at 22:02

score 3 · Answer 1 · answered Jan 07 '14 at 00:04

3

There are a few optimisations you can apply

Declarations: you need to specify the data type of each variable
Remove redundant calculations in Redim
Use the more compact For loop structure
Specify your variants as arrays
And for most impact: Use a Sub rather than Function

These together will reduce run time of the Transpose by more than 50%

Public Sub TransposeArray(ByRef InputArr() As Variant, ByRef ReturnArray() As Variant)
    Dim RowNdx As Long, ColNdx As Long
    Dim LB1 As Long, LB2 As Long, UB1 As Long, UB2 As Long

    LB1 = LBound(InputArr, 1)
    LB2 = LBound(InputArr, 2)
    UB1 = UBound(InputArr, 1)
    UB2 = UBound(InputArr, 2)

    ReDim ReturnArray(LB2 To UB2, LB1 To UB1)

    For RowNdx = LB2 To UB2
    For ColNdx = LB1 To UB1
        ReturnArray(RowNdx, ColNdx) = InputArr(ColNdx, RowNdx)
    Next ColNdx, RowNdx

End Sub

Call it like this

TransposeArray tmpArray, tmpArray2

answered Jan 07 '14 at 00:04

chris neilsen

52,446
10
84
123

Great!. But I'm having a compile error:'Type mismatch:array or user-defined type expected'. What am I missing? – Jan 07 '14 at 09:49
right when I call `TransposeArray tmpArray, tmpArray2`, highlighting `tmpArray` – Jan 07 '14 at 10:55
It works! All I had to do was include '()' when I dim the arrays so `Dim tmpArray(), tmpArray2() As Variant`. Guess that's what you meant by '4- Specify your variants as arrays'. Not the solution I was initially looking for but indeed speeds up by 50%! – Jan 08 '14 at 11:24
Thanxs for this Answer. Cleaning up one's code is something that we all need to be better at. – John Shaw Sep 28 '21 at 12:32

score 1 · Answer 2 · answered Aug 05 '21 at 11:34

1

Know this is ancient - but if you are in Excel as your tags suggest - then you will probably find that Application.WorksheetFunction.Transpose() is a great deal faster (not tried it - but I believe a good hunch)

answered Aug 05 '21 at 11:34

BenMcBen

11
2

Transpose rows column in GetRows

2 Answers2

Linked