How to initialize a multidimensional array variable in vba for excel

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

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))

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

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.

Here is a version which allows you to initialise a two dimensional static array. You can use this function with any variable type, not just integer, just change the type of parameter 'oparray'. Note that the index and size calculations are based on using arrays which are declared base 0 but do not use that index.

retcode = build_array(str_array, "0", "0", "0", "0", "F_LEVEL", "F_PATH", "0", "F_SORTTL", "F_PATH", "0", "F_TYPE", "F_PATH")

retcode = build_array(int_array, 4, 12, 13, 12, 2, 12, 5, 6, 7)


Public Function build_array(oparray() As Integer, ParamArray ipdata() As Variant) As Integer
    
Dim idx As Integer                                                    ' General purpose loop counter
Dim items_ip As Integer                                               ' Number of data items in parameter array
Dim items_op As Integer                                               ' Number of data items in output array
Dim retcode As Integer                                                ' Result code returned to calling routine
Dim ysize As Integer                                                  ' Number of columns in input array

retcode = 1                                                           ' Assume there is too much data in parameter array
items_ip = UBound(ipdata) - LBound(ipdata) + 1                        ' Get number of data items in input  array
ysize = UBound(oparray, 2)                                            ' Get number of columns in output array
items_op = UBound(oparray, 1) * ysize                                 ' Get number of data items in output array
If (items_op < items_ip) Then GoTo x                                  ' ERROR EXIT - output array is too small
For idx = LBound(ipdata) To UBound(ipdata)                            ' Step through all items in parameter array
    oparray(1 + (idx \ ysize), 1 + (idx Mod ysize)) = ipdata(idx)     ' Copy data items into output array
Next
retcode = 0                                                           ' Indicate array was built successfully
x:
build_array = retcode                                                 ' Return result code to calling routine
Exit Function

Public Function ArrDiscrete(ix, jx As Range) As Variant
Dim v0, v1, v2, vv, v As Variant

v0 = Array(1, 2)
v1 = Array(3, 4)
v2 = Array(5, 6)

'Create an Array of Arrays or Jagged Array
vv = Array(v0, v1, v2)

'Converts a Jagged Array (Array of Array) into a Matrix (Array 2D)
ReDim v(UBound(v0), UBound(vv))
For i = 0 To UBound(v0)                 'rows - 1
    For j = 0 To UBound(vv)             'cols - 1
        v(i, j) = vv(j)(i)              'cols,rows
    Next j
Next i

ArrDiscrete = v(ix, jx)
End Function

Try the above defined function in a spreadsheet.

Where:

• "ix" is for rows, it starts from 0
• "jx" if for columns, it starts from 0

Here's another solution in case one wants to pass a 2-d table to static arrays in vba for whatever reason, e.g. when building a function...

So even if you dim a 2d arrays like

Dim 2dArr(10,10) 

you can't populate a 'row' of this array like this which would make sense (following line will give an error for not proper dimensions):

2dArr(0) = Array(1,2,3,...)

...and if your table is not small to use

2dArr = [{1,1},{1,2},...]  

...and not too big either to have to revert to use of a sheet to store the information, which would then present an issue if you are trying to build a custom function which you want to work on any spreadsheet just my adding the modules to an add-on, then the following might be considered an alternative...

So

• predim to required size the 2darray (e.g. arrID(12,40))

• pass the values of each row to temporary arrays as you build the table (e.g. arrTemp configured for row 0, then for row 1, etc.

• in between pass the temporary array to the proper row with a for loop. Frankly, I have not found any more elegant solution that would allow me to build not too big but then again not to small static tables inside the code to use with custom functions

    Function fID_in(OD_in, SCHED)
  
    Dim arrSCHED As Variant
    Dim arrOD As Variant
    Dim arrID(12, 40) As Variant
    Dim arrTemp As Variant
    Dim i, SCHEDpointer
  
    arrSCHED = Array("STD", "XS", "XXS", "SCH10", "SCH30", "SCH40", "SCH60", 
    "SCH80", "SCH100", "SCH120", "SCH140", "SCH160")
  
    arrOD = Array(0.125, 0.25, 0.5, 0.75, 1, 1.5, 2, 2.5, 3, 4, 6, 8, 10, 12, _ 
        14, 16, 18, 20, 24, 26, 28, 30, 32, 34, 36, 38, 40, 42, 44, 46, 48, _
        52, 56, 60, 64, 68, 72, 76, 80, 81)
   '0: STD
       arrTemp = Array(0.269, 0.364, 0.622, 0.824, 1.049, 1.61, 2.067, 2.469, _ 
       3.068, 4.026, 6.065, 7.981, 10.02, 12, 13.25, 15.25, 17.25, 19.25, _
       23.25, 25.25, 27.25, 29.25, 31.25, 33.25, 35.25, 37.25, 39.25, 41.25, _
       43.25, 45.25, 47.25, 51.25, 55.25, 59.25, 63.25, 67.25, 71.25, 75.25, _ 
       79.25, "Diam>80!")
    For i = LBound(arrTemp) To UBound(arrTemp): arrID(0, i) = arrTemp(i): Next i
  
   '1: XS
       arrTemp = Array(0.215, 0.302, 0.546, 0.742, 0.957, 1.5, 1.939, 2.323, _ 
       2.9, 3.826, 5.761, 7.625, 9.75, 11.75, 13, 15, 17, 19, 23, 25, 27, _
       29, 31, 33, 35, 37, 39, 41, 43, 45, 47, 51, 55, 59, 63, 67, 71, 75, _
       79, "Diam>80!")
    For i = LBound(arrTemp) To UBound(arrTemp): arrID(1, i) = arrTemp(i): Next i
  
   '2: etc... (e.g. in my case it goes on to define 12 rows)
  

To make it more clear later on in the code the static table is used as follows:

fID_in = "N/A"
SCHEDpointer = -1

For i = LBound(arrSCHED) To UBound(arrSCHED)
    If SCHED = arrSCHED(i) Then
        SCHEDpointer = i
        Exit For
    End If
Next i

If SCHEDpointer < 0 Then
    fID_in = "Invalid Schedule"
Else
    For i = LBound(arrOD) To UBound(arrOD)
        If OD_in = arrOD(i) Then fID_in = arrID(SCHEDpointer, i)
    Next i
End If

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