excel - 提高生成随机数的嵌套循环的速度

Question

我正在努力提高代码的速度。

我添加了一个计时器来确定我的代码运行需要多长时间。每 1000 次迭代大约需要 4.14。

我读过一些关于写入数组并读回的文章，但如何在这里应用这个想法？或许，还有另一种方法。

Sub Random()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False

    Dim wksData As Worksheet
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    Dim x As Double
    Dim i As Double    

    Set wksData = Sheets("Data")
    wksData.Range("O3:P1048576").ClearContents             
    StartTime = Timer
    With wksData
        For x = 3 To 1002
            For j = 3 To 161
                .Range("O" & j) = Rnd()  
            Next j
            wksData.Calculate
            .Range("P" & x) = .Range("N1")
        Next x
    End With
    SecondsElapsed = Round(Timer - StartTime, 2)
    MsgBox "Macro ran successfully in " & SecondsElapsed & " seconds", vbInformation

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True

End Sub

N1 对工作表上的公式得出的成本求和。每次生成一系列随机数时，我都会计算成本。

Answer 1

在内循环中实现数组解决方案后，速度提高了 10 倍(至少在我的简化测试文件中)。

Sub Random()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False

    Dim wksData As Worksheet
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    Dim x As Integer
    Dim j As Byte
    Dim ar As Variant

        Set wksData = Sheets("Data")
        wksData.Range("O3:P1048576").ClearContents
        StartTime = Timer
            With wksData
                For x = 3 To 1002
                    ReDim ar(1 To 159, 1 To 1)
                    For j = 1 To UBound(ar, 1)
                        ar(j, 1) = Rnd()
                    Next
                    .Range("O3").Resize(UBound(ar, 1)).Value = ar
                    wksData.Calculate
                    Erase ar
                    .Range("P" & x) = .Range("N1")
                Next 'x
            End With
        SecondsElapsed = Round(Timer - StartTime, 2)
        MsgBox "Macro ran successfully in " & SecondsElapsed & " seconds", vbInformation

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True

End Sub

他们想要实现数组的关键思想是减少 VBE 和工作表之间的转换次数。在之前的代码中，您必须在每次迭代中转到工作表将数据写入单元格。现在，您对数组中的值执行 Rnd() 操作，该数组在完成之前不会存储在工作表中。完成后，您将转到工作表并输出结果!

其他变化非常小。我用 Integer 和 Byte 替换了 Double，因为它们需要更少的内存。另外，在 For ... Next 中，我在 Next 之后删除了重复的变量。这样做没有意义，编译器知道它位于哪个循环中。