使用数组
约 469 字大约 2 分钟
2024-07-07
数组
'定义一维数组并赋值
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