Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Working with Arrays

An

array is a series of variables referred to by the same name. You differentiate each element of the array by a unique index number, but all the elements must be of the same data type. Arrays help make coding more efficient. It's easy to loop through each element of an array, performing some process on each element. Arrays have a lower bound, which is zero by default, and an upper bound, and array elements must be contiguous.

The scope of an array can be Public, Module, or Local. As with other variables, this depends on where the array is declared and whether the Public, Private, or Dim keyword is used.

Declaring and Working with Fixed Arrays

When declaring a

fixed array , you give VBA the upper bound and the type of data that it will contain. The following code creates an array that holds six string variables:

Dim astrNames(5) As String

Fixed means that you cannot alter this array's size at runtime. The following code gives an example of how you can loop through the array:

Sub FixedArray()
'Declare an array of six elements
Dim astrNames(5) As String
Dim intCounter As Integer
'Populate the first four elements of the array
astrNames(0) = "Dan"
astrNames(1) = "Alexis"
astrNames(2) = "Brendan"
astrNames(3) = "Zachary"
'Use a For...Next loop to loop through the
'elements of the array
For intCounter = 0 To UBound(astrNames)
Debug.Print astrNames(intCounter)
Next intCounter
End Sub

This code starts by storing values into the first four elements of a six-element array. It then loops through each element of the array, printing the contents. Notice that the For...Next loop starts at zero and goes until the upper bound of the array, which is (5). Because the array is made up of strings, the last two elements of the array contain zero-length strings. If the array was composed of integers, the last two elements would contain zeros.

Another way to traverse the array is to use the For Each...Next construct. Your code would look like this:

Sub ArrayWith()
'Declare an array of six elements
Dim astrNames(5) As String
Dim intCounter As Integer
Dim vntAny As Variant
'Populate the first four elements of the array
astrNames(0) = "Dan"
astrNames(1) = "Alexis"
astrNames(2) = "Brendan"
astrNames(3) = "Zachary"
'Use a For...Each loop to loop through the
'elements of the array
For Each vntAny In astrNames
Debug.Print vntAny
Next vntAny
End Sub

This code declares a Variant variable called vntAny. Instead of using a loop with Ubound as the upper delimiter to traverse the array, the example uses the For Each...Next construct.

NOTE

Many people do not like the fact that, by default, the elements of an array are zero-based. Fortunately, the VBA language allows you to declare both the lower bound and the upper bound of any array. The syntax looks like this:

Dir astrNames(1 to 6)

Declaring and Working with Dynamic Arrays

Often, you don't know how many elements your array needs to contain. In this case, you should consider declaring a

dynamic array , which you can resize at runtime. Using this type of array can make your code more efficient because VBA pre-allocates memory for all elements of a fixed array, regardless of whether you store data in each of the elements. However, if you aren't sure how many elements your array will contain, pre-allocating a huge amount of memory can be quite inefficient.

To create a dynamic array, you declare it without assigning an upper bound. You do this by omitting the number between the parentheses when declaring the array, as shown in this example:

Sub DynamicArray()
'Declare a dynamic array
Dim astrNames() As String
Dim intCounter As Integer
Dim vntAny As Variant
'Resize the array to hold two elements
ReDim astrNames(1)
'Populate the two elements
astrNames(0) = "Dan"
astrNames(1) = "Alexis"
'Use a For...Each loop to loop through the
'elements of the array
For Each vntAny In astrNames
Debug.Print vntAny
Next vntAny
End Sub

However, there's a potential problem when you try to resize the array:

Sub ResizeDynamic()
'Declare a dynamic array
Dim astrNames() As String
Dim intCounter As Integer
Dim vntAny As Variant
'Resize the array to hold two elements
ReDim astrNames(1)
'Populate the two elements
astrNames(0) = "Dan"
astrNames(1) = "Alexis"
'Use a For...Each loop to loop through the
'elements of the array
For Each vntAny In astrNames
Debug.Print vntAny
Next vntAny
End Sub
Sub ResizeDynamic()
'Declare a dynamic array
Dim astrNames() As String
Dim intCounter As Integer
Dim vntAny As Variant
'Resize the array to hold two elements
ReDim astrNames(1)
'Populate the two elements
astrNames(0) = "Dan"
astrNames(1) = "Alexis"
'Resize the array to hold four elements
ReDim astrNames(3)
'Populate the last two elements
astrNames(2) = "Brendan"
astrNames(3) = "Zachary"
'Use a For..Each loop to loop through the
'elements of the array
For Each vntAny In astrNames
Debug.Print vntAny
Next vntAny
End Sub

You might expect that all four elements will contain data. Instead, the ReDim statement re-initializes all the elements, and only elements 2 and 3 contain values. You can avoid this problem by using the Preserve keyword. The following code behaves quite differently:

Sub ResizePreserve()
'Declare a dynamic array
Dim astrNames() As String
Dim intCounter As Integer
Dim vntAny As Variant
'Resize the array to hold two elements
ReDim astrNames(1)
'Populate the two elements
astrNames(0) = "Dan"
astrNames(1) = "Alexis"
'Resize the array to hold four elements
ReDim Preserve astrNames(3)
'Populate the last two elements
astrNames(2) = "Brendan"
astrNames(3) = "Zachary"
'Use a For...Each loop to loop through the
'elements of the array
For Each vntAny In astrNames
Debug.Print vntAny
Next vntAny
End Sub

In this example, all values already stored in the array are preserved. The Preserve keyword brings its own difficulties, though. It can temporarily require huge volumes of memory because, during the ReDim process, VBA creates a copy of the original array. All the values from the original array are copied to a new array. The original array is removed from memory when the process is complete. The Preserve keyword can cause problems if you're dealing with very large arrays in a limited memory situation.

TIP

Each type of array complements the other's drawbacks. As a VBA developer, you have the flexibility of choosing the right type of array for each situation. Fixed arrays are the way to go when the number of elements doesn't vary widely. Dynamic arrays should be used when the number varies widely, and you're sure you have enough memory to resize even the largest possible arrays.

Passing Arrays as Parameters

Many people are unaware that you can pass an array as a parameter to a function or subroutine. The following code provides an example:

Sub PassArray()
'Declare a six-element array
Dim astrNames(5) As String
Dim intCounter As Integer
'Call the FillNames function, passing a reference
'to the array
Call FillNames(astrNames)
'Use a For...Next loop to loop through the
'elements of the array
For intCounter = 0 To UBound(astrNames)
Debug.Print astrNames(intCounter)
Next intCounter
End Sub

The code begins by declaring a fixed array called astrNames. The code calls the FillNames routine. It receives the array as a parameter and then populates all its elements. The PassArray routine is then able to loop through all the elements of the array that was passed, displaying information from each element. The FillNames routine looks like this:

Sub FillNames(varNameList As Variant)
'Populate the elements of the array
varNameList(0) = "Alison"
varNameList(1) = "Dan"
varNameList(2) = "Alexis"
varNameList(3) = "Brendan"
varNameList(4) = "Zachary"
varNameList(5) = "Sonia"
End Sub

Notice that the routine receives the array as a variant variable. It then populates each element of the array.