vba 你可以直接在excel VBA中声明锯齿状数组吗？

Can you declare jagged arrays in excel VBA directly?

提问by JazZeus

In my project I work a lot with jagged arrays, i.e. arrays of which the elements are also arrays.

在我的项目中，我经常使用锯齿状数组，即元素也是数组的数组。

Up until know I only managed to define these arrays like this:

直到知道我只能像这样定义这些数组：

dim subarray(1 to 3) as Integer
dim MyArray(1 to 5) as Variant
subarray(1) = 40
subarray(2) = 50
subarray(3) = 60

MyArray(1) = subarray

But I would like to do something like this:

但我想做这样的事情：

dim MyArray(1 to 5)(1 to 3) as Variant/Integer
MyArray(1)(1) = 40

The example above doesn't compile. Is there a similar, valid way to declare nested arrays directly?

上面的例子不能编译。是否有类似的有效方法可以直接声明嵌套数组？

EDIT: The right term is 'jagged array' not 'nested array'.

编辑：正确的术语是“锯齿状数组”而不是“嵌套数组”。

EDIT2: Edited the example values, to prevent confusion between indices and values.

EDIT2：编辑示例值，以防止索引和值之间的混淆。

回答by Joshua Honig

There are a variety of ways in VBA to have collections of collections. All of them have benefits and drawbacks.

在 VBA 中有多种方式来拥有集合的集合。它们都有优点和缺点。

Multidimensional Arrays

多维数组

Good:

好的：

• Simple syntax (only one variable)
• Type safety. All elements of a matrix of Integerare known and enforced to be Integers.
• Veryfast array access

• 简单的语法（只有一个变量）
• 类型安全。的矩阵的所有元素Integer都是已知的并强制为Integers。
• 非常快的数组访问

Bad:

坏的：

• If there are large differences in the size of the inner arrays, a matrix will waste some space because there are unused "cells" in the matrix.
• You can only change the bounds of the last dimension with ReDim Preserve. So you can't add "columns" to a matrix without clearing all the data.

• 如果内部数组的大小差异很大，矩阵会浪费一些空间，因为矩阵中有未使用的“单元格”。
• 您只能使用 更改最后一个维度的边界ReDim Preserve。所以你不能在不清除所有数据的情况下向矩阵添加“列”。

You declare multidimensional arrays by including multiple bounds separated by commas:

您可以通过包含以逗号分隔的多个边界来声明多维数组：

Dim intMatrix(0 to 2, 0 to 4) As Integer

You can dynamically increase the last dimension of a multidimensional array if you first declare the array without any bounds:

如果首先声明数组没有任何边界，则可以动态增加多维数组的最后一维：

Dim intMatrix() As Integer                ' Uninitialized dynamic array
ReDim intMatrix(0 to 4, 0 to 2)           ' Initialize as a matrix
ReDim Preserve intMatrix(0 to 4, 0 to 3)  ' Add another "row" to the matrix, preserving existing data

Jagged Arrays

锯齿状数组

Good:

好的：

• Flexible

• 灵活的

Bad:

坏的：

• You lose compile-time type safety
• They are a bit tricky / messy because of the nested structure
• It is awkward and expensive to resize the inner arrays

• 你失去了编译时类型安全
• 由于嵌套结构，它们有点棘手/混乱
• 调整内部数组的大小既尴尬又昂贵

You can create jagged arrays be declaring an outer array of type Variant(), and assigning other arrays to the elements of the outer array:

您可以通过声明 type 的外部数组Variant()并将其他数组分配给外部数组的元素来创建锯齿状数组：

Dim outer() As Variant  ' Dynamic, so new inner arrays can be added
Dim inner() As Integer  ' Dynamic, so new elements can be added

ReDim outer(0 to 3)
ReDim inner(0 to 4)
outer(2) = inner

Lost compile-time type information

丢失编译时类型信息

All the compiler "knows" about the outer array is that it can contain anything. So the following code will compile:

编译器“知道”的关于外部数组的所有内容是它可以包含任何内容。所以下面的代码将编译：

Set objWorksheet = outer(2)(3)

Although at runtime this will cause an error because the inner array at outer(2)contains Integers, not Worksheetobjects.

虽然在运行时这会导致错误，因为内部数组outer(2)包含Integers，而不是Worksheet对象。

Awkward to resize

尴尬地调整大小

One of the benefits of jagged array is that the inner arrays can be of different sizes. However, you cannot directly resize an inner array. VBA just can't handle the syntax; the following doesn't compile:

锯齿状数组的好处之一是内部数组可以有不同的大小。但是，您不能直接调整内部数组的大小。VBA 无法处理语法；以下不编译：

ReDim Preserve outer(2)(0 to 5)

In order resize an inner array, you first have to assign the inner array to a separate variable, resize that variable, and then assign it back to the jagged array:

为了调整内部数组的大小，您首先必须将内部数组分配给一个单独的变量，调整该变量的大小，然后将其分配回锯齿状数组：

Dim tempInts() As Integer
tempInts = outer(2)
ReDim Preserve tempInts(0 to 5)
outer(2) = tempInts

The reason you have to reassign tempIntsback to the outerarray is that arrays use by-value semantics in VBA. That means when you assign an array to a variable (as in tempInts = outer(2), you copy the entire array. That can be very expensive if your array is long (say a few thousand elements), and even more expensive if your array contains strings, because every single string must also be copied.

您必须重新分配tempInts回outer数组的原因是数组在 VBA 中使用按值语义。这意味着当您将一个数组分配给一个变量时（如在 中tempInts = outer(2)，您复制整个数组。如果您的数组很长（比如几千个元素），这可能非常昂贵，如果您的数组包含字符串，则成本更高，因为每个还必须复制单个字符串。

Jagged Collections

锯齿状的集合

Good:

好的：

• Simple syntax for adding and removing elements
• Just as flexible as jagged arrays
• Collections use by-reference semantics so assigning is cheap, and you can have multiple references to the same collection object

• 添加和删​​除元素的简单语法
• 就像锯齿状数组一样灵活
• 集合使用按引用语义，因此赋值很便宜，并且您可以对同一个集合对象有多个引用

Bad:

坏的：

• Like jagged arrays, there is no type safety

• 像锯齿状数组一样，没有类型安全

If you will be adding elements to your inner arrays frequently, it will be a lot easier to use Collectionobjects instead of arrays. Collections do not enforce the data type of their elements, so this has the same drawbacks of using Variantarrays -- but you must do that to use jagged arrays anyways.

如果您要经常向内部数组添加元素，则使用Collection对象而不是数组会容易得多。Collections 不强制其元素的数据类型，因此这与使用Variant数组具有相同的缺点——但无论如何您必须这样做才能使用锯齿状数组。

Dim cAnimals As New Collection 

' Let's add stats on the Cheetah
Dim cCheetah As New Collection

' Easy to add inner collections to the outer collection.  Also, cCheetah refers
' to the same collection object as cAnimals(1).  
cAnimals.Add cCheetah          

' Easy to add items to inner collection.
' Working directly with the cCheetah collection:
For Each vMeasurment In GetMeasurements("Cheetah")
    cCheetah.Add vMeasurement
Next

' Working on the same collection by indexing into the outer object
For i = 1 To cAnimals.Count
    For j = 1 To cAnimals(i).Count
        cAnimals(i)(j) = cAnimals(i)(j) * dblNormalizingFactor
    Next
Next

回答by Charles Williams

As Joshua says: there is no specific VBA syntax for declaring jagged arrays directly.
But Jagged arrays follow the normal VBA rules for assignment: eg

正如 Joshua 所说：没有用于直接声明锯齿状数组的特定 VBA 语法。
但是锯齿状数组遵循正常的 VBA 分配规则：例如

Dim a as integer
dim v as variant
a=17
v=a
a=19

You don't expect V to now equal 19!

您不会期望 V 现在等于 19！

回答by Slai

Array of Arrays:

数组数组：

Dim aa(), ax(),  dd,  x(), xx(), x2()  ' all are " As Variant"

' Array of Arrays - Variant(0 To 2) with 3 Variant(0 To 2) ( 3 Variant/Integer each )
aa = Array( Array(1, 2, 3), Array(4, 5, 6), Array(7, 8, 9) )
aa(0)(0) = 0

' Array of "Excel" arrays - Variant(0 To 2) with 3 Variant(1 To 3) (3 Variant/Integer each)
ax = Array([{1,2,3}], [{4,5,6}], [{7,8,9}])
ax(0)(1) = 0

Another option is Collection of Collections, or Dictionary of Dictionaries:

另一种选择是 Collection of Collections 或 Dictionary of Dictionaries：

Set dd = CreateObject("Scripting.Dictionary")
Set dd(2) = CreateObject("Scripting.Dictionary")
dd(2)(4) = 24

---

---

一些“Excel”矩形数组示例（因为不是 VBA 类型并且也适用于 Excel 公式）：

' "row" array starts at 1 - Variant(1 To 3) with 3 Variant/Integer each
x = [{1,2,3}]
x(1) = 0

' "column" array starts at 1, 1 - Variant(1 To 3, 1 To 1)
xx = [{1;2;3}]
xx(1, 1) = 0

' "Excel" rectangular array - Variant(1 To 3, 1 To 3)
x2 = [{1,2,3;4,5,6;7,8,9}]
x2(1, 1) = 0

Stop ' pause to check the types in the Locals window