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?

note that the microsoft site's code is initializing a nested array instead of a multi-dim array — im_chc, Jan 04 '18 at 02:45
The link on the Microsoft site refers to "Visual Basic' rather than 'Visual Basic for Applications' — LeasMaps, Feb 03 '18 at 07:10
Whenever I'm searching up the documentation for VBA, the first thing I check is if the URL says `office/vba` or `dotnet/visual-basic`. Office VBA is what you're using in Excel. dotnet visual basic is refering to [VB.NET](https://en.wikipedia.org/wiki/Visual_Basic_.NET), the language that was made for the .Net framework, a "successor" to [Visual Basic Script](https://en.wikipedia.org/wiki/VBScript). — Toddleson, Dec 02 '21 at 16:05

score 34 · Answer 1 · answered Jun 02 '16 at 06:17

34

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

answered Jun 02 '16 at 06:17

Robin Mackenzie

18,801
7
38
56

1

This is a better answer than the accepted because it creates a true two-dimensional array that is accessed using standard `varData(row, col)` notation. – ChaimG Jun 30 '16 at 02:21
Yes, this is best. It is worth pointing out that the square brackets are syntactic sugar for Application.Evaluate (at least I believe so). – S Meaden Apr 17 '18 at 22:47
@Robin very cool, I searched high and low for this syntax, thanks! – ashleedawg May 23 '18 at 12:44
@SMeaden Not sure what your point is -- The answer states (repeatedly) that the square brackets are emulating `Evaluate`. – ashleedawg May 23 '18 at 12:46
@ashleedawg : haha, thanks for your feedback. my comment arose because of something I read in an old COM textbook that states that square brackets emulating `Evaluate` was a COM convention driven of dispid -5 and in fact I explored this later http://exceldevelopmentplatform.blogspot.com/2018/05/vba-use-square-brackets-trick-to.html i.e. it is not unique to Excel. I admit that without the details the earlier comment above does not add much value there. Thanks. – S Meaden May 23 '18 at 15:31
@ashleedawg : Sorry, there were two blog posts,start with this one http://exceldevelopmentplatform.blogspot.com/2018/05/vba-com-dispid-5-gives-vba-square.html – S Meaden May 23 '18 at 15:40
@Robin Mackenzie I try to apply your code in MS Access. In the line `varData = [{1, 2, 3; 4, 5, 6; 7, 8, 9}]` I get the error `Run-time error '2465': Microsoft Access cannot find field 1 ", specified in the expression.`. Could you tell me what I'm doing wrong? How to fix the error? – eusataf May 14 '19 at 18:10
@eusataf maybe this technique only works for Excel. I never tried it in Access. – Robin Mackenzie May 15 '19 at 01:16
3

@Robin `[...]` is a shortcut to the `Evaluate` method, which is a part of Excel , and not Access – chris neilsen May 29 '19 at 21:26
1

the drawback of this syntax is that it doesn't work (to me) spread in 3 separated lines using the " _". The solution with "array(...)" can do this instead – 6diegodiego9 Sep 21 '20 at 11:45
@RobinMackenzie fyi though an old post, you might be interested in my alternative approach to your array short cut method via a frequently unknown use of `Application.Index()` – T.M. Apr 06 '21 at 16:49

score 13 · Answer 2 · edited Jun 14 '18 at 16:44

13

The Microsoft site suggests...

This suggestion is for VB.NET but not VBA.

For VBA you were in the right direction. You can do this:

Dim A as Variant
A = Array(Array(1, 2), Array(3, 4), Array(5, 6))

edited Jun 14 '18 at 16:44

JakeK

76
2
18

answered Jul 05 '14 at 07:29

Dmitry Pavliv

35,333
13
79
80

many thanks I can then access members of the array using X=A(1)(2) – user3807215 Jul 05 '14 at 12:46
In this case, how to get the different sizes ? – Enissay Jul 22 '16 at 22:01
7

Note that is creates a [jagged array](https://stackoverflow.com/q/9435608/11683) (a 1D array of arrays), not a 2D array. – GSerg Apr 25 '19 at 16:42

T.M. · Answer 3 · 2021-04-06T16:51:40.050

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

score 1 · Answer 4 · answered Jul 05 '14 at 13:58

So here you generate the array without anything on it, just by telling its dimensions. Dimension is X+1 because 0 counts as a position in the array.

Dim MyArray(X, X) As Integer

Then you fill it by doing for exemple

MyArray (0,0) = 1
MyArray (0,1) = 2
MyArray (1,0) = 3
MyArray (1,1) = 4

...

And so on.

If you want a more convenient way of filling it you can use For Cycles if there is a inherent logic to the numbers you are filling it with.

score 0 · Answer 5 · answered Apr 06 '21 at 08:14

In case the size is unknown until run time.

Dim nRows As Integer, nCols As Integer
...
Dim yourArray() As Integer
ReDim yourArray(1 to nRows, 1 to nCols) 'One base initialisation
'ReDim yourArray(0 to nRows - 1, 0 to nCols - 1) 'Zero base initialisation

Then you can initialise (or access) the grid as:

yourArray(1, 1) = ... 'set first cell

How to initialize a multidimensional array variable in vba for excel

5 Answers5

Linked

Related