在Excel VBA中预先定义多维数组 [英] Pre defining multi dimensional array in Excel VBA

问题描述

我知道我们可以使用以下

  GroupCols = Array（A， B，C，D，E，F，G，H，I，J，K，L）
 < c $ c> 

如何以同样的方式预定义多维数组？

另外我想按照以下方式跟踪某些级别

 级别[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.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.

这篇关于在Excel VBA中预先定义多维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！