arrays - vba - 循环内的循环冻结excel
问题描述
我正在尝试创建一个循环来通过一个数组(47193, 4)和一个名为攻击(41892,1)的数组 2。这里的想法是攻击数组具有从工作表中按顺序排列的值,我稍后将这些值添加到下一列,这就是我将值添加到第三个数组的原因。因此,循环将逐一遍历攻击数组中的值,同时遍历 arr 数组以查找公共数据。我尝试将值直接复制到工作表,但 excel 冻结了很多。现在通过这种方式,excel此时仍然冻结。有什么问题吗?
Dim arr3() As Variant
Dim dee As Long
ReDim arr3(UBound(attacks, 1), 1)
For k = 0 To UBound(attacks, 1)
j = 0
For j = 0 To UBound(arr, 1)
If attacks(k, 0) = arr(j, 0) And attacks(k, 1) = arr(j, 2) Then
arr3(dee, 0) = attacks(k, 0)
arr3(dee, 1) = attacks(k, 1)
de = dee + 1
End If
Next j
Next k
解决方案
这是一些显示如何使用字典的代码:
Sub Tester()
Const SZ As Long = 10000 'size of test arrays
Dim arr1(1 To SZ, 1 To 2)
Dim arr2(1 To SZ, 1 To 2)
Dim arr3(1 To SZ, 1 To 2) '<<matches go here
Dim n As Long, m As Long, i As Long, t, dict, k
t = Timer
'fill test arrays with random data
For n = 1 To SZ
arr1(n, 1) = CLng(Rnd * 200)
arr1(n, 2) = CLng(Rnd * 200)
arr2(n, 1) = CLng(Rnd * 200)
arr2(n, 2) = CLng(Rnd * 200)
Next n
Debug.Print "Filled test arrays", Timer - t
t = Timer
'test the nested loop approach
For n = 1 To SZ
For m = 1 To SZ
If arr1(n, 1) = arr2(m, 1) And arr1(n, 2) = arr2(m, 2) Then
i = i + 1
arr3(i, 1) = arr1(n, 1)
arr3(i, 2) = arr1(n, 2)
End If
Next m
Next n
Debug.Print "Finished nested loop", Timer - t, i & " matches"
t = Timer
'create a lookup using a dictionary
Set dict = CreateObject("scripting.dictionary")
For n = 1 To SZ
k = arr1(n, 1) & "|" & arr1(n, 2)
dict(k) = dict(k) + 1
Next n
Debug.Print "Filled dictionary", Timer - t
t = Timer
i = 0
Erase arr3
'Perform the match against arr2 using the dictionary
For m = 1 To SZ
k = arr2(m, 1) & "|" & arr2(m, 2)
If dict.exists(k) Then
i = i + 1
arr3(i, 1) = arr2(m, 1)
arr3(i, 2) = arr2(m, 2)
End If
Next m
Debug.Print "Finished dictionary loop", Timer - t, i & " matches"
End Sub
输出:
Filled test arrays 0
Finished nested loop 9.101563 2452 matches
Filled dictionary 0.03125
Finished dictionary loop 0.0078125 2177 matches
请注意,匹配数略有不同 - 嵌套循环捕获重复匹配,但 Dictionary 仅计算唯一匹配。您可能需要根据您的用例进行调整。
推荐阅读
- selenium-webdriver - 有人可以解释这段代码,它帮助我解决了这个错误“在点(1158、533)不可点击。其他元素会收到点击”
- c# - JsReport 食谱 ChromePdf 页码
- flutter - 在 Flutter 中初始化通知的正确位置
- javascript - 在 Gatsby 中安装新插件后解决 .cache 中的 `prop-types`
- ios - 慢速 iOS 应用开始使用 Xcode 10.2 + iOS 12.2
- java - 通过 JMX 的远程连接似乎挂在 visualVM 中
- cgal - CGAL:有效地查找体积中的所有三角形(边界框或球查询)
- c# - 为什么枚举参数不能接受重载方法中的int值(> 0)
- c# - 错误消息未使用 asp.net core 1.0.1 web api 应用程序发送到 Rollbar 仪表板
- angularjs - 如何删除#!在 URL 溢出?