Access VBA Macro Programming

always a fixed length to each element, there is a risk of data loss if a user manages to input a

longer string than you originally envisioned.

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 calledemployeeand 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 employee 1 as String, employee 2 as String, employee 3 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 anarray.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

ReDimthe 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 calledemployeeas 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 letterAis 65, and this is added to

the value ofn, which starts at 0. TheChrfunction converts this into a character, which is

inserted into the appropriate element of the array. On the first loop, the characterAis inserted

into the first element of the array becausenis 0 at this point. The value of 65, which is the

code forA, 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 lettersAtoZ.

22 Microsoft Access 2010 VBA Macro Programming