VBA Programming Series...28

Multidimensional arrays

The arrays created in the previous examples are all one-dimensional arrays. Think of one-dimensional arrays as a single line of values. Arrays you create in VBA can have as many as 60 dimensions — although you rarely need more than two or three dimensions in an array. The following example declares an 81-integer array with two dimensions:

Dim MyArray (1 To 9, 1 To 9) As Integer

You can think of this array as occupying a 9 x 9 matrix — perfect for storing all numbers in a soduku puzzle.

To refer to a specific element in this array, you need to specify two index numbers (similar to it’s “row” and it’s “column” in the matrix). The following example shows how you can assign a value to an element in this array:

MyArray (3, 4)= 125

This statement assigns a value to a single element in the array. If you’re think-ing of the array in terms of a 9 x 9 matrix, this assigns 125 to the element located in the third row and fourth column of the matrix.

Here’s how to declare a three-dimensional array, with 1,000 elements:

Dim My3DArray (1 To 10, 1 To 10, 1 To 10) As Integer

You can think of a three-dimensional array as a cube. Visualizing an array of more than three dimensions is more difficult. Sorry, I haven’t yet mastered the fourth dimension and beyond.