excel - 为什么每个循环的 VBA 100,000 单元格需要这么长时间？

Question

更新：我知道有更快的方法来实现结果 - 我的问题是关于我的机器的相对性能。

我正在关注 Excel VBA 教程，其中讲师运行一个宏来创建一个新的工作表，然后“For Each”循环遍历 100,000 个单元格（A1 到 A100000），在每个单元格中设置一个任意值（例如“Hello”）。当她执行宏时，需要不到 5 秒的时间才能完成。

当我在自己的计算机上运行宏时，它需要很长时间（我没有计时，因为我通常会在沮丧中强制退出）。在循环中将代码从 100,000 个单元格更改为 10,000 个单元格，宏大约需要三分钟。

这是因为我使用的是旧计算机（和处理器）（Dell Optiplex 9020、8GB RAM、500GB HDD、Intel Core i5-4570 CPU @ ~3.2Ghz（4 个处理器））还是还有其他可能的原因？

在宏运行时，任务管理器显示总 CPU 和内存利用率徘徊在 50% 左右，性能速度约为 3.4Ghz。

Sub Slow_code()
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    Dim ShNew As Worksheet
    Dim cell As Range
    
    StartTime = Timer
      
    Application.StatusBar = "Wait"
    Set ShNew = Worksheets.Add
    For Each cell In ShNew.Range("A1:A10000")
        cell.Value = 10
    Next cell
    
    ShNew.Select
    Application.StatusBar = ""
        
    SecondsElapsed = Round(Timer - StartTime, 2)
    
    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
    
End Sub

---

找到罪魁祸首：停用插件包 (FastExcel v4)，速度提高到与讲师和其他用户相当的水平。

标签： excelvbaperformanceloopscpu

Answer 1

在 VBA 中使用数组写入数据比在 for 循环中一一添加要快得多。

在以下示例中获得战利品

Sub ArrayFillRange()

'turning off screen updating makes calculations happen faster
    Application.ScreenUpdating = False

' Create Sheet    
    Set ShNew = Worksheets.Add

'   Fill a range by transferring an array
    Dim CellsDown As Long
    Dim i As Long
    Dim TempArray() As Double
    Dim TheRange As Range
 
'   Change these values
    CellsDown = 100000

'   Redimension temporary array
    ReDim TempArray(1 To CellsDown)
 
'   Set worksheet range
    Set TheRange = Range(Cells(1, 1), Cells(CellsDown, 1))
 
'   Fill the temporary array 
    For i = 1 To CellsDown
        TempArray(i) = 10
    Next i
 
'   Transfer temporary array to worksheet
    TheRange.Value = TempArray

'   Turning screen updating back on
    Application.ScreenUpdating = True

End Sub

附带说明一下，我不确定为什么您的代码在您的计算机上运行时间如此之长，因为它在我的计算机上运行了 0.34 秒。

如果您对速度差异感兴趣，请看下面，我已经平均了两个选项（数组和 for 循环）的运行时间超过 5 次运行，行数越来越多。

您可以清楚地看到数组优于 for 循环。