9

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?

Improve this question
0

4 Answers 4

Reset to default
10

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

9

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.

Improve this answer

Comments

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)

Improve this answer

6 Comments

Why would one do this instead of just a two-dimensional array? This seems overly convoluted, but there may be a reason...
In some cases, it allows you to avoid a nearly empty matrix.
How so? How would the array of arrays data(i)(j) not contain the same total number of elements as the equivalent two-dimensional array data2D(i,j)?
If you need only 7, 8 and 16 in the first dimension, then data(2) will be empty.
Gotcha. Thanks. This is a good solution... but you're still storing a Variant Empty in all the empty spots, i.e. in this particular case still storing 1 element instead of 3. Not super efficient for sparse data sets. Anyway, +1
|
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.