Pre defining multi dimensional array in Excel VBA

I know we can define sing开发者_如何转开发le 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?

There is a way to define a 2D array by using evaluate() almost like using array() for 1D:

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.

http://www.ozgrid.com/forum/showthread.php?t=52372

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.

You could use Array(Array()) for e.g.

data = Array(Array(1,2), Array(3,4))

To refer to the first element, use data(0)(0)

(copied from here)

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:

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.