Simplify Your Code Using Array Variables

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

ACCESS 97, 2000

Simplify Your Code Using Array Variables

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.by Susan Sales Harkins

Dancing is probably the last thing that pops into your head when you think of programming, but chances are you feel like you've got two left feet every time you try to work with arrays. However, like dance moves, arrays are easy to work with if you learn the right steps and practice. Once you've got the routine down, you can take advantage of an array's flexible behavior--without stepping on anyone's toes.

In this article, we'll explain what an array is, when to use one, and how. We'll walk through the techniques for creating an array, adding data to it, and retrieving information from it. We'll also show you how to size arrays, both for when you know how much data you need to store at the outset, and for when you need a more flexible solution.

What's an array?

Don't let the technical mumbo jumbo scare you. A VBAarray is simply a fixed collection of variable elements that share the same data type and name. The trick is that VBA treats this group as a single variable. In fact, the only real difference between an array and a normal variable is that the array has an index. The index values allow VBA to identify the individual elements stored in the array. For instance, a simple array might contain three variables and their respective index values would be 0, 1, and 2.

When should you use an array?

You might be wondering just why you would want to use an array. Anytime you find yourself looping through the same code to grab different variables, you should consider an array to store those variables. You'll probably reduce the amount of code you need, and most likely the end results will be more efficient than a non-array solution. However, keep in mind that an array should simplify a process, not complicate it. Be aware that arrays can become outrageously complex. If the array seems to take on a life of its own, you might want to rethink your strategy before things get too nasty.

Create a sample array

Let's consider a simple example. Suppose you run a small dance studio, and you want to know the total number of students currently attending your beginner classes. We'll use a single array variable to store separate values for each class. Then, we'll sum all the elements in the array to get the total number of students.

We'll be working with the table and data shown in Figure A.

Figure A: We'll use an array variable to work with the data for these classes.

After you set up the table, click on the Modules tab in the Database window (in Access 2000, click Modules in the Object bar), and then click the New button. We'll create several functions to demonstrate how arrays work.

Declaring an array variable

The first step in working with an array is to declare it. The purpose of a declaration is to identify a variable's data type. The same process is necessary for setting up an array--you need to identify the data type for each of the array's elements. When declaring an array, you'll often want to identify the lower and upper bounds for the array. These bounds define the numbers that comprise the array's index. However, you should be aware that you can wait until later to identify these values--you don't have to do so in the declaration statement. We'll keep things simple for the moment, and work with arrays where we define the lower and upper bounds at the outset.

For our example, we're assuming you have four beginning dance classes. The declaration we'll use to create the appropriate array is

  
Dim arrCount(1 To 4) As Long

The array arrCount will contain four elements with the index values 1 through 4. The first element's index value will be 1, the second element's will be 2, and so on. In addition, each element in the array will be a long integer. The Long data type might seem like overkill when the Byte data type would do. When you know a value will not exceed a Byte's limits, you should use it, but we're assuming you might have more than 255 students in a class.

Using the array

Let's create a procedure that demonstrates how an array works. Enter the code shown in Listing A in the Module window. If you're using Access 2000, note that we used DAO to create our recordset, so you'll need to reference the appropriate library. To do so, select Tools/References, select the Microsoft DAO 3.6 Object Library check box, and click OK. Access 2000 users will also need to preface any Recordset references used in this article's code with DAO. When you've finished, click the Go/Continue button (Run Sub/UserForm in Access 2000). Access returns the total number of beginner students, 32, in a simple message box.

Tip: You can also test the function by pressing [Ctrl]G to display the Debug window (Immediate window in Access 2000) and then typing the function name, followed by the [Enter] key.

Listing A: AddBeginners function

  
Function AddBeginners()
1 Dim db As Database, rst As Recordset
2 Dim intCounter As Integer, lSum As Long
3 Dim arrCount(1 To 4) As Long
4 Set db = CurrentDb
5 Set rst = db.OpenRecordset("tblDanceClass", _
    dbOpenDynaset)
6 rst.MoveFirst
7 For intCounter = 1 To 4
8 		arrCount(intCounter) = _
    		rst!NoOfStudents
9  	rst.MoveNext
10 Next intCounter
11 lSum = arrCount(1) + arrCount(2) _
    + arrCount(3) + arrCount(4)
12 MsgBox lSum
End Function

Our procedure is typical of most simple arrays. The first five lines declare and define the variables. Line 3 declares the variable array arrCount. The sixth line selects the first record in rst--a recordset object of our dance class records. Line 7 sets up a For loop using both the array's lower and upper limits. Line 8 grabs the current record's NoOfStudents value and assigns it to the current array element--arrCount(intCounter).

Once the For loop completes four cycles, line 11 sums the elements. Finally, the MsgBox function in line 12 displays the results of adding the elements. Note that there are simpler ways to generate the sum in this case--our main concern is demonstrating the array's behavior.

Understanding the array index

We used a For loop to populate the elements in arrCount. However, it may not be clear how to work with an individual element of the array. To do so, simply use the element's index value. In our example, we have four elements--the NoOfStudents value for each dance class. The first element contains the value 6, the second element equals 8, and so on. You can specify any of the four values using the syntax

arrCount(x)

where x equals the element's index value. Our example's index values are 1, 2, 3, and 4. The statement

arrCount(1)

returns the value 6--the number of students in Jazz101. The statement

arrCount(2)

returns the value 8--the number of students in Modern101, and so on. For a clear illustration of how the index works, create and run the procedure shown in Listing B, using the same method as before. Lines 11 through 13 simply display each array value in a message box, using permanent values in the array variable's index argument.

Listing B: OneAtATime function

  
Function OneAtATime()
1 Dim db As Database, rst As Recordset
2 Dim intCounter As Integer
3 Dim arrCount(1 To 4) As Long
4 Set db = CurrentDb
5 Set rst = db.OpenRecordset("tblDanceClass", _
	dbOpenDynaset)
6 rst.MoveFirst
7 For intCounter = 1 To 4
8    arrCount(intCounter) = rst!NoOfStudents
9    rst.MoveNext
10 Next intCounter
11 MsgBox "Number of students is " & arrCount(1)
12 MsgBox "Number of students is " & arrCount(2)
13 MsgBox "Number of students is " & arrCount(3)
14 MsgBox "Number of students is " & arrCount(4)
End Function

You can use the same behavior to set the elements. For instance, instead of using a For statement to loop through records, we could just as easily define each element using the form

arrCount(1) = 6
arrCount(2) = 8

and so on.

Working with individual elements

One of the great benefits to working with arrays is that once you've assigned values to each element, you can then work with each one separately. For instance, let's suppose you want to determine the increase in revenue realized by adding just one new student to each class. We'll assume that each class costs $25 per person.

To create a procedure to do this, set up a new function in the Module window called AddOne. Enter lines 1 through 10 of the code we used in the AddBeginners function. Then, add the code shown in Listing C before the End Function statement. Finally, test out AddOne using the same process as before.

Listing C: AddOne code

  
11 rst.MoveFirst
12 For intCounter = 1 To 4
13 lSum = (arrCount(intCounter) + 1) * 25
14 MsgBox lSum
15 Next intCounter

This procedure displays a message box with the projected total for each class if you add one student. At line 11, we reselect the first record in rst. The For loop in lines 12 through 15 adds 1 to each element and then multiples the result by 25 (the cost per person). If you wanted, you could use our previous technique to create a second array to store the results of the second For loop and return a grand total for the classes.

Creating dynamic arrays

So far we've known how many elements our array contains and specified the lower and upper bounds for the array when we declared it. When you specify the dimensions of an array when you declare it, you create what's known as a fixed-size array.

If you don't specify the size of the array when you declare it, you create what's known as a dynamic array. You must then use the ReDim keyword when you're ready to size the array. To create a dynamic array, simply leave the index dimensions blank when you declare the variable. For instance, to declare the arrCount array as a dynamic array, you'd use the statement

  
Dim arrCount() As Long

Once you're ready to set the array's size, you'd use a statement in the form

ReDim arrCount(lowerlimit To upperlimit) As Long

where lowerlimit and upperlimit are the lower- and upper-limit values or a variable that's equal to those values, respectively. Let's suppose that instead of a fixed-size array, you want to accommodate changes that may occur to the number of classes recorded in your database table. Listing D shows a procedure that sets the upper bound of the arrCount array equal to the current recordset count. The code displays counts for each class and displays the revenue based on current registrations.

Listing D: FlexibleSum() function

  
Function FlexibleSum()
1 Dim db As Database, rst As Recordset
2 Dim intCounter As Integer, lSum As Long
3 Dim intUpBound As Integer
4 Dim arrCount() As Long
5 Set db = CurrentDb
6 Set rst = db.OpenRecordset("tblDanceClass", _
   dbOpenDynaset)
7 rst.MoveLast
8 rst.MoveFirst
9 intUpBound = rst.RecordCount
10 ReDim arrCount(1 To intUpBound) As Long
11 For intCounter = 1 To intUpBound
12     arrCount(intCounter) = rst!NoOfStudents
13     rst.MoveNext
14 Next intCounter
15 For intCounter = 1 To intUpBound
16     MsgBox "Number of students is " & _
            arrCount(intCounter) _
            , , "Index: " & intCounter
17     lSum = lSum + arrCount(intCounter)
18 Next intCounter
19 MsgBox "Total Revenue: " & lSum * 25
End Function

Now, add a few new records to the table and then try out the function to examine the results. Again, we could have simplified the loops used in this code, but we want to show different ways you can work with arrays and index arguments.

If you work with dynamic arrays, you need to be aware of the Preserve keyword. You can ReDim an array any number of times in a procedure. The Preserve keyword lets you retain any existing data in an array if you ReDim it. For instance, let's say that you initially used the statement

  
ReDim arrCount(1 To 4) As Long

to size the arrCount variable and then populated the four elements. If you want to resize the array to accommodate five variables without losing the existing data, you should use the statement

ReDim Preserve arrCount(1 To 5) As Long

If you omit the Preserve keyword, VBA clears the array elements when it resizes the array.

Things you should know

You'll probably need a little practice before you become good at working with arrays. Therefore, we'd like to share just a few tidbits that we've learned the hard way:

Declaring an array actually reserves memory for the entire array. VBA doesn't care whether you fill it up or not, so be conservative when defining array elements.

Earlier, we told you that all the elements in an array must be the same data type. You can get around this restriction by declaring your array as a Variant, but a Variant array will consume more memory than any other type.

Once you're done with a dynamic array, you can use a ReDim statement to reclaim the memory your array consumed.

Conclusion

We've used simple examples to illustrate an array's basic requirements. Although they may seem daunting at first, don't avoid arrays any longer. With a little practice, you too can use arrays to simplify your code.