如何在 VBA 中设置“锯齿状数组”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9435608/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I set up a "jagged array" in VBA?
提问by phan
I have a classroom full of kids, each of whom have to list their favorite toys for an assignment. Some kids only list 1 toy whereas others list more.
我的教室里挤满了孩子，每个人都必须列出他们最喜欢的玩具以进行作业。有些孩子只列出 1 个玩具，而其他孩子列出更多。
How do I create a jagged array such that Kids(x)(y)...where x is the number of kids in my class, and y is the list of toys that they list as their favorites?
我如何创建一个锯齿状的数组，这样 Kids(x)(y)... 其中 x 是我班上孩子的数量，y 是他们列为他们最喜欢的玩具列表？
回答by Jean-Fran?ois Corbett
"Jagged array" is slang for array of arrays. VBA'sVariantdata type can contain just about anything*, including an array. So you make an array of type Variant, and assign to each of its elements an array of arbitrary length (i.e. not all of them have to have equal length).
“锯齿状数组”是数组数组的俚语。VBA 的Variant数据类型几乎可以包含任何内容*，包括数组。因此，您创建一个 type 数组Variant，并为其每个元素分配一个任意长度的数组（即并非所有元素都必须具有相同的长度）。
Here's an example:
下面是一个例子：
Dim nStudents As Long
Dim iStudent As Long
Dim toys() As Variant
Dim nToys As Long
Dim thisStudentsToys() As Variant
nStudents = 5 ' or whatever
ReDim toys(1 To nStudents) ' this will be your jagged array
For iStudent = 1 To nStudents
'give a random number of toys to this student (e.g. up to 10)
nToys = Int((10 * Rnd) + 1)
ReDim thisStudentsToys(1 To nToys)
'code goes here to fill thisStudentsToys()
'with their actual toys
toys(iStudent) = thisStudentsToys
Next iStudent
' toys array is now jagged.
' To get student #3's toy #7:
MsgBox toys(3)(7)
'will throw an error if student #3 has less than 7 toys
* A notable exception is user-defined types. Variants cannot contain these.
* 一个值得注意的例外是用户定义的类型。变体不能包含这些。
回答by ja72
You can use a collection of collections
您可以使用集合的集合
Public Sub Test()
Dim list As New Collection
Dim i As Integer, j As Integer
Dim item As Collection
For i = 1 To 10
Set item = New Collection
For j = 1 To i
item.Add "Kid" & CStr(i) & "Toy" & CStr(j)
Next j
list.Add item
Next i
Debug.Print "Kid 4, Toy 2 = " & list(4)(2)
End Sub
Which outputs Kid 4, Toy 2 = Kid4Toy2
哪些输出 Kid 4, Toy 2 = Kid4Toy2
回答by Steve Rindsberg
You could also concatenate the list of toys into eg a pipe-separated string, then use Split to turn the string into an array when needed:
您还可以将玩具列表连接到例如管道分隔的字符串中，然后在需要时使用 Split 将字符串转换为数组：
Sub UntangleTheString()
Dim sToys As String
Dim aToys() As String
Dim x As Long
sToys = "baseball|doll|yoyo"
aToys = Split(sToys, "|")
For x = LBound(aToys) To UBound(aToys)
Debug.Print aToys(x)
Next
End Sub
回答by Excel Developers
Jean-Francois pointed out that each element can be an array of varying length. I would add that each element can also be of other types and need not be arrays. For example:
让-弗朗索瓦指出，每个元素都可以是一个长度可变的数组。我要补充一点，每个元素也可以是其他类型，不需要是数组。例如：
Dim c as New Collection
Dim a(1 to 5) as Variant
c.Add "a","a"
c.Add "b","b"
a(1) = 5
a(2) = Array(2,3,4)
set a(3) = c
a(4) = "abcd"
a(5) = Range("A1:A4").Value
The various child elements can then be referenced depending on the implicit type of each:
然后可以根据每个子元素的隐式类型引用各种子元素：
a(2)(1) = 3
a(2)(1) = 3
a(3)(1) = "a"
a(3)(1) = "a"
a(5)(2,1) = whatever is in cell A2.
a(5)(2,1) = 单元格 A2 中的任何内容。
回答by John Parker
You don't necessarily need a jagged array to handle your scenario as a 2D array (r, c) will also work. One row for each child and one column for each present. The array dimensions will be (# of children, MAX # of presents) and it will just mean that some of the slots will be empty or 0 (depending on your data type). But at least this way you won't need to redim the array each time you add a present for a child.
您不一定需要锯齿状数组来处理您的场景，因为二维数组 (r, c) 也可以使用。每个孩子一行，每个礼物一列。数组维度将是 (# of children, MAX # of present)，这意味着某些插槽将为空或 0（取决于您的数据类型）。但至少这样你就不需要在每次为孩子添加礼物时重新调整数组。
