Pre defining multi dimensional array in Excel VBA

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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)