VBA

从例子中了解什么是数组 ：在vba里数组就是一组变量

Sub ss1()
Dim i, k
t = Timer '这个是从今天0点开始已经过了多少秒的时间
For i = 2 To 200000
    If Range('g' & i) = Range('n5') Then '每一次取值都要到range('n5')里取值，所以很耽误时间
        k = k + Range('j' & i)
    End If
Next
Range('p5') = k
MsgBox Timer - t
End Sub

　　优化1

Sub ss2()
Dim i, k
Dim str As String

t = Timer
str = Range('n5') '这样就只取用一次
For i = 2 To 200000
    If Range('g' & i) = str Then '从变量里面取值
        k = k + Range('j' & i)
    End If
Next
Range('p5') = k
MsgBox Timer - t
End Sub

　　优化2 这样运算速度特别快

Sub ss3()
Dim i, k
Dim str As String
Dim arr() '数组

t = Timer
arr = Range('g1:j200000')   '是二维的 arr(行，列)
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(1 To 4) '在这里定义数组范围，这个是一维的,是横着的
arr(1) = '张三'
arr(2) = '李四'
arr(3) = '赵五'
Range('b1') = arr(2)  '就是 “李四”
Range('a10:d14') = arr '输出是横着的
End Sub

　　再举个例子：动态数组

Sub test()
Dim arr()
arr = Range('a1:a5') '直接把放到数组里
Range('c1') = arr(2, 1)
End Sub

查找销售冠军：使用数组作为辅助列，作为存储空间，数组可以直接用在函数里

 计算出该组的最高销售额及产品

Sub test()
Dim arr(1 To 4)

For i = 1 To 4
    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)

End Sub

再深入一步：在这了解redim 以及ubound和lbound的作用

Sub test()
Dim arr() 'dim里面只能用常量
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 UBound(arr) '这里是针对arr 有两个方法计算arr的上限和下限，ubound是上限 lbound是下限
'可以结合for循环使用

End Sub

排列组合计算汇款信息：这里用到了goto 方法来退出所有循环

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

通过数组来优化

Sub test()
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('i3') = arr(l, 1)
                    GoTo 100
                End If
            Next
        Next
    Next
Next
100
MsgBox Format(Timer - t, '0.00000')
End Sub