這裡介紹 Excel VBA 的陣列基本用法,還有陣列配合迴圈一同使用的技巧與實際範例。
前面我們所介紹的 VBA 變數都是儲存單一值變數(例如一個整數或一個字串等),而如果我們需要儲存多個值的時候(像是十個整數或十個字串等),就需要使用陣列(array)。
一維陣列可以想像成是多個相同類型的純量變數串在一起,陣列的宣告方式跟純量變數類似:
Dim MyArray(4) As Integer
陣列在宣告時會在變數名稱後方加上一對小括號,括號內的整數是指定陣列的結尾索引值,VBA 的陣列索引預設是從 0
開始的,也就是說這樣宣告的 MyArray
就是一個長度為 5 的整數陣列,五個元素的索引分別為 0
、1
、2
、3
、4
。
若要指定陣列中所儲存的值,可用小括號加上索引值的方式來設定:
' 把 MyArray 的第 3 個值(索引為 2)設定為 10 MyArray(2) = 10
陣列元素的讀取方式也是用小括號加上索引:
' 輸出 MyArray 的第 3 個值 MsgBox MyArray(2)
簡單來說,陣列加上小括號與索引值之後,就變成一般的純量變數,使用方式跟一般的變數相同。
若要判斷一個變數是否是陣列,可以使用 IsArray
函數:
MsgBox IsArray(MyArray)
其他各種變數類型的陣列語法也都相同,這是一個浮點數(Double
)陣列的範例:
Dim MyDblArr(4) As Double MyDblArr(3) = 1.234 MsgBox MyDblArr(3)
這是字串(String
)陣列的範例:
Dim MyStrArr(4) As String MyStrArr(3) = "Hello, World." MsgBox MyStrArr(3)
這是布林(Boolean
)陣列的範例:
Dim MyBoolArr(4) As Boolean MyBoolArr(3) = True MsgBox MyBoolArr(3)
我們也可以把陣列宣告為萬用類型(Variant
),用來存放各種不同類型的變數:
Dim MyVarArr(4) As Variant MyVarArr(0) = 12.34 MyVarArr(1) = True MyVarArr(2) = "Hello, World." MsgBox MyVarArr(0) MsgBox MyVarArr(1) MsgBox MyVarArr(2)
萬用類型的陣列在宣告時,也可以簡寫為這樣:
Dim MyVarArr(4)
沒有指定陣列類型的話,預設就是 Variant
。
Array
函數Array
函數可以讓設計者輸入一連串的資料,直接建立一個陣列,這種寫法對於初始化一些包含常數的靜態陣列非常有用:
Dim arr As Variant arr = Array(1, 2, 3) MsgBox (arr(1))
如果想要改變陣列索引的起始值,可以自行指定索引範圍的開始與結束值:
' 索引從 1 開始的陣列 Dim MyArray2(1 To 5) As Integer
這裡宣告一個 MyArray2
陣列,其索引值為 1
、2
、3
、4
、5
。
多維度的陣列在 VBA 的程式中也是很常見的,尤其是在處理表格或是矩陣的資料時,就會用到二維陣列。多維度的陣列用法與一維陣列大同小異,只是維度增加而已,語法都類似,以下是一個二維矩陣的範例:
' 宣告一個 3 x 4 的矩陣 Dim mat(2, 3) As Integer ' 指定矩陣內特定元素的值 mat(0, 0) = 2 mat(2, 3) = 5 ' 取出矩陣內特定元素的值 MsgBox (mat(2, 3))
多維度的陣列操作方式與一維陣列非常類似,只是增加索引的個數而已,三維以上的陣列以此類推。
若要自訂多微陣列的索引範圍,語法也是類似:
' 自訂多微陣列的索引範圍 Dim mat(1 To 3, 1 To 4) As Integer mat(1, 1) = 2 mat(3, 4) = 5 MsgBox (mat(3, 4))
一般的陣列在宣告時就必須決定好陣列的大小,但是有的時候我們無法事先預知程式所需要的陣列大小,要等到實際執行時才會知道需要多少空間,這時候就可以使用動態陣列的方式來儲存資料。
動態陣列(dynamic arrays)的特點就是可以動態改變陣列的大小,在空間不足時可以擴增,而空間太大時也可以縮減,以下是動態陣列的使用方式:
' 宣告動態陣列 Dim MyDynArr() As Integer ' 調整陣列大小 ReDim MyDynArr(3) MsgBox "LBound = " & LBound(MyDynArr) _ & ", Ubound = " & UBound(MyDynArr) MyDynArr(3) = 123
動態陣列的宣告方式就是在宣告陣列時不要指定索引範圍,然後在要使用陣列之前執行 ReDim
設定陣列的大小。
隨後如果需要改變動態陣列的大小,也是同樣呼叫 ReDim
並指定新的陣列大小,而在預設的狀況下使用 ReDim
改病陣列大小時,原本儲存於陣列中的資料會被刪除,如果想要保留舊資料,就要加上 Preserve
,如果舊資料不需要保留的話,就可以將 Preserve
省略,這樣執行速度會比較快。
' 調整陣列大小,保留陣列內部資料 ReDim Preserve MyDynArr(10) MsgBox "LBound = " & LBound(MyDynArr) _ & ", Ubound = " & UBound(MyDynArr) MyDynArr(7) = 456 MsgBox MyDynArr(3) MsgBox MyDynArr(7)
這裡的 LBound
與 UBound
是用來查詢陣列索引下限與上限的函數。
當動態陣列使用完畢之後,我們可以使用 Erase
將系統配置給動態陣列的記憶體收回:
Erase MyDynArr
Erase
若用於一般性的陣列,則會將陣列內的每個元素重新初始化。
陣列的應用範圍很廣泛,許多的資料在處理時,都會運用到陣列,以下是一些跟陣列有關的應用範例。
這是一個將 Excel 工作表中特定範圍的資料,一次全部讀取至 VBA 二維陣列中處理的範例。
Dim i As Integer Dim myTable As Variant ' 將 Excel 工作表中特定範圍的資料讀取至 VBA 二維陣列中 myTable = Sheets("工作表1").Range("A1:B5").Value ' 使用迴圈處理二維陣列資料 For i = 2 To 5 MsgBox (myTable(i, 1) & " = " & myTable(i, 2)) Next i
善用 VBA 的二維陣列操作技巧,就可以很容易的將 Excel 表格整個抓進 VBA 處理,等所有的資料處理完後再輸出至 Excel 表格中,這種方式適合用於較大量的資料自動化處理,對於複雜的資料處理來說,這樣會比起直接在 Excel 資料表上操作要來的有效率。
在 VBA 中若要將一串文字分割成好多段,可以使用 Split
函數,而其傳回的結果就是一個一維的陣列,以下是一個簡單的範例:
Dim arr As Variant Dim ub As Integer ' 使用 Split 以空白字元分割字串 arr = Split("This is a test", " ") ' 取得陣列長度 ub = UBound(arr) ' 輸出陣列內容 For i = 0 To ub MsgBox (i & " = " & arr(i)) Next i
這裡我們也可以使用 For Each
的方式來處理陣列,這樣就可以省去取得陣列長度的步驟:
' 使用 For Each 輸出陣列內容 For Each s In arr MsgBox (s) Next s
關於 VBA 迴圈的教學請參考 VBA 迴圈控制。
若要將陣列的所有元素串接成一個字串,可以使用 Join
函數,以下是一個接續的範例:
Dim str As String ' 使用 Join 將陣列內容串接為一個字串 str = Join(arr, " ") MsgBox (str)
Filter
函數可以篩選陣列的每個元素,將符合條件的元素拿出來,組成新的陣列並傳回。以下這個例子會將 arr
陣列中含有字母 B
的元素挑出來:
Dim arr, flt As Variant ' 建立測試用陣列 arr = Array("ABC", "BCD", "CDE") ' 篩選出含有 "B" 的元素 flt = Filter(arr, "B") ' 輸出結果 For Each x In flt MsgBox ("result: " & x) Next x
若要在陣列中尋找特定的元素,並取的該元素的位置,可以使用 Application.Match
函數來處理:
Dim arr, pos As Variant ' 建立測試用陣列 arr = Array("ABC", "BCD", "CDE") ' 尋找 "BCD" 的位置 pos = Application.Match("BCD", arr, 0) ' 輸出結果 If Not IsError(pos) Then MsgBox "Position: " & pos Else MsgBox "Not found!" End If
更多關於 VBA 的教學文章,請參考 VBA 程式設計。
參考資料:Excel Easy、tutorialspoint、
home & learn、ExcelFunctions.net