MS-Access / Getting Started

Multidimensional Arrays

We discussed one-dimensional arrays, but you can have several dimensions to an array. Think of it as being like a spreadsheet. You have rows and columns that give a reference; you can also have several different sheets so that a cell reference is made up of the sheet name plus the cell column and row:

Dim temp(10,4) as String

If this was a spreadsheet, it would have 11 columns and five rows, giving you a total of 55 pigeonholes or cells to place your data into and refer to it.
A three-dimensional array would be as follows:

Dim temp(10,4,3) as String

Imagining this again as a spreadsheet, it would have 11 columns and five rows, but they would span across four worksheets, giving you a total of 220 pigeonholes. If you remember that each one of these elements can take a string up to 65,400 characters, you begin to see how much memory can be used by a simple array and how much data can be stored.

Dimensioning an array immediately allocates memory to it-this is an important consideration when planning your program. Taking up large chunks of memory can cause your program and Windows to run inefficiently. Because Windows itself is a graphical application, it uses large amounts of RAM (random access memory) to hold information. You may find that using a large array slowsWindows down, and other applications run more slowly and take longer to process information. This may be all right on your home computer, but a professional application needs to take this into account.

Further dimensions are possible, but these become difficult to keep track of and manipulate. Five dimensions is considered the safe maximum to use. If you go back to thinking of an array as being like a series of spreadsheets, think how complicated a five-dimensional spreadsheet would be! ReDim can still be used to resize the array, but you cannot use it to change the number of dimensions in the array, nor can you use it to change the type of the array-for example, from string to integer.

[Previous] [Contents] [Next]