☰

如何使用数据结构而不是 for-next 循环来提高代码速度？

tag-icon • tag-icon microsoft-excel vba
如何使用数据结构而不是 for-next 循环来提高代码速度？

我已经使用Application.Indexwith解决了其他几个问题Application.WorksheetFunction.Match，并将执行时间从大约 7-8 秒缩短到几毫秒。但我觉得仍有改进的空间。

Index我应该使用带有和的数组Match吗？

我也被告知要使用Scripting.Dictionary，但我正在寻找可以演示如何在这种情况下立即做到这一点的人。因为在我的脑海中，我必须先用循环填充字典，然后才能使用它，所以在速度方面它不会相似吗？

'Production Quantity for Dashboard
For i = 2 To Total_rows_Prod
    For j = 2 To Total_rows_Dash
        If ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 5) = ThisWorkbook.Worksheets("Dashboard").Cells(j, 1) Then
           ThisWorkbook.Worksheets("Dashboard").Cells(j, 4) = ThisWorkbook.Worksheets("Dashboard").Cells(j, 4) + ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 31) / ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 4)
        End If
    Next j
Next i

经过一些瓶颈测试之后如下所示（代码运行时间显示在第 10 行）： 在此处输入图片描述

但是，当使用Index和Matchwhile 时仅使用 1 个for-next循环，如下面的代码所示：

'Production Quantity for Dashboard
For i = 2 To Total_rows_Prod
    m = Application.Match(ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 5), ThisWorkbook.Worksheets("Dashboard").Range("A:A"), 0)
    If Not IsError(m) Then
        ThisWorkbook.Worksheets("Dashboard").Cells(Application.WorksheetFunction.Match(ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 5), ThisWorkbook.Worksheets("Dashboard").Range("A:A"), 0), 4) = ThisWorkbook.Worksheets("Dashboard").Cells(Application.WorksheetFunction.Match(ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 5), ThisWorkbook.Worksheets("Dashboard").Range("A:A"), 0), 4) + ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 31) / ThisWorkbook.Worksheets("Prod. Qty.").Cells(i, 4)
    End If
Next i

运行时间可以忽略不计，如下所示（仍在第 10 行）：

在此处输入图片描述

我最后一次能够让所有东西运行Index并Match替换的时间是 2 秒：

在此处输入图片描述

但在运行 Pentium Atom 处理器的较慢上网本上，执行相同代码需要 26 秒。所以我想知道是否有办法缩短这 26 秒。

任何能够使执行时间最小化的改进都是很好的。考虑使用字典，但我不知道如何应用它们，因为有一个KeyandValue参数，而且在我看来，做同样的事情.Add需要 2 个循环？for-next

答案1

通常，VBA 代码中最耗时的部分是从工作表读取值和将值写入工作表。您应该尽可能减少这一部分。

减少此类操作的最简单方法是将输入数据读入数组，根据需要对其进行操作，然后写回输出。
您可以在此处阅读更多相关信息：http://www.cpearson.com/Excel/ArraysAndRanges.aspx

更新后的代码将如下所示：

Dim arr_prodQty5 As Variant
Dim arr_DashBoard1 As Variant
Dim arr_DashBoard4 As Variant
Dim arr_prodQty31 As Variant
Dim arr_prodQty4 As Variant

arr_prodQty5 = ThisWorkbook.Worksheets("Prod. Qty.").Range(Cells(2, 5), Cells(Total_rows_Prod, 5))
arr_prodQty4 = ThisWorkbook.Worksheets("Prod. Qty.").Range(Cells(2, 4), Cells(Total_rows_Prod, 4))
arr_prodQty31 = ThisWorkbook.Worksheets("Prod. Qty.").Range(Cells(2, 5), Cells(Total_rows_Prod, 5))
arr_DashBoard1 = ThisWorkbook.Worksheets("Dashboard").Range(Cells(2, 1), Cells(total_rows_dash, 1))
arr_DashBoard4 = ThisWorkbook.Worksheets("Dashboard").Range(Cells(2, 4), Cells(total_rows_dash, 4))

For i = 2 To Total_rows_Prod
    For j = 2 To total_rows_dash
        If arr_prodQty5(i, 1) = arr_DashBoard1(j, 1) Then
           arr_DashBoard4(j, 1) = arr_DashBoard4(j, 1) + arr_prodQty31(i, 1) / arr_prodQty4(i, 1)
        End If
    Next j
Next i

ThisWorkbook.Worksheets("Dashboard").Range(Cells(2, 4), Cells(total_rows_dash, 4)) = arr_DashBoard4

（我没有您的数据，所以我无法检查它是否真的有效，很可能您需要在运行它之前修复一些错误）

使用字典而不是数组只是一种实现相同结果的更复杂的方法。

相关内容