Arrays

Up to now I have discussed individual variables . You can set up a variable and give it a value such as a number or a string. A simple example is the name of an employee. You can set up a variable called employee and give it a string with the employee's name. However, what about other employees? Suppose you are writing a program that needs to refer to 26 employees . You would have a great deal of difficulty referring to them in your program using simple variables. You would have to do something like this:

 Dim employee1 as String, employee2 as String, employee3 as String,....... 

This would be extremely cumbersome and inefficient. What would you do if new employees had to be added? Your program would no longer work!

Fortunately, a variable can be dimensioned as an array. All you need to specify is

 Dim employee(25) as String 

As mentioned previously, an array is effectively like a block of pigeonholes or cells in a spreadsheet that you can read and write data to by using the index of that array. You use the subscript number in brackets to indicate which element you are referring to. You can also ReDim the array at runtime to enlarge it if your program requires.

This example sets up a 26-element array numbered from 0 to 25, with 26 strings to put your names into. Each element can be referenced from your code by using the index number. A For..Next loop can easily be used to list out all names within the array:

 Dim employee(25) as String 
 For n = 0 To 25 
 employee(n) = Chr(n+65) 
 Next n 
 For n = 0 To 25 
 MsgBox employee(n) 
 Next n 

In this macro, you first dimension an array called employee as a string with 26 elements.

The first For..Next loop puts data into the array. The ASCII (American Standard Code for Information Interchange ‚ see Appendix A) code for the letter A is 65, and this is added to the value of n , which starts at 0. The Chr function converts this into a character, which is inserted into the appropriate element of the array. On the first loop, the character A is inserted into the first element of the array because n is 0 at this point. The value of 65, which is the code for A , is added to it.

The second For..Next loop displays each element of the employee array in turn . When you run this example, it will give the letters A to Z .

Arrays follow the same rules as ordinary variables. They can be local, module, or global and can be any data type including Variant. The size of the array in terms of elements is limited to an integer (in the range ‚ 32,768 to 32,767). The default lower boundary is always 0, but this can be altered by placing an Option Base statement in the declarations section of the module:

 Option Base 1 

All arrays in that module start at 1.

You can also specify the lower limit for an array by using the To keyword:

 Dim temp (1 To 15) as String 

Multidimensional Arrays

I have only discussed one-dimensional arrays, but you can have several dimensions to an array. Think of it 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 were a spreadsheet, it would have 11 columns and 5 rows, 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 5 rows, but they would span across 4 worksheets, giving a total of 220 pigeonholes. Bear in mind that each one of these elements can take a string up to 65,500 characters , and you begin to see how much memory can be used up 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 slows Windows right 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 complicated to manipulate and keep track of. Five dimensions is considered the safe maximum to use. If you go back to thinking of an array 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.

Dynamic Arrays

Sometimes you do not know how large an array needs to be. A good example is if you are recursively storing pathnames from a disk device in each array element. You do not know how many subdirectories there are and how long a branch will extend. You could set your array to 1,000 elements, taking up valuable memory in the process, and then find that you only needed 500 elements. Alternatively, there could be 2,000 subdirectory pathnames, so you would run out of room.

You create a dynamic array in exactly the same way as a normal array ‚ using the Dim statement at the global, module, or local level or using Static at the local level. You give it an empty dimension list:

 Dim temp() 

You then use the ReDim statement within your procedure to resize the number of elements within the array. The ReDim statement can only be used within a procedure, and you cannot change the number of dimensions:

 ReDim temp(100) 

You could write code to check the number of values collected and then resize the array if it is getting close to the upper boundary. There are two functions that are helpful here when working with arrays ‚ LBound and UBound . These are functions that can be used to return the upper and lower limits of the dimensions of an array by specifying the array number as a subscript:

 Dim temp(10) 
 
 MsgBox LBound(temp) 
 
 MsgBox UBound(temp) 

LBound will return the value of 0; UBound will return the value of 10.

ReDim will automatically clear all values in the array unless you use the Preserve keyword:

 ReDim Preserve temp(100) 

Data already held in temp will now be preserved.