Declare A Multidimensional Array

You can declare a multidimensional array to store related values within one array. VBA allows you to create arrays with up to 60 dimensions, although dealing with arrays that have more than two or three dimensions can become rather confusing.

Multidimensional arrays provide the ability to store related values in one location, such as the test score for each student in the class. The first dimension of the array may contain the student's name, and the second dimension may contain the student's score.

To help you envision a somewhat overwhelming multidimensional array, try thinking of a two-dimensional array as a worksheet, with rows and columns. You access each element of the array by specifying two different index values. For example, MultiArray(2,4) accesses the value whose first dimension index is 2 and whose second dimension is 4.

As you add a third dimension to an array it gains depth. Using the worksheet example, you can add a third dimension to the two-dimensional array to make it resemble a cube. Accessing an element of the array now requires three index values, as in the example:

MultiArray(2,4,2).

As with other variables, you use the Dim statement to declare procedure-level arrays, the Private statement for arrays available to other procedures within the module and finally the Public statement for arrays that are accessible to the entire project.

When you declare a multidimensional array, you need to indicate the size of each dimension in the array. You do not have to make the dimensions of the array, as in the example: Dim MultiArray (1 To 4, 1 To 5, 1 To 3). In this example, the array contains four elements in the first dimension, five in the second, and three in the third.

See Chapter 3 for more information on VBA data types.

DECLARE A MULTIDIMENSIONAL ARRAY

DECLARE A MULTIDIMENSIONAL ARRAY

'-H Create a new subrouti

< Type Dim CellRange As Range.

■ Set the range of cells for the Range object.

'-H Create a new subrouti

B Type Dim ArrayName(1 To N, 1 To M) As DataType, replacing ArrayName with the name of the array, N and M with the upper bounds of each dimension, and DataType with the data type of the array elements.

< Type Dim CellRange As Range.

^Q Type Set CellRange = Range(Cells(1,1), Cells (3,3)) replacing Range(Cells(1,1), Cells (3,3)) with the range of cells.

■ Set the range of cells for the Range object.

Type ArrayName(1,1) = Value replacing ArrayName(1,1) with the array element reference and Value with the value of the first element of the array.

0 0

Post a comment