vba-数组学习

Sub arrayStudy()
Dim arr(), arr2(), arr3()
'''''''''''''''''''''''''''''''''''''''''''''''
'定义动态数组
'Dim Arr2(), r%
'r = r + 1
'数组重构，保留已有数据
'ReDim Preserve Arr2(1 To r)
'''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''
'给数组赋值
'arr = [a1:e1].Value
'arr2 = Array(1, 2, 3, 4, 5)
'arr2 = Array("1", "2", "3", "4", "5")
'将数组赋给excel单元格
'[a2:e2] = arr
'[a3].Offset(1, 0).Resize(1, 5) = arr2
'''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''
'数组函数的运用1
'arr2 = Array(1, 2, 3, 4, 5)
'最大值,最小值
'Application.WorksheetFunction.Max (arr2)
'Application.WorksheetFunction.Min(arr2)
'返回第2大值
'MsgBox Application.WorksheetFunction.Large(arr2, 2)
'返回第6大值
'MsgBox Application.WorksheetFunction.Large(arr3, 6)
'返回最小值
'MsgBox Application.WorksheetFunction.Small(arr2, 1)
'返回第2小值
'MsgBox Application.WorksheetFunction.Small(arr2, 2)
'''''''''''''''''''''''''''''''''''''''''''''''
'数组函数的运用2
'Fileter函数，过滤出查找的数据，返回数组
'arr = Array("1", "2", "1", "1", "3", "1")
'返回一个由4个“1”组成的数组
'newarray = Filter(arr, "1")
'UBound函数，返回数组的最大下标，下面返回一维数组的最大下标
'[a6].Resize(1, UBound(newarray, 1) + 1) = newarray
'''''''''''''''''''''''''''''''''''''''''''''''
'数组函数的运用3
'取得单元格的格式，绝对引用，相对引用
'arr = [a1].Formula
'arr = [a1].FormulaR1C1
'''''''''''''''''''''''''''''''''''''''''''''''
'数组函数的运用4
'Split(字符串[, 分隔符[, 要返回的子字符串数量[,比较方式]]])
'后三项可选，默认：分隔符为空格，返回全部子字符串
'与Split相反 Join(源数组[,分隔符])
'''''''''''''''''''''''''''''''''''''''''''''''
'矩阵函数
'转置：         TRANSPOSE函数
'矩阵的逆矩阵： MINVERSE函数
'矩阵乘积：     MMULT函数
'''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''
'实例运用：将A列中的数据与C列相比较,输出C列中没有的数据到D列
Dim arr, brr, i&, x&, d As Object
arr = Range("a1:a" & [a65536].End(xlUp).Row)
brr = Range("c1:c" & [c65536].End(xlUp).Row)
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
d(arr(i, 1)) = ""
Next
For x = 1 To UBound(brr)
If d.exists(brr(x, 1)) Then
d.Remove brr(x, 1)
End If
Next
[d1].Resize(d.Count, 1) = Application.Transpose(d.keys)
'''''''''''''''''''''''''''''''''''''''''''''''
End Sub