Arrays
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 gives 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