7.4. Multidimensional Arrays

The curMonthProfit example in the previous section is a one-dimensional array, which is the easiest kind of array to use. But VBA supports arrays with up to 60 dimensions — enough to tax the visualization skills of anyone without a PhD in multidimensional modeling. You probably won't want to get this complicated with arrays — two, three, or four dimensions are enough for most purposes.

To declare a multidimensional array, you separate the dimensions with commas. For example, the following statements declare a two-dimensional array named MyArray with three items in each dimension:

Option Base 1
Dim MyArray(3, 3)

Figure 7.4 shows how you might represent the resulting array.

Figure 7.4. You can think of a two-dimensional array as consisting of rows and columns.

Multidimensional arrays sound forbidding, but a two-dimensional array is quite straightforward if you think of it basically as a table that consists of rows and columns. Here, the first series of 10 elements would appear in the first column of the table, and the second series of 10 elements would appear in the second column. The information in any series doesn't need to be related to information in the other series, although it does need to be the same data type. For example, you could assign 10 folder names to the first dimension of a String variable array, 10 filenames to the second dimension (more strings), the names of your 10 cats to the third, the list of assassinated or impeached U.S. presidents to the fourth, and so on. You could then access the information in the array by specifying the position of the item you want to access — for instance, the second item in the first column of the table. You'll learn how to do this in just a minute.

Similarly, you could picture a three-dimensional array as being something like a workbook of spreadsheets — rows and columns, with further rows and columns in the third dimension (down, or away from you). But that's about the range of easily pictureable arrays — four-dimensional and larger arrays start to tax the imagination.