.本文轉(zhuǎn)載自公眾號：大海魚數(shù)據(jù)分析，原創(chuàng)作者：waves，本文著作權(quán)歸原創(chuàng)作者所有，本人收藏此文僅作為學(xué)習(xí)之用，不作其他目的，如有侵權(quán)，請聯(lián)系我刪除。VBA數(shù)組在Excel開發(fā)應(yīng)用中，作用還是很明顯的，用好數(shù)組可以提高工作效率，下面就開始揭開VBA數(shù)組的神秘面紗。下面是幾種數(shù)組常用的定義方法，一維數(shù)組的定義、二維數(shù)組的定義直接賦值定義、調(diào)用Array函數(shù)定義、調(diào)用Excel工作表內(nèi)存數(shù)組''''''''''''直接定義給數(shù)組賦值
'一維常量數(shù)組的定義
Sub arrDemo1()
Dim arr(2) As Variant '數(shù)組
arr(0) = 'vba'
arr(1) = 100
arr(2) = 3.14
MsgBox arr(0)
End Sub
'二維常量數(shù)組的定義
Sub arrDemo2()
Dim arr(1, 1) As Variant 'Dim arr(0 To 1, 0 To 1) As Variant
arr(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)
Next
Next
End Sub
''''''''''''用array函數(shù)創(chuàng)建常量數(shù)組
'一維數(shù)組
Sub arrayDemo3()
Dim arr As Variant '數(shù)組
arr = Array('vba', 100, 3.14)
MsgBox arr(0)
End Sub
'二維數(shù)組
Sub arrayDemo4()
Dim arr As Variant '數(shù)組
arr = Array(Array('張三', 100), Array('李四', 76), Array('王五', 80))
MsgBox arr(1)(1)
End Sub
'調(diào)用Excel工作表內(nèi)存數(shù)組
' 一維數(shù)組[{'A',1,'C'}]
'二維數(shù)組[{'a',10;'b',20;'c',30}]
Sub mylook()
Dim arr
arr = [{'a',10;'b',20;'c',30}]
Range('a1:b3') = arr
MsgBox Application.WorksheetFunction.VLookup('b', arr, 2, 0) '調(diào)用vlookup時可以作為第二個參數(shù)
End Sub
'動態(tài)數(shù)組的定義方法
Sub arrDemo5()
Dim arr1() '聲明一個動態(tài)數(shù)組（動態(tài)指不固定大小）
Dim arr2 '聲明一個Variant類型的變量
arr1 = Range('a1:b2') '把單元格區(qū)域A1：B2的值裝入數(shù)組arr1
arr2 = Range('a1:b2') '把單元格區(qū)域A1：B2的值裝入數(shù)組arr2
MsgBox arr1(1, 1) '讀取arr數(shù)組中第1行第1列的數(shù)值
MsgBox arr2(2, 2) '讀取arr1數(shù)組的第2行第2列的數(shù)值
End Sub
'讀取單元格數(shù)據(jù)到數(shù)組，進行計算，再賦值給單元格
Sub arr_calculate()
Dim arr '聲明一個變量用來盛放單元格數(shù)據(jù)
Dim i%
arr = Range('a2:d5') '把單元格數(shù)據(jù)搬入到arr里，它有4列4行
For i = 1 To 4 '通過循環(huán)在arr數(shù)組中循環(huán)
arr(i, 4) = arr(i, 3) * arr(i, 2) '數(shù)組的第4列(金額)=第3列*第2例
Next i
Range('a2:d5') = arr '把數(shù)組放回到單元格中
End Sub
3、數(shù)組的合并（join）與拆分（split）'數(shù)組合并(join)與拆分(Split)
Sub join_demo()
Dim a As Variant
Dim b As Variant
' Join using spaces
a = Array('Red', 'Blue', 'Yellow')
b = Join(a, '')
MsgBox ('The value of b is :' & b) 'Red Bule Yellow
' Join using $
b = Join(a, '$') 'Red$Bule$Yellow
MsgBox ('The Join result after using delimiter is : ' & b)
End Sub
Sub split_demo()
Dim a As Variant
Dim b As Variant
a = Split('Red$Blue$Yellow', '$') 'a = Array('red'，'blue'，'yellow')
b = UBound(a)
For i = 0 To b
MsgBox a(i)
Next
End Sub
'vba數(shù)組的篩選
Sub arr_filter()
arr = Array('ABC', 'F', 'D', 'CA', 'ER')
arr1 = VBA.Filter(arr, 'A', True) '篩選所有含A的數(shù)值組成一個新數(shù)組
arr2 = VBA.Filter(arr, 'A', False) '篩選所有不含A的數(shù)值組成一個新數(shù)組
MsgBox Join(arr1, ',') '查看篩選的結(jié)果
End Sub
5、數(shù)組維度的轉(zhuǎn)換（Transpose）'數(shù)組維數(shù)的轉(zhuǎn)換
'一維轉(zhuǎn)二維
Sub arr_tranpose1()
arr = Array(10, 'vba', 2, 'b', 3)
arr1 = Application.Transpose(arr)
MsgBox arr1(2, 1) '轉(zhuǎn)換后的數(shù)組是1列多行的二維數(shù)組
End Sub
'二維數(shù)組轉(zhuǎn)一維 '注意:在轉(zhuǎn)置時只有1列N行的數(shù)組才能直接轉(zhuǎn)置成一維數(shù)組
Sub arr_tranpose2()
arr2 = Range('A1:B5')
arr3 = Application.Transpose(Application.Index(arr2, , 2)) '取得arr2第2列數(shù)據(jù)并轉(zhuǎn)置成1維數(shù)組
MsgBox arr3(4)
End Sub
'把單元格中的內(nèi)容用“-”連接起來
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
6、利用數(shù)組獲取所有工作表名稱的自定義函數(shù)'利用數(shù)組獲取所有工作表名稱的自定義函數(shù)
Function getSheetsname(id)
Dim i%, arr()
k = Sheets.Count
ReDim arr(1 To k)
For i = 1 To k
arr(i) = Sheets(i).Name
Next
getSheetsname = Application.Index(arr, id)
End Function
'數(shù)組賦值，提高計算效率
'2.03秒
Sub dataInput()
Dim start As Double
start = Timer
Dim i&
For i = 1 To 30000
Cells(i, 1) = i
Next
MsgBox '程序運行時間為' & Format(Timer - start, '0.00') & '秒'
End Sub
'0.12秒
Sub dataInputArr()
Dim start As Double
start = Timer
Dim i&, arr(1 To 30000) As String
For i = 1 To 30000
arr(i) = i
Next
Range('a1:a30000').Value = Application.Transpose(arr)
MsgBox '程序運行時間為' & Format(Timer - start, '0.00') & '秒'
End Sub
'0.09秒
Sub dataInputArr2()
Dim start As Double
start = Timer
Dim i&, arr(1 To 30000, 1 To 1) As String
For i = 1 To 30000
arr(i, 1) = i
Next
Range('a1:a30000').Value = arr
MsgBox '程序運行時間為' & Format(Timer - start, '0.00') & '秒'
End Sub
VBA數(shù)組還是很強大的，通過對單元格區(qū)域數(shù)據(jù)的讀取，賦值給數(shù)組，再利用數(shù)組函數(shù)或者調(diào)用Excel內(nèi)置函數(shù)進行相關(guān)處理。另外，數(shù)組在賦值計算效率上面也是非常高的，大家可以自行嘗試下。
