在Excel VBA中预先定义多维数组

本文介绍了在Excel VBA中预先定义多维数组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我知道我们可以使用以下 GroupCols = Array（A， B，C，D，E，F，G，H，I，J，K，L） 如何以同样的方式预定义多维数组？ 另外我想按照以下方式跟踪某些级别 级别[16] [0] 级别[ 16] [1] 级别[16] [2] 级别[8] [0] 级别[8] [1] 级别[8] [2] 级别[7] [0] 级别[7] [1] 级别[7] [2] 第一个索引定义级别，因此可能不是连续的...像16之后有直8等等。对于每个我需要3个信息是0,1,2秒索引。 任何人都可以指导我如何在excel VBA中实现相同？解决方案您不能在数组中具有不连续的索引。如果您仅使用索引的非连续子集，则所有其他元素将为空，但仍然占用存储空间，这无效率和容易出错（ LaunchMissile = Levels（17， 1），whoops！）。 您要查找的是Dictionary对象。在使用之前，必须设置参考如下：工具>参考>检查Microsoft脚本运行时。 示例： Dim Levels As Scripting.Dictionary 设置级别= New Scripting.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） '从字典中检索项 Debug.Print Levels.Item（8）（0） Debug.Print Levels.Item（8 ）（1） Debug.Print Levels.Item（8）（2） 注意一个Collection对象也可以做到这一点。优势：本土化到VBA，所以无需设置参考。缺点：关键是只写，这可能很尴尬。 I know we can define single dimension array in excel VBA using the following 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.Can anyone guide me on how to achieve the same in excel VBA? 解决方案 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!). What you're looking for is the Dictionary object. Before use, must set reference as follows: Tools > References > check Microsoft Scripting Runtime. Example:Dim Levels As Scripting.DictionarySet Levels = New Scripting.Dictionary' Fill up the dictionaryLevels.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 dictionaryDebug.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. 这篇关于在Excel VBA中预先定义多维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！