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?

You can fill a 2-dimensional array quickly by using a dynamic array and entering the data into a range on a worksheet. Then you can simply: array = Range("Range"). For more than 2 dimensions you have to read each value in individually. — Mr. Mascaro
– Mr. Mascaro, Commented Nov 7, 2014 at 18:44
That's a 1-D array you have there - did you really mean multidimensional? — Tim Williams
– Tim Williams, Commented Nov 7, 2014 at 18:50

Jean-François Corbett · Accepted Answer · 2014-11-07 20:29:22Z

12

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.

edited Nov 7, 2014 at 20:29

Jean-François Corbett

38.7k30 gold badges145 silver badges192 bronze badges

answered Nov 7, 2014 at 18:55

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

4 Comments

Mathieu Guindon Over a year ago

Really?? That's utterly cool! How on Earth haven't I ever seen this syntax before?!

Mr. Mascaro Over a year ago

It should be noted that this only works as shown. A variable that is declared as an array will not be able to be loaded this way. You'll get a compile error: "Can't assign to array."

Mr. Mascaro Over a year ago

@Jean-FrançoisCorbett, I just tried it exactly that way and it fails with a compile error.

Jean-François Corbett Over a year ago

Dim a() As Variant: a = [{1,2;3,4;5,6}] compiles and executes just fine at my end? Office 2010

Collectives™ on Stack Overflow

Fill multidimensional array

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related