Excel VBA - 循环访问存储在字典中的二维数组(性能)

Question

我有一段代码在 2D 数组中执行一些操作，这些数组本身存储在字典中。

我的目标是遍历字典，并迭代每个项目(2D 数组(以执行一些操作。然后，这些 2D 数组的每一行都存储在最终的 2D 数组中。

代码工作得很好，但非常慢，知道2D数组一点也不大(顶部3000行和顶部10列(。

Private Function Process_Data(ByVal var1 As Variant, ByVal var2 As Variant, ByVal var3 As Variant, ByVal var4 As Variant, ByVal var5 As Variant)
Dim res As Variant
Dim size_dim1 As Long: Dim size_dim2 As Long
Dim dic As New Dictionary
Dim i As Long: Dim j As Long: Dim k As Long
Dim key As Variant
'Fill dic with parameters
dic.Add 1, var1
dic.Add 2, var2
dic.Add 3, var3
dic.Add 4, var4
dic.Add 5, var5
'Dim final array
size_dim1 = UBound(var1, 1) + UBound(var2, 1) + UBound(var3, 1) + UBound(var4, 1) + UBound(var5, 1)
size_dim2 = 8   'col H
ReDim res(1 To size_dim1, 1 To size_dim2)
j = 1
'Various elements in the final 2D array (res)
For Each key In dic.Keys
For i = LBound(dic(key), 1) To UBound(dic(key), 1)
res(j, 1) = dic(key)(i, 1)     'Whatever happens here
res(j, 2) = dic(key)(i, 2)
res(j, 3) = dic(key)(i, 3)
res(j, 4) = dic(key)(i, 4)
res(j, 5) = dic(key)(i, 5)
res(j, 6) = ""
res(j, 7) = ""
res(j, 8) = ""
j = j + 1
Next i
Next key
Process_Data = res
End Function

你有什么建议来改进我的代码吗？

非常感谢您的时间！ 克里斯

Answer 1

您没有利用使用字典的任何优势(至少如代码所示(，但您会遭受字典 5*size_dim1 次的管理开销。我会以这种方式重构您的代码：

...
j = 1
j=Process_Data_Add_Buf (res, var1, j)
j=Process_Data_Add_Buf (res, var2, j)
j=Process_Data_Add_Buf (res, var3, j)
j=Process_Data_Add_Buf (res, var4, j)
j=Process_Data_Add_Buf (res, var5, j)
Process_Data = res
End Sub
Private Function Process_Data_Add_Buf (byref OutBuf() as Variant, Byref InBuf() as variant, iRow as Long) as Long
Dim i as long
For i = LBound(InBuf, 1) To UBound(InBuf, 1)
OutBuf(iRow, 1) = InBuf(i, 1)     'Whatever happens here
...
iRow = iRow + 1
Next i
Process_Data_Add_Buf = iRow
End Function