在 Excel VBA 中预定义多维数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6938614/
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
Pre defining multi dimensional array in Excel VBA
提问by Sunny D'Souza
I know we can define single dimension array in excel VBA using the following
我知道我们可以使用以下方法在 excel VBA 中定义一维数组
GroupCols = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L")
How can you predefine multi- dimensional array in the same manner?
如何以相同的方式预定义多维数组？
Also I want to keep track of certain levels in the following manner
我还想通过以下方式跟踪某些级别
Level[16][0]
Level[16][1]
Level[16][2]
Level[8][0]
Level[8][1]
Level[8][2]
Level[7][0]
Level[7][1]
Level[7][2]
The first index defines the level and so may not be consecutive...like after 16 there is straight 8 and so on. For each i need 3 info which is 0,1,2 second indexes.
第一个索引定义了级别，因此可能不是连续的......就像在 16 之后有连续的 8 等等。对于每个我需要 3 个信息，即 0,1,2 秒索引。
Can anyone guide me on how to achieve the same in excel VBA?
谁能指导我如何在excel VBA中实现相同的目标？
回答by Jean-Fran?ois Corbett
You can't have non-consecutive indices in an array like that. If you do only use a non-consecutive subset of the indices, then all the other elements will be empty but still use up storage space, which is both inefficient and error-prone (LaunchMissile = Levels(17,1), whoops!).
像这样的数组中不能有非连续索引。如果您只使用索引的非连续子集，那么所有其他元素都将是空的，但仍会占用存储空间，这既低效又容易出错（LaunchMissile = Levels(17,1)，哎呀！）。
What you're looking for is the Dictionary object. Before use, must set reference as follows: Tools > References > check Microsoft Scripting Runtime.
您正在寻找的是 Dictionary 对象。使用前，必须设置参考如下：工具>参考>勾选Microsoft Scripting Runtime。
Example:
例子：
Dim Levels As Scripting.Dictionary
Set Levels = New Scripting.Dictionary
' Fill up the dictionary
Levels.Add Key:=16, Item:=Array("A", "B", "C")
Levels.Add Key:=8, Item:=Array("FAI", "CNT", "YES")
Levels.Add Key:=7, Item:=Array("Once", "Twice", "Thrice")
' Retrieve items from the dictionary
Debug.Print Levels.Item(8)(0)
Debug.Print Levels.Item(8)(1)
Debug.Print Levels.Item(8)(2)
Note that a Collection object could also do the trick. Advantage: native to VBA, so no need to set reference. Disadvantage: Key is write-only, which can be quite awkward.
请注意，一个 Collection 对象也可以做到这一点。优点：VBA 原生，无需设置引用。缺点：密钥是只写的，这可能很尴尬。
回答by Hubisan
There is a way to define a 2D array by using evaluate() almost like using array() for 1D:
有一种方法可以通过使用evaluate() 来定义二维数组，就像使用array() 来定义一维数组一样：
Sub Array2DWithEvaluate()
Dim Array2D As Variant
'[] ist a shorthand for evaluate()
'Arrays defined with evaluate start at 1 not 0
Array2D = [{"1,1","1,2","1,3";"2,1","2,2","2,3"}]
Debug.Print Array2D(2, 2) '=> 2,2
End Sub
If you want to use a string to define the array you have to use it like this
如果你想使用一个字符串来定义数组，你必须像这样使用它
Sub Array2DWithEvaluateFromString()
Dim strValues As String
Dim Array2D As Variant
strValues = "{""1,1"",""1,2"",""1,3"";""2,1"",""2,2"",""2,3""}"
Array2D = Evaluate(strValues)
Debug.Print Array2D(2, 2) '=> 2,2
End Sub
If you want more info about other uses of the function Evaluate() check this great post.
如果您想了解有关 Evaluate() 函数其他用途的更多信息，请查看这篇很棒的文章。
回答by Patrick Honorez
回答by aevanko
There is no way to declare and populate a two-dimensional array like when you use the array() function. You can use ReDim to define the array dimensions, then populate it. Here's an example:
无法像使用 array() 函数那样声明和填充二维数组。您可以使用 ReDim 定义数组维度，然后填充它。下面是一个例子：
ReDim myArray(0 To 16, 0 To 3)
myArray(0, 0) = "A"
myArray(0, 1) = "B"
...
Unfortunately, when you create an array, an element will be created for each entry from the lower bound to upper bound. They will be empty, but they are there, so you need to be aware of it, especially if you plan to loop through it.
不幸的是，当您创建数组时，将为从下限到上限的每个条目创建一个元素。它们将是空的，但它们在那里，所以你需要意识到它，特别是如果你打算循环遍历它。
