数组

'定义一维数组并赋值
Sub test()
Dim arr(1 To 4)

arr(1) = "张三"
arr(2) = "李四"
arr(3) = "王五"
End Sub

'定义二维数组并赋值
Sub test1()
Dim arr(1 To 4, 1 To 2)

arr(1, 1) = "张三"
arr(1, 2) = 30
arr(2, 1) = "李四"
arr(2, 2) = 35
arr(3, 1) = "王五"
arr(3, 2) = 40
End Sub


'将数组中的某个值输出到单元格
Sub test3()
Dim arr(1 To 4)

arr(1) = "张三"
arr(2) = "李四"
arr(3) = "王五"

Range("b2") = arr(2)
End Sub

'将一维数组中的所有值输出到单元格区域
Sub test4()
Dim arr(1 To 4)

arr(1) = "张三"
arr(2) = "李四"
arr(3) = "王五"

Range("a7:d7") = arr
End Sub


'将二维数组中的所有值输出到单元格区域
Sub test5()
Dim arr(1 To 3, 1 To 2)

arr(1, 1) = "张三"
arr(1, 2) = 30
arr(2, 1) = "李四"
arr(2, 2) = 35
arr(3, 1) = "王五"
arr(3, 2) = 40

Range("a15:b17") = arr

'将区域赋值给数组

Sub test6()
Dim arr()

arr = Range("a1:a5")

End Sub

Sub test()
Dim i, k
Dim t
Dim str As String
Dim arr()


t = Timer

arr = Range("g1:j200000")
str = Range("n5")
For i = 2 To 200000
    If arr(i, 1) = str Then
        k = k + arr(i, 4)
    End If
Next


Range("p5") = k


MsgBox Timer - t

End Sub

数组查找

Sub test()
Dim arr()
Dim j, i As Integer

j = Range("a65536").End(xlUp).Row - 1

ReDim arr(1 To j)

For i = 1 To j
    arr(i) = Range("b" & i + 1) * Range("c" & i + 1)
Next


Range("h3") = Application.WorksheetFunction.Max(arr)
Range("h2") = Range("a" & Application.WorksheetFunction.Match(Range("h3"), arr, 0) + 1)

MsgBox LBound(arr)

End Sub

排列组合计算信息

Sub test()
Dim i, j, k, l As Integer
Dim arr()

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("i3") = arr(l, 1)
                    GoTo 100
                End If
            Next
        Next
    Next
Next


100
MsgBox Format(Timer - t, "0.00000")

End Sub