VBA之数组的基本应用和for循环比较

数组的应用:

代码1:

Sub test()
Dim i, j, k, l As Integer
t = Timer
For i = 2 To 80
For j = 2 To 80
For k = 2 To 80
For l = 2 To 80

            If Range("a" & i) + Range("a" & j) + Range("a" & k) + Range("a" & l) = 124704 Then
                Range("f3") = Range("a" & i)
                Range("g3") = Range("a" & j)
                Range("h3") = Range("a" & k)
                Range("i3") = Range("a" & l)
                GoTo 100
            End If
            
        Next
    Next
Next

Next

100

MsgBox Timer - t

End Sub

说明:四层fo循环,查找起来特别慢

代码2:
使用数组

Sub test2()
Dim i, j, k, l As Integer
t = Timer

arr = Range(“a1:a80”)
For i = 2 To 80
For j = 2 To 80
For k = 2 To 80
For l = 2 To 80
If arr(i, 1) + arr(j, 1) + arr(k, 1) + arr(l, 1) = 124704 Then
Range(“f3”) = arr(i, 1)
Range(“g3”) = arr(j, 1)
Range(“h3”) = arr(k, 1)
Range(“l3”) = arr(l, 1)
GoTo 100
End If

        Next
    Next
Next

Next

100

MsgBox Format(Timer - t, “0.00000”)

End Sub

说明:速度提高200倍