''''''''''''直接定義給數(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ù)組，進(jìn)行計(jì)算，再賦值給單元格
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

'數(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

'數(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

'利用數(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ù)組賦值，提高計(jì)算效率
'2.03秒
Sub dataInput()
Dim start As Double
start = Timer
Dim i&
For i = 1 To 30000
    Cells(i, 1) = i
Next
MsgBox "程序運(yùn)行時間為" & 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 "程序運(yùn)行時間為" & 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 "程序運(yùn)行時間為" & Format(Timer - start, "0.00") & "秒"
End Sub