arrays - VBA 嵌套 For 循环效率

Question

我正在尝试找到在 VBA 中执行任务的最快方法。目前我把它写成一个嵌套的 for 循环，这可能非常慢。我正在循环访问唯一数字的列表，并将它们与不同列表中的数字进行匹配。如果我得到一个匹配项，我会将信息存储在多维数组中，因为可能有多个匹配项，并且我想跟踪所有匹配项。不幸的是，这意味着当使用 for 循环时，如果只有 1000 个唯一数字和 5000 个数字来查找匹配项，我的循环最终可能会迭代 1000*5000 = 5000000 次。如您所见，这很快就会产生问题。我想问一下在VBA中是否有更好的方法来解决这个问题。我已经完成了所有技巧，例如将 screenUpdating 设置为 false 并将计算设置为 manaul。

这是我的代码:

For x = 0 To UBound(arrUniqueNumbers)
    Dim arrInfo() As Variant
    ReDim Preserve arrInfo(0)
    If UBound(arrInfo) = 0 Then
        arrInfo(0) = CStr(arrUniqueNumbers(x))
    End If

    For y = 2 To Length
        UniqueString = CStr(arrUniquePhoneNumbers(x))
        CLEARString = CStr(Sheets(2).Range("E" & y).Value)
        If UniqueString = CLEARString Then 'match!
            NormalizedDate = Format(CStr(Sheets(2).Range("G" & y).Value), "yyyymmdd")
            z = z + 1
            ReDim Preserve arrInfo(z)
            arrInfo(z) = NormalizedDate & " " & LTrim(CStr(Sheets(2).Range("D" & y).Value))
            arrInfo(z) = LTrim(arrInfo(z))
        End If
    Next

    arrUniqueNumbers(x) = arrInfo()
    ReDim arrInfo(0)  'erase everything in arrOwners
    z = 0
Next

Answer 1

循环效率相当低，因此有很多可以避免的瓶颈(大部分按照从最简单到最复杂的顺序排列)

1. 将 UniqueString 步骤从最内层循环中取出:此步骤不会随着 y 的变化而改变，因此没有必要重复它.
2. 将 Redim Preserve 从最内层循环中取出:您正在最内层循环中重新分配内存，这是极其低效的。在循环外分配“足够”的内存。
3. 不要继续使用 Sheets().Range() 访问单元格内容:每次访问电子表格上的某些内容时，都会产生巨大的阻力并产生与访问相关的大量开销。考虑从电子表格中一步提取操作，以及将结果一步推送操作返回到电子表格。请参阅下面的示例代码。

电子表格的高效获取和推回操作的示例代码:

Dim VarInput() As Variant
Dim Rng As Range

' Set Rng = whatever range you are looking at, say A1:A1000

VarInput = Rng
' This makes VarInput a 1 x 1000 array where VarInput(1,1) refers to the value in cell A1, etc.
' This is a ONE STEP fetch operation

' Your code goes here, loops and all

Dim OutputVar() as Variant
Redim OutputVar(1 to 1000, 1 to 1)

' Fill values in OutputVar(1,1), (1,2) etc. the way you would like in your output range

Dim OutputRng as Range
Set OutputRng = ActiveSheet.Range("B1:B1000")
' where you want your results

OutputRng = OutputVar
' ONE STEP push operation - pushes all the contents of the variant array onto the spreadsheet

还有很多其他步骤可以进一步显着加速您的代码，但这些应该不需要太多努力就能产生明显的影响。