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

Rectangular Arrays - These are really 2 dimensional. The number of rows is identical to the number of columns.
Jagged Arrays - These are not really 2 dimensional and represent a 1 dimensional array of 1 dimensional arrays. Each element in the main array can hold an array of varying length.

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

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.

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