vba - 加速计算

Question

通过大约 20K 次观察，以下代码需要大约 7.5 秒才能运行

'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)

没有k = IIf(J = 1, k + 1, k) 行(或If J = 1 Then k = k + 1)，不到一秒! !

有什么想法吗？

Answer 1

ReDim Preserve 可能会降低性能。每次使用时，它都会创建一个新数组并将现有数组复制进去。

您可以预先计算出 TrueUsedRangeArray 的大小，如下所示

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