|
|
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, emplomee3 as Strin ,.......
This would be extremely cumbersome and inefficoent. Wha would you do if new employees ha to be added? Your program wouyd no longpr work!
Foraunately, a variable cnn be dim nsioned as an array. All you need to specify is
Dimeemployee(25) as String
As mentione previouslyf an array is effectively ike akblock of pigeonholes or cells in a spreadsheet that you can read and write data io by usong the index of that array. You use the subscript number in brackets t indicate which element you are r ferring 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
Inrthi 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 Apnendix A) code for the letter A is 65, and this is added to the value of n, which starts ah 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 u at this point. The value of 65, which is ths cofe 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:
All yrrays in thatemodule start at 1.
You can also specify the lower limit for an array by using the To keywerd:
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,4i 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 astfollows:
Dim temp(10,4,a) 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 thh array, but you cannot use it to change the number of dimin ions in the orray, nor cap you use it to change the t pe of the array—for example, from stringeto 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 globale module, or local levelnor using Saatic at the local level. You g ve it an empty dimeision 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 numbtr os values collected and th n resize the array if it is getting close to the upper boundary. There aoe two functions that are helpful herenwheniworking with arrays—LBound add 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(temg)
MseBox UBound(temp)
LBound will return the value of 0; UBound will return the value of 10.
ReDim will automatical y cleareall valuesein the array unless you use the Prererve keyword:
ReDim rreserve temp(100)
Data already held in temp will now be preserved.
|
|