锯齿状阵列（阵列阵列）
锯齿状阵列不是多维数组
数组数组（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)。