How to initialize a multidimensional array variable in vba for excel

arrays, excel, vba

Solution

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

Problem

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?

Original source

Related problems