VBA数组用法案例详解

''''''''''''直接定义给数组赋值'一维常量数组的定义Sub arrDemo1()Dim arr(2) As Variant   '数组arr(0) = "vba"arr(1) = 100arr(2) = 3.14MsgBox arr(0)End Sub '二维常量数组的定义Sub arrDemo2()Dim arr(1, 1) As Variant  'Dim arr(0 To 1, 0 To 1) As Variantarr(0, 0) = "apple"arr(0, 1) = "banana"arr(1, 0) = "pear"arr(1, 1) = "grape"For i = 0 To 1    For j = 0 To 1        MsgBox arr(i, j)    NextNextEnd Sub ''''''''''''用array函数创建常量数组'一维数组Sub arrayDemo3()Dim arr As Variant   '数组arr = Array("vba", 100, 3.14)MsgBox arr(0)End Sub '二维数组Sub arrayDemo4()Dim arr As Variant   '数组arr = Array(Array("张三", 100), Array("李四", 76), Array("王五", 80))MsgBox arr(1)(1)End Sub '调用Excel工作表内存数组' 一维数组[{"A",1,"C"}]'二维数组[{"a",10;"b",20;"c",30}]Sub mylook()Dim arrarr = [{"a",10;"b",20;"c",30}]Range("a1:b3") = arrMsgBox Application.WorksheetFunction.VLookup("b", arr, 2, 0)  '调用vlookup时可以作为第二个参数End Sub '动态数组的定义方法Sub arrDemo5()Dim arr1() '声明一个动态数组（动态指不固定大小）Dim arr2  '声明一个Variant类型的变量 arr1 = Range("a1:b2")   '把单元格区域A1：B2的值装入数组arr1arr2 = Range("a1:b2")   '把单元格区域A1：B2的值装入数组arr2 MsgBox arr1(1, 1)  '读取arr数组中第1行第1列的数值MsgBox arr2(2, 2) '读取arr1数组的第2行第2列的数值End Sub

'读取单元格数据到数组，进行计算，再赋值给单元格Sub arr_calculate()Dim arr     '声明一个变量用来盛放单元格数据Dim i%arr = Range("a2:d5")     '把单元格数据搬入到arr里，它有4列4行For i = 1 To 4     '通过循环在arr数组中循环    arr(i, 4) = arr(i, 3) * arr(i, 2)      '数组的第4列(金额)=第3列*第2例Next iRange("a2:d5") = arr     '把数组放回到单元格中End Sub

'数组合并(join)与拆分(Split)Sub join_demo()Dim a As VariantDim b As Variant  ' Join using spacesa = Array("Red", "Blue", "Yellow")b = Join(a, "")MsgBox ("The value of b is :" & b) 'Red Bule Yellow ' Join using $b = Join(a, "$")                   'Red$Bule$YellowMsgBox ("The Join result after using delimiter is : " & b)End Sub Sub split_demo()Dim a As VariantDim b As Variant a = Split("Red$Blue$Yellow", "$")     'a = Array("red"，"blue"，"yellow") b = UBound(a) For i = 0 To b    MsgBox a(i) NextEnd Sub

1234567

'vba数组的筛选Sub arr_filter()arr = Array("ABC", "F", "D", "CA", "ER")arr1 = VBA.Filter(arr, "A", True) '筛选所有含A的数值组成一个新数组arr2 = VBA.Filter(arr, "A", False) '筛选所有不含A的数值组成一个新数组MsgBox Join(arr1, ",") '查看筛选的结果End Sub

'数组维数的转换 '一维转二维Sub arr_tranpose1()arr = Array(10, "vba", 2, "b", 3)arr1 = Application.Transpose(arr)MsgBox arr1(2, 1) '转换后的数组是1列多行的二维数组End Sub '二维数组转一维 '注意:在转置时只有1列N行的数组才能直接转置成一维数组Sub arr_tranpose2()arr2 = Range("A1:B5")arr3 = Application.Transpose(Application.Index(arr2, , 2)) '取得arr2第2列数据并转置成1维数组MsgBox arr3(4)End Sub '把单元格中的内容用“-”连接起来Sub join_transpose_demo()arr = Range("A1:C1")arr1 = Range("A1:A5")MsgBox Join(Application.Transpose(Application.Transpose(arr)), "-")MsgBox Join(Application.Transpose(arr1), "-")End Sub

'利用数组获取所有工作表名称的自定义函数Function getSheetsname(id)Dim i%, arr()k = Sheets.CountReDim arr(1 To k)For i = 1 To k    arr(i) = Sheets(i).NameNextgetSheetsname = Application.Index(arr, id)End Function

'数组赋值，提高计算效率'2.03秒Sub dataInput()Dim start As Doublestart = TimerDim i&For i = 1 To 30000    Cells(i, 1) = iNextMsgBox "程序运行时间为" & Format(Timer - start, "0.00") & "秒"End Sub '0.12秒Sub dataInputArr()Dim start As Doublestart = TimerDim i&, arr(1 To 30000) As StringFor i = 1 To 30000    arr(i) = iNextRange("a1:a30000").Value = Application.Transpose(arr)MsgBox "程序运行时间为" & Format(Timer - start, "0.00") & "秒"End Sub '0.09秒Sub dataInputArr2()Dim start As Doublestart = TimerDim i&, arr(1 To 30000, 1 To 1) As StringFor i = 1 To 30000    arr(i, 1) = iNextRange("a1:a30000").Value = arrMsgBox "程序运行时间为" & Format(Timer - start, "0.00") & "秒"End Sub