0

Is there a syntax to use the VBA function Array() to create a multidimensional array?

The documentation does not mention it. Also I did not find any such thing with Google, but searching for "Array" creates too many search results to be useful.

For instance...

Dim MyArr() As Variant
MyArr = Array(("1a","1b"),("2a","2b"))           'This does not work.
MyArr = Array(Array("1a","1b"),Array("2a","2b")) 'This does work, but creates nested arrays.

It seems a multidimensional array can only be filled by explicitly defining it and then assigning the values of the individual elements...

Dim MyArr(9,1) As String
MyArr(0,0) = "Hello"
MyArr(0,1) = "world!"
'etc.

In other systems such as .Net or Java, we have a syntax such as...

Dim MyArr() As String = {{"1a","1b"},{"2a","2b"}}

...and unless I am mistaken, we do not have this in VBA.

spinjector
  • 3,121
  • 3
  • 26
  • 56
  • I believe you're correct. – BigBen May 25 '23 at 17:35
  • @ScottCraner - the linked thread is for Excel only though, right? – BigBen May 25 '23 at 17:49
  • @BigBen correct. The highest voted answer yes. The other answers which show that the Op is correct that in other versions the only way is to put each value in one at a time is for all versions. – Scott Craner May 25 '23 at 17:51
  • What about the next way: `Dim myArr(), arr2D As Variant` `myArr = Array(Array("1a", "1b"), Array("2a", "2b"))` `arr2D = Application.Transpose(myArr)` `Debug.Print arr2D(1, 1), arr2D(1, 2), arr2D(2, 1), arr2D(2, 2)` Just to prove that arr2D is a 2D array with two columns and two rows... It places the jagged arrays elements in columns. Or `Dim arr2 As Variant` `arr2 = Application.Index(myArr, 0, 0)` `Debug.Print arr2(1, 1), arr2(1, 2), arr2(2, 1), arr2(2, 2)` It places the jagged array elements on rows... – FaneDuru May 25 '23 at 18:26

0 Answers0