CODEWITHSUNDEEP
excelvba
Regenbui
Regenbui

Reputation: 181

Fill multidimensional array

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?

Upvotes: 6

Views: 3255

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

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.

Upvotes: 12

Related Questions