How to initialize a multidimensional array variable in vba for excel

Question

The Microsoft site suggests the following code should work:

Dim numbers = {{1, 2}, {3, 4}, {5, 6}}

However I get a complile error when I try to use it in an excel VBA module. The following does work for a 1D array:

A = Array(1, 2, 3, 4, 5)

However I have not managed to find a way of doing the same for a 2D array. Any ideas?

simoco · Answer 1 · 2014-07-05 07:29:22Z

up vote 6 down vote

The Microsoft site suggests...

This suggestion for VB.NET but not VBA.

For VBA you wehe in the right direction. You can do:

Dim A as Variant
A = Array(Array(1, 2), Array(3, 4), Array(5, 6))

answered Jul 5 at 7:29

simoco
23.4k81835

many thanks I can then access members of the array using X=A(1)(2) – user3807215 Jul 5 at 12:46

add a comment |

user2972169 · Answer 2 · 2014-07-05 13:58:01Z

So here you generate the array without anything on it, just by telling its dimensions. Dimension is X+1 because 0 counts as a position in the array.

Dim MyArray(X, X) As Integer

Then you fill it by doing for exemple

MyArray (0,0) = 1
MyArray (0,1) = 2
MyArray (1,0) = 3
MyArray (1,1) = 4

...

And so on.

If you want a more convenient way of filling it you can use For Cycles if there is a inherent logic to the numbers you are filling it with.

2 Answers 2