Multi-Dimensional Arrays

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

Fixed Arrays

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" 

Dynamic Arrays

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


Preserving Values

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.

Array Function

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.

© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext