Speeding calculations

user_3387046 0
Viewed 93

With some 20K observations, the following code takes some 7.5 sec to run

'Remember time when macro starts
StartTime = Timer
For i = 2 To UBound(avTransposed, 2)
    For J = 1 To UBound(avTransposed, 1)
        k = IIf(J = 1, k + 1, k)
        '                    If J = 1 Then k = k + 1
        ReDim Preserve TrueUsedRangeArray(1 To Dim2, 1 To k)
        TrueUsedRangeArray(J, k) = avTransposed(J, i)
    Next
Next
'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)

Without the k = IIf(J = 1, k + 1, k) line (or If J = 1 Then k = k + 1), it takes less than one sec!!

Any idea?

vb6 vba
2 Answers

The ReDim Preserve is probably killing performance. Every time it is used, it creates a new array and copies the existing array in.

You can work out up-front the size of TrueUsedRangeArray, something like the following

ReDim TrueUsedRangeArray(1 To Ubound(avTransposed, 2), 1 To Ubound(avTransposed, 1))
Patrick_McDonald_619890

Too many things in your inner loop which do not need to be there:

For i = 2 To UBound(avTransposed, 2)
    k = k + 1
    ReDim Preserve TrueUsedRangeArray(1 To Dim2, 1 To k)
    For J = 1 To UBound(avTransposed, 1)
        TrueUsedRangeArray(J, k) = avTransposed(J, i)
    Next
Next

As Patrick notes though, you do not need the redim preserve in the loop, since you already know the final size of TrueUsedRangeArray from the dimensions of avTransposed

user_4788840
Related