鋸齒狀陣列（陣列陣列）
鋸齒狀陣列不是多維陣列
陣列陣列（Jagged Arrays）與多維陣列不同，如果你在視覺上考慮它們多維陣列看起來像矩陣（矩形），在它們的維度上有定義數量的元素（在陣列內部），而 Jagged 陣列就像每年一樣內部陣列具有不同數量的元素的日曆，例如不同月份的天數。
雖然 Jagged Arrays 由於它們的巢狀級別而使用起來非常麻煩且難以使用，並且沒有太多的型別安全性，但它們非常靈活，允許你非常輕鬆地操作不同型別的資料，並且不需要包含未使用的或空元素。
建立鋸齒狀陣列
在下面的例子中，我們將初始化一個鋸齒狀陣列，其中包含兩個陣列，一個用於 Names，另一個用於 Numbers，然後訪問每個陣列。
Dim OuterArray() As Variant
Dim Names() As Variant
Dim Numbers() As Variant
'arrays are declared variant so we can access attribute any data type to its elements
Names = Array("Person1", "Person2", "Person3")
Numbers = Array("001", "002", "003")
OuterArray = Array(Names, Numbers)
'Directly giving OuterArray an array containing both Names and Numbers arrays inside
Debug.Print OuterArray(0)(1)
Debug.Print OuterArray(1)(1)
'accessing elements inside the jagged by giving the coordenades of the element
動態建立和讀取鋸齒狀陣列
我們可以在構建陣列時更加動態，假設我們在 excel 中有一個客戶資料表，我們想構建一個陣列來輸出客戶詳細資訊。
Name - Phone - Email - Customer Number
Person1 - 153486231 - 1@STACK - 001
Person2 - 153486242 - 2@STACK - 002
Person3 - 153486253 - 3@STACK - 003
Person4 - 153486264 - 4@STACK - 004
Person5 - 153486275 - 5@STACK - 005
我們將動態構造一個 Header 陣列和一個 Customers 陣列，Header 將包含列標題，Customers 陣列將包含每個 customer / row 的資訊作為陣列。
Dim Headers As Variant
' headers array with the top section of the customer data sheet
For c = 1 To 4
If IsEmpty(Headers) Then
ReDim Headers(0)
Headers(0) = Cells(1, c).Value
Else
ReDim Preserve Headers(0 To UBound(Headers) + 1)
Headers(UBound(Headers)) = Cells(1, c).Value
End If
Next
Dim Customers As Variant
'Customers array will contain arrays of customer values
Dim Customer_Values As Variant
'Customer_Values will be an array of the customer in its elements (Name-Phone-Email-CustNum)
For r = 2 To 6
'iterate through the customers/rows
For c = 1 To 4
'iterate through the values/columns
'build array containing customer values
If IsEmpty(Customer_Values) Then
ReDim Customer_Values(0)
Customer_Values(0) = Cells(r, c).Value
ElseIf Customer_Values(0) = "" Then
Customer_Values(0) = Cells(r, c).Value
Else
ReDim Preserve Customer_Values(0 To UBound(Customer_Values) + 1)
Customer_Values(UBound(Customer_Values)) = Cells(r, c).Value
End If
Next
'add customer_values array to Customers Array
If IsEmpty(Customers) Then
ReDim Customers(0)
Customers(0) = Customer_Values
Else
ReDim Preserve Customers(0 To UBound(Customers) + 1)
Customers(UBound(Customers)) = Customer_Values
End If
'reset Custumer_Values to rebuild a new array if needed
ReDim Customer_Values(0)
Next
Dim Main_Array(0 To 1) As Variant
'main array will contain both the Headers and Customers
Main_Array(0) = Headers
Main_Array(1) = Customers
To better understand the way to Dynamically construct a one dimensional array please check Dynamic Arrays (Array Resizing and Dynamic Handling) on the Arrays documentation.
上面程式碼片段的結果是一個帶有兩個陣列的 Jagged 陣列，其中一個陣列有 4 個元素，2 個縮排級別，另一個是另一個 Jagged 陣列，其中包含 5 個陣列，每個陣列包含 4 個元素和 3 個縮排級別，請參見下面的結構：
Main_Array(0) - Headers - Array("Name","Phone","Email","Customer Number")
(1) - Customers(0) - Array("Person1",153486231,"1@STACK",001)
Customers(1) - Array("Person2",153486242,"2@STACK",002)
...
Customers(4) - Array("Person5",153486275,"5@STACK",005)
要訪問你必須記住的資訊，你需要記住你建立的 Jagged 陣列的結構，在上面的示例中，你可以看到 Main Array 包含一個 Headers 陣列和一個陣列 Array（Customers），因此具有不同的訪問方式要素。
現在我們將閱讀 Main Array 的資訊並列印出每個客戶資訊為 Info Type: Info。
For n = 0 To UBound(Main_Array(1))
'n to iterate from fisrt to last array in Main_Array(1)
For j = 0 To UBound(Main_Array(1)(n))
'j will iterate from first to last element in each array of Main_Array(1)
Debug.Print Main_Array(0)(j) & ": " & Main_Array(1)(n)(j)
'print Main_Array(0)(j) which is the header and Main_Array(0)(n)(j) which is the element in the customer array
'we can call the header with j as the header array has the same structure as the customer array
Next
Next
記住要跟蹤你的 Jagged 陣列的結構，在上面的例子中訪問客戶名稱是通過訪問 Main_Array -> Customers -> CustomerNumber -> Name 這是三個級別，要返回 Person4 你需要在 Main_Array 中的 Customers 位置，然後是 Location 客戶 Jagged 陣列中的客戶四，最後是你需要的元素的位置，在這種情況下 Main_Array(1)(3)(0) 即 Main_Array(Customers)(CustomerNumber)(Name)。