excel - Fill multidimensional array

Question

I was wondering how to fill a multi-dimensional array in Excel VBA. A 1d array can be filled as follows:

Dim myarray as variant

myarray = Array("sheep", "goat", "chicken")

How would I fill each row separately for a multi-dimensional array?

Answer 1

You can do this:

Dim a
a = [{1,2;3,4;5,6}]

Limitations:

• This only works with arrays of type Variant, because [x] is shorthand for Evaluate("x") which means that x is interpreted via Excel, and Excel only returns Variants. So declaring Dim a As Variant or an array Dim a() As Variant works fine. But not any other type of array e.g. Dim a() As String fails.

• This only works for one specific kind of multi-dimensional array, namely two-dimensional arrays. Not three-, four- etc. dimensional.