Excel VBA 程式設計教學:陣列(Array)

這裡介紹 Excel VBA 的陣列基本用法,還有陣列配合迴圈一同使用的技巧與實際範例。

前面我們所介紹的 VBA 變數都是儲存單一值變數(例如一個整數或一個字串等),而如果我們需要儲存多個值的時候(像是十個整數或十個字串等),就需要使用陣列(array)。

一維陣列

一維陣列可以想像成是多個相同類型的純量變數串在一起,陣列的宣告方式跟純量變數類似:

Dim MyArray(4) As Integer

陣列在宣告時會在變數名稱後方加上一對小括號,括號內的整數是指定陣列的結尾索引值,VBA 的陣列索引預設是從 0 開始的,也就是說這樣宣告的 MyArray 就是一個長度為 5 的整數陣列,五個元素的索引分別為 01234

若要指定陣列中所儲存的值,可用小括號加上索引值的方式來設定:

' 把 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 陣列,其索引值為 12345

多維陣列

多維度的陣列在 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)

這裡的 LBoundUBound 是用來查詢陣列索引下限與上限的函數。

當動態陣列使用完畢之後,我們可以使用 Erase 將系統配置給動態陣列的記憶體收回:

Erase MyDynArr

Erase 若用於一般性的陣列,則會將陣列內的每個元素重新初始化。

應用範例

陣列的應用範圍很廣泛,許多的資料在處理時,都會運用到陣列,以下是一些跟陣列有關的應用範例。

VBA 讀取 Excel 工作表

這是一個將 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

Excel VBA 陣列應用範例

善用 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 Easytutorialspoint
home & learnExcelFunctions.net

Windows, 程式設計

6 Comments

  1. wate

    在分割字串那邊有提到可以改成for each,請問要怎麼改比較好,我自己嘗試了一下都錯誤,新手學VBA希望大大能指點一下。

    • G. T. Wang

      我已將 For Each 的範例加入文章中。

  2. Ed

    請問一下, 以您最後那個 Filter 的範例, 如果我想要在陣列裡尋找”BCD”, 並得知其位於此陣列裡第幾個位置, 需要用什麼指令呢? 謝謝~

  3. YAN

    您好,請問Filter的第一個參數,不能使用您再「VBA 讀取 Excel 工作表」所寫的myTable這個表格嗎?
    myTable = Sheets(“工作表1”).Range(“A1:B5”).Value

    我想將EXCEL的表格去做篩選的話,用Filter該如何使用呢~

  4. William

Leave a Reply