时间戳

首页 > 解决方案 > Excel 数组和小型 VBA 循环与不带数组的大型 VBA 循环的性能

arrays - Excel 数组和小型 VBA 循环与不带数组的大型 VBA 循环的性能

问题描述

我有两个工作表。第一个（计算）包含 10.000 行，其中包含许多 RTD 公式和不同的计算。第二个（观察者）观察第一个。
我有一个每秒运行的 VBA 脚本并检查工作表 1（计算）的每一行。如果循环在工作表 1 上找到一些标记的数据，则它将一些数据从 WS1 复制到 WS2。

解决方案 1：循环检查 10.000 行

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For I = 1 To 10000
    If CStr(.Cells(I, 1)) = "X" Then
        'DO SOME SUFF (copy the line from WS 1 to WS2)
        'Find first empty row
        LR2 = WS2.Cells(15, 1).End(xlDown).Row + 1
        'Copy data from WS1 to WS2
        WS1.Range(.Cells(I, 1), .Cells(I, 14)).Copy
        WS2.Cells(LR2, 1).PasteSpecial xlValues
    End If
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

解决方案 2：带有小循环的数组函数
我可以使用 Excel 数组而不是 10.000 行循环来观察 10.000 行并使用较小的数组做一些事情。

在工作表 2 上，我会有以下代码：(A1:O15)

{=index(Calculation!A$1:$O$10000; .....)....))}

同样，我将通过 15 行数组函数进行更小的循环：

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For K = 1 To 15
    If CStr(.Cells(I, 1)) = "X" Then
        'Find first empty row
        LR2 = WS2.Cells(15, 1).End(xlDown).Row + 1
        'Copy data from WS1 to WS2
        WS1.Range(.Cells(I, 1), .Cells(I, 14)).Copy
        WS2.Cells(LR2, 1).PasteSpecial xlValues
    End If
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

我想知道哪种解决方案具有更好的性能。

我不确定超过 10.000 行的 Excel 数组是否具有良好的性能。当然，15-rowLoop 比 10000-row-Loop 快。

我不知道如何测量与数组连接的 15 行循环（观察 10.000 行）是否更快。

标签： arraysexcelvbaexcel-2016

解决方案

与其返回A列 10,000 次，不如将所有值放入 1D VBA 数组中，然后遍历该数组：

Sub whatever()
    Dim rng As Range, arr
    
    Set rng = Sheets("Calculation").Range("A1:A10000")
    arr = WorksheetFunction.Transpose(rng)
    
    For i = 1 To 10000
        If arr(i) = "X" Then
            'do some stuff
        End If
    Next i
End Sub

如果X很少，那么它可能几乎是瞬时的。

编辑＃1：

根据 Chris Neilsen 的评论，这是一个不使用的版本Transpose()：

Sub whatever2()
    Dim rng As Range, arr

    Set rng = Sheets("Calculation").Range("A1:A10000")
    arr = rng

    For i = 1 To 10000
        If arr(i, 1) = "X" Then
            'do some stuff
        End If
    Next i
End Sub

推荐阅读