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?

Answer 1

Alternative via Application.Index()

Extending on Dmitriv Pavliv's use of a jagged array (and as alternative to Robin Mackenzie's short hand approach), you can go a step further by applying Application.Index() on this array of arrays (with identical number of elements each) - note the double zero arguments!:

Sub Get2DimArray()
    Dim arr() As Variant
    'a) build array of arrays (aka as jagged array)
    arr = Array(Array(1, 2, 4), Array(4, 5, 6), Array(7, 8, 9))
    'b) make it 2-dimensional
    arr = Application.Index(arr, 0, 0)
End Sub

Results in a 2-dim arr(1 To 3, 1 To 3), where

   * Row 1 ~> 1|2|4
   * Row 2 ~> 4|5|6
   * Row 3 ~> 7|8|9

Related link

Further reading regarding Some pecularities of the Application.Index() function

Answer 2

You can also use a shorthand format leveraging the Evaluate function and a static array. In the code below, varData is set where [] is the shorthand for the Evaluate function and the {...} expression indicates a static array. Each row is delimited with a ; and each field delimited with a ,. It gets you to the same end result as simoco's code, but with a syntax closer to your original question:

Sub ArrayShorthand()

    Dim varData As Variant
    Dim intCounter1 As Integer
    Dim intCounter2 As Integer

    ' set the array
    varData = [{1, 2, 3; 4, 5, 6; 7, 8, 9}]

    ' test
    For intCounter1 = 1 To UBound(varData, 1)
        For intCounter2 = 1 To UBound(varData, 2)
            Debug.Print varData(intCounter1, intCounter2)
        Next intCounter2
    Next intCounter1

End Sub