vba - 优化 VBA Excel 中双循环的性能

Question

我有兴趣了解在双循环中执行一组指令以循环遍历二维单元格范围的最快方法。我的代码将是这样的:

Sub Test()

For i = 1 To 1000000
    For j = 1 To 10   'It can be more than 10
        'I put a set of instructions here
    Next j
Next i

End Sub

例如，假设我编写一个简单的代码来实现以下任务:

Sub Test1()
T0 = Timer
For i = 1 To 1000000
    For j = 1 To 10
        Cells(i, j) = j + Rnd()
    Next j
Next i
InputBox "The runtime of this program is ", "Runtime", Timer - T0
End Sub

我在我的机器上运行了过程Test1，花了 179.6406 秒才能完成。由于我没有声明变量(i 和 j)，因此 Test1 正在运行，这些变量默认为 Variant 类型。然后，我向 Test1 添加一行以将变量声明为长整型，因为 VBA 针对长整型进行了优化。新过程 Test2 将我机器上的运行时间缩短至 168.7539 秒(快了近 11 秒)。

为了提高 Test2 的性能，我关闭了 Test2 代码运行时不需要的 Excel 功能。

Sub Test3()
Dim i As Long, j As Long
T0 = Timer

ScreenUpdateState = Application.ScreenUpdating
StatusBarState = Application.DisplayStatusBar
CalcState = Application.Calculation
EventsState = Application.EnableEvents
DisplayPageBreakState = ActiveSheet.DisplayPageBreaks
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

For i = 1 To 1000000
    For j = 1 To 10
        Cells(i, j) = j + Rnd()
    Next j
Next i

Application.ScreenUpdating = ScreenUpdateState
Application.DisplayStatusBar = StatusBarState
Application.Calculation = CalcState
Application.EnableEvents = EventsState
ActiveSheet.DisplayPageBreaks = DisplayPageBreaksState

InputBox "The runtime of this program is ", "Runtime", Timer - T0
End Sub

上述方法有助于提高Test2的性能，并且Test3在我的机器上在96.13672秒内完成。所以我想知道是否有更有效的方法来做到这一点。有人能想出一个更快的版本吗？如果可能的话，甚至避免双循环过程。

Answer 1

我使用了内部 VBA 数组，运行时间不到 10 秒:

Sub QuickTest()
    Dim v(1 To 1000000, 1 To 10) As Double
    For i = 1 To 1000000
        For j = 1 To 10
            v(i, j) = j + Rnd
        Next j
    Next i

    Range("A1:J1000000") = v
End Sub

注意:

• 使用内部 VBA 数组可以让我们避免多次“接触”工作表
• 可以一步而不是循环地将数组传输到工作表单元格。

编辑#1:

考虑这个续集:

Sub QuickTest2_The_Sequel()
    Dim i As Long, j As Long, m As Long, n As Long
    Range("K1") = Evaluate("Now()")

    m = 10
    n = 1000000

    ReDim v(1 To n, 1 To m) As Double
    For i = 1 To n
        For j = 1 To m
            v(i, j) = j + Rnd
        Next j
    Next i

    Range("A1:J" & n) = v
    Range("K2") = Evaluate("Now()")
End Sub

这里我们使用单元格K1和K2来记录开始和停止时间。我们还使用 ReDim 而不是 Dim 来“参数化”限制: