If you have programmed in other languages, you’re probably familiar with the concept of arrays. You use arrays to refer to a series of variables by the same name while using a number (an index) to tell them apart. This helps you create smaller and simpler code in many situations because you can set up loops that deal efficiently with multiple cases by using the index number. Arrays have both upper and lower bounds, and the elements of the array are contiguous within those bounds. Because Visual Basic allocates space for each index number, avoid declaring an array larger than you need it to be.
All the elements in an array have the same data type. Of course, when the data type is Variant, the individual elements may contain different kinds of data (strings, numbers, date/time values, or objects). You can declare an array with any of the fundamental data types, including user-defined types, and object variables.
See Also For more information on user-defined types, see “Creating Your Own Data Types” earlier in this chapter. For information on object types, see Chapter 5, “Working with Objects and Collections.”
You can declare an ordinary (fixed-size) array in three ways, depending on the scope you want the array to have:
There are additional rules when you create a dynamic array (an array whose size can change at run time).
See Also For more information on dynamic arrays, see “Dynamic Arrays” later in this chapter.
When declaring an array, follow the array name with the upper bound in parentheses. The upper bound must be a Long data type (in the range -231 to 231). For example, the following array declarations can appear in the Declarations section of a module:
Dim intCount(14) As Integer ' Declares an array with 15 ' elements (0 through 14).
Dim dblSum(20) As Double ' Declares an array with 21 ' elements (0 through 20).
To create a public array, you use Public in place of Dim (or Private):
Public intCount(14) As Integer
Public dblSum(20) As Double
To create a local array, use the Dim (or Static) statement:
Dim intCount(14) As Integer
Static dblSum(20) As Double
The first declaration creates an array with 15 elements, with index numbers running from 0 through 14. The second creates an array with 21 elements, with index numbers running from 0 through 20. The default lower bound is 0. However, you can change the default lower bound to 1 by placing the following Option Base statement in the Declarations section of a module:
Option Base 1
See Also For more information on the Option Base statement, search the Help index for “Option Base statement.”
Another way to specify the lower bound is to provide it explicitly by using the To keyword. For example:
Dim intCount(1 To 15) As Integer
Dim dblSum(100 To 120) As Double
In the preceding declarations, the index numbers of intCount run from 1 through 15 (15 elements), and the index numbers of dblSum run from 100 through 120 (21 elements).
Note With some versions of Basic, you can use an array without first declaring it. With Visual Basic, this isn’t possible; you must declare an array before using it.
With Visual Basic, you can declare arrays of up to 60 dimensions. For example, the following statement declares a two-dimensional 10-by-10 array within a procedure:
Static dblMatrix(9, 9) As Double
Either or both dimensions can be declared with explicit lower bounds:
Static dblMatrix(1 To 10, 1 To 10) As Double
You can extend this to more than two dimensions, as in the following example:
Dim intMultiD(3, 1 To 10, 1 To 15) As Integer
This declaration creates a three-dimensional 4-by-10-by-15 array. The total number of elements is the product of these three dimensions, or 600.
Note Because the total storage needed by the array increases dramatically when you start adding dimensions to it, be sure to use multidimensional arrays with care. Be especially careful with Variant arrays, because Variant arrays are larger than arrays containing other data types.
Loops often provide an efficient way to manipulate arrays. For example, the following loop initializes all elements in the array to 5:
Static intCount(1 To 15) As Integer
Dim intX As Integer
For intX = 1 To 15
intCount(intX) = 5
Next intX
You can efficiently process a multidimensional array by using nested For loops. For example, the following statements initialize every element in dblMatrix to a value based on its location in the array:
Dim intX As Integer, intY As Integer
Static dblMatrix(1 To 10, 1 To 10) As Double
For intX = 1 To 10
For intY = 1 To 10
dblMatrix(intX, intY) = intX * 10 + intY
Next intY
Next intX
Sometimes you may not know exactly how large to make an array. You may want to be able to change the size of the array at run time.
A dynamic array can be resized at any time. Dynamic arrays are among the most flexible and convenient features in Visual Basic, and they help you to manage memory efficiently. For example, you can use a large array for a short time and then free up memory to the system when you’re no longer using the array.
The alternative is to declare an array with the largest anticipated size and then to ignore array elements you don’t need. However, if overused, this approach may cause Microsoft Access to run low on memory.
Û To create a dynamic array
Dim intDynArray() As Integer
ReDim intDynArray(intX + 1)
The ReDim statement can appear only in a procedure. Unlike the Dim and Static statements, ReDim makes the application carry out an action at run time.
The ReDim statement supports the same syntax as that used for fixed arrays. Each ReDim statement can change the number of elements, as well as the lower and upper bounds for each dimension. However, the number of dimensions in the array can’t change from the number used in the first ReDim statement.
See Also For more information on array syntax, see “Arrays” earlier in this chapter.
For example, you create the dynamic array intMatrix by first declaring it at the module level:
Dim intMatrix() As Integer
A function then allocates space for the array:
Function CalcValuesNow () As Integer
.
.
.
ReDim intMatrix(19, 29)
End Function
The ReDim statement allocates a matrix of 20-by-30 integers (for a total size of 600 elements). Alternatively, the bounds of a dynamic array can be set by using variables:
ReDim intMatrix(intX, intY)
It’s important to note that each time you run the ReDim statement, all the values currently stored in the array are lost. Visual Basic resets the values to the Empty value (for Variant arrays), to zero (for numeric arrays), to a zero-length string ("") (for string arrays), or to Nothing (for arrays of objects).
This is useful when you want to prepare the array for new data or when you want to shrink the size of the array to take up minimal memory. However, you may sometimes want to change the size of the array without losing the data in the array. You can do this by using the ReDim statement with the Preserve keyword. For example, you can enlarge an array by one element without losing the values of the existing elements, as shown in the following code:
ReDim Preserve intMatrix(UBound(intMatrix) + 1)
Only the upper bound of the last dimension in a multidimensional array can be changed when you use the Preserve keyword. If you change any of the other dimensions or the lower bound of the last dimension, a run-time error occurs. Therefore, you can use code as follows:
ReDim Preserve intMatrix(10, UBound(intMatrix, 2) + 1)
But you cannot use the following code:
ReDim Preserve intMatrix(UBound(intMatrix, 1) + 1, 10)
See Also For more information on the Preserve keyword, search the Help index for “ReDim statement.”