You can declare arrays with as many dimensions as you need, even in excess of 20, although you will probably not have more than 3 or 4 maximum.
Multi Dimensional arrays can have up to 60 dimensions.
It is very unusual to use more than 2 or 3 dimensions.
When using 2-dimensional arrays always have columns and the rows, (ie (1,1) (1,2) (1,3) etc).
In a multi-dimensional array can you have vArrayName(1, 2, 1 ) When is it 2 dimensional ???
A multi dimensional fixed array is declared using the Dim statement.
Dim aCellValues(1 To 10, 1 To 100) As String
This is a two dimensional array whose first index ranges from 1 to 10 and whose second index ranges from 1 to 100.
aCellValues(2,3) = "some text"
A multi dimensional dynamic array must be declared on two lines
Dim aNumbers() As Integer
ReDim aNumbers(1 to 10, 1 to 100)
You can use the ReDim statement to change both the number of dimensions and the size of each dimension.
Only the upper bound of the last dimension in a multi-dimensional array can be changed.
A run-time error will occur if you try to change any other dimension.
Dim sCellValues(1 To 50, 1 To 10) As String
ReDim aCellValues(1 To 50, 1 To 20)
ReDim aCellValues(1 To 50, 1 To 20) As String
If you use the Preserve keyword, then you cannot change the number of dimensions.
If you use the Preserve keyword, then you are only able to resize the last dimension.
For more details refer to the ARRAY Function page.
You can also define and initialise multi-dimensional arrays
Dim vArray As Variant
vArray = Array( Array("one"), Array("Two"), Array("three"), Array("four") )
A big disadvantage to using a multi dimensional array is that all the dimensions must contain the same data type.
User Defined Types can combine multiple data types into a single data type.
You can use a user defined type instead when you need to store values with different data types.