Is there a way to create a 2-dimensional array in VBA using Array function>

Question

I want to quickly create a 6X4 array of small integers. For a small 1-dimensional array, I like to use something like

Dim MyArr()
MyArr = Array(1,2,3,4,5,6)

Is there a similar way to create a 2-D array? I tried separating the rows with ; but

MyArr = Array (1, 2, 3; 4, 5, 6) is invalid.

I guess I'm being too lazy to set up the loops, but a one-line method would be compact and useful.

If you're using Excel, then [this](https://stackoverflow.com/a/5252452/9245853). — BigBen, Apr 09 '21 at 01:57
@TimWilliams ha! I always forget about using `Application.Evaluate` like this - that's actually answer-worthy, and exactly the compact form OP seems to be looking for. — Mathieu Guindon, Apr 09 '21 at 03:02
I don't want to repeat the relatively unknown use of **`Application.Index`** to transform a *jagged array* via **double zero** arguments here, as I've answered it already at [Initialize a (2-)dim array in VBA](https://stackoverflow.com/questions/24584088/how-to-initialize-a-multidimensional-array-variable-in-vba-for-excel/66969472#66969472); further readings [Some pecularities of `Application.Index()`](https://stackoverflow.com/questions/51688593/excel-vba-insert-new-first-column-in-datafield-array-without-loops-or-api-call/51714153#51714153) — T.M., Apr 09 '21 at 09:58
I wasn't aware of Application.Evaluate, or it's short cut, enclosing the expression in square brackets. Thanks, Tim for the answer, and Mathieu for pointing out that it's a way of using Evaluate. — Freond, Jul 27 '21 at 02:17

Tim Williams · Accepted Answer · 2021-04-09T03:54:52.797

5

In Excel you can use Application.Evaluate (or the [] shortcut) and an array literal:

MyArr = [{1,2;3,4}]

edited Apr 09 '21 at 03:54

answered Apr 09 '21 at 03:23

Tim Williams

154,628
8
97
125

Mathieu Guindon · Answer 2 · 2021-04-09T02:29:30.973

The VBA._HiddenModule.Array function has this signature:

Public Function Array(ParamArray ArgList() As Variant) As Variant

I'd guess it's basically just returning the ParamArray pointer, effectively turning an inline sequence of elements into a simple one-dimensional Variant array.

If that sequence of elements consists in elements that are themselves arrays, then you can have yourself a jagged array, or "array of arrays" - but not a 2D array:

Dim JaggedArray As Variant
JaggedArray = Array( _
    Array(1, 2, 3, 4), _
    Array(1, 2, 3, 4), _
    Array(1, 2, 3, 4), _
    Array(1, 2, 3, 4), _
    Array(1, 2, 3, 4), _
    Array(1, 2, 3, 4), _
)

You access its elements with successive subscripts:

Debug.Print JaggedArray(2)(3)

The biggest issue with that, is that you've turned data into code, and what's normally a good thing is to separate the data from the code.

If only there was a worksheet nearby to read the data from!

Dim twoDimensionalVariantArray As Variant
twoDimensionalVariantArray = Sheet1.Range("A1:D6").Value

Or better yet, use a named range:

twoDimensionalVariantArray = Sheet1.Range("ItemsSource").Value

Or an actual table:

twoDimensionalVariantArray = Sheet1.ListObjects("Table1").DataBodyRange.Value

All one-liners, no loop needed!

Given a range representing an area of two or more cells, Range.Value returns a 2D Variant array. Careful though, an array that came from a worksheet will always be 1-based so that indexes line up with row and column numbers (there no row or column 0 on a worksheet). Always use LBound(theArray, dimension) and UBound(theArray, dimension) to get the lower and upper boundaries of an array before you iterate it with a For...Next loop.

You access its elements with 2D subscripts:

Debug.Print twoDimensionalVariantArray(2, 3)

fyi Might be interested in my extension to the proposed jagged array solution via an undocumented feature of `Application.Index()` as well as another way by **sequencing** via MS 365. @MathieuGuindon — T.M., Apr 10 '21 at 18:39

score 2 · Answer 3 · answered Apr 10 '21 at 18:35

A) Quick alternative via Excel/MS 365

To quickly create a 2-dim array (with well defined steps) you could profit from the new SEQUENCE() function in Excel for MS 365:

    Dim MyArr As Variant
    MyArr = Application.Sequence(2, 3, 1, 1)

Result

    MyArr(1,1) = 1
    MyArr(1,2) = 2
    MyArr(1,3) = 3
    MyArr(2,1) = 4
    MyArr(2,2) = 5
    MyArr(2,3) = 6

Syntax

=SEQUENCE(rows,[columns],[start],[step])

Note: Any missing optional arguments will default to 1.

So it would suffice to pass only arguments (2, 3) to the Sequence function in this example.

B) Addendum regarding Application.Index()

As already commented you can profit also from an undocumented use of Application.Index to

transform a ►jagged array via double zero arguments.

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

See Initialize a (2-)dim array in VBA
A further SO post lists Some unknown pecularities of Application.Index()

Is there a way to create a 2-dimensional array in VBA using Array function>

3 Answers3