An array is a variable that simultaneously stores several values of the same type. Thus, an array represents a collection of homogeneous indexed variables.
The number of indices of an array may also vary. Most often, arrays with one or two indices are used; less often—with three; arrays with even more indices are extremely rare. In VBA, it is allowed to use up to 60 indices. The number of indices of an array is usually referred to as the dimension of the array. Arrays with one index are called one-dimensional, with two—two-dimensional, and so on. Arrays with a larger number of dimensions can occupy very large amounts of memory, so one should be cautious in their use.
Before using an array, it must be declared with the Dim statement and the type of the stored values must be specified. All values in an array belong to one data type. This restriction can be bypassed in practice by declaring the array as type Variant—in that case, the elements of the array may take values of different types. The syntax of the array declaration statement is as follows:
Dim <ArrayName>(<size1>, <size2>, ...) As <DataType>
where the values <size1>, <size2> specified in parentheses define the dimensions of the array—the number of indices and the maximum permissible value for each specific index. By default, array elements are indexed starting at zero. For example, the declaration:
Dim Array1(9) As Integer
defines a one-dimensional array of 10 elements, which are integer variables, while the declaration:
Dim Array2(4, 9) As Variant
defines a two-dimensional array of 50 elements, which are variables of the universal type Variant.
NOTE
The default lower bound of an array (the index) does not have to be zero. To change this default value, use the Option Base statement. For example, if you place the statement Option Base 1 at the beginning of your module, array indexing will by default start from one instead of zero.
For instance, the following operator declares a vector consisting of 11 elements:
Option Base 1 Dim A(11) As Integer
Another way to change the base index is to use the keyword To when declaring an array:
Dim B(1 To 3, 1 To 3) As Single Dim A(1 To 12) As Integer
When declaring an array, you can specify not only the upper bound of the index but also its lower bound, i.e., explicitly define the range of a given array index. The lower bound can be any integer, not necessarily non-negative. The syntax of such a definition looks like this:
Dim <ArrayName>(<min1> To <max1>, ...) As <DataType>
For example, if you intend to work with an array of meteorological data representing the average daily temperatures for the last two weeks, it may be convenient to define the array as follows:
Dim Temperature(-14 To 0) As Single
In this case, for example, Temperature(-2) would correspond to the temperature of the day before yesterday, and to determine the required index for the day of interest, it is enough to use the difference between the dates.
In the examples given above, we dealt with fixed-size arrays, where the number of elements was explicitly specified at the time of declaration with the Dim statement. Such arrays are called static. In VBA, it is also possible to use dynamic arrays, whose size is not fixed at the time of declaration. The definition of the size of a dynamic array can be made directly during program execution.
When defining a dynamic array, the Dim statement contains only the name of the array followed by empty parentheses and the data type. The number of indices and their ranges are not specified. However, before using the array, the ReDim statement must be executed, which defines the dimensions and ranges of the dynamic array indices.
The syntax for declaring and defining the size of a dynamic array is:
Dim <ArrayName>() As <DataType> ReDim <ArrayName>(<size1>, <size2>, ...)
Here is an example of declaring, sizing, and using a dynamic array, and then changing the size and dimensions of the same array:
Dim dArray() As Variant ReDim dArray(1, 2) dArray(0, 0) = 2 dArray(0, 1) = 3 k = dArray(0, 0) + dArray(0, 1) ReDim dArray(k) dArray(0) = "String1"
In this example, the array dArray is first defined as a two-dimensional array of six elements, and then redefined as a one-dimensional array, with the upper bound of the index set by the value of variable k.
NOTE
To determine the current lower or upper bound of an array, you can use the functions LBound() and UBound(), respectively.
For example, the following code will display 100 and 5:
Dim A(1 To 100, 0 To 5) MsgBox UBound(A, 1) & vbCr & UBound(A, 2)
The following instructions allow you to iterate through the elements of an array without explicitly specifying its size:
Dim d As Variant
Dim i As Integer
d = Array("Mon", "Tue", "Wed", "Thu", "Fri")
For i = LBound(d) To UBound(d)
MsgBox d(i)
Next
Keep in mind that by default, when the size of an array is changed, new memory is allocated for it, and the current values of its elements are lost. To preserve the current values of the array when changing its size, the keyword Preserve is used.
For example, to increase the size of the array dArray by one element without losing the values of the existing elements, you can do the following:
ReDim Preserve dArray(UBound(dArray) + 1)
Element-by-Element Initialization of an Array
An array can be initialized element by element in the following ways:
- With a sequence of assignment statements:
Dim B(1, 1) As Single B(0, 0) = 2 B(0, 1) = 4 B(1, 0) = 1 B(1, 1) = 6 Dim M(1 To 9, 1 To 9) As Integer
- With a loop statement:
Dim i As Integer Dim j As Integer For i = 1 To 9 For j = 1 To 9 M(i, j) = i * j Next Next
Array Initialization Using the Array() Function
As mentioned earlier, a convenient way to define one-dimensional arrays is the Array() function, which converts a list of elements separated by commas into a vector of these values and assigns them to a variable of type Variant. Initialization of both one-dimensional and multi-dimensional arrays is possible by using nested Array() function constructions.
- Initialization of a one-dimensional array:
Dim num As Variant Dim s As Double num = Array(10, 20) s = num(0) + num(1) MsgBox s
- Initialization of a multi-dimensional array:
Dim CityCountry As Variant
CityCountry = Array(Array("Saint Petersburg", "Russia"), _
Array("Cape Town", "South Africa"))
MsgBox CityCountry(0)(0)
' Displays Saint Petersburg
MsgBox CityCountry(0)(1)
' Displays Russia
Array and Range
In VBA, there is a close relationship between ranges and arrays. It is possible both to fill an array with values from the cells of a range using a single assignment operator, and conversely, to fill a range of cells with the elements of an array using a single assignment operator. In such cases, the array must be declared as a variable of type Variant.
- Initializing an array from a range with a single assignment operator:
Dim r As Range
Set r = Range("C1:D3")
Dim M As Variant
M = r.Value
Dim i As Integer
Dim j As Integer
For i = 1 To r.Rows.Count
For j = 1 To r.Columns.Count
Cells(i, j).Value = M(i, j)
Next
Next
- Filling a range from an array with a single assignment operator:
Dim M(1 To 9, 1 To 9) Dim i As Integer Dim j As Integer For i = 1 To 9 For j = 1 To 9 M(i, j) = i * j Next Next Dim r As Range Set r = Range(Cells(1, 1), Cells(9, 9)) r.Value = M
Using Dynamic Arrays
Let us give an example of using the ReDim statement to change the number of elements and dimensions of an array (Listing 2.2). In the given example, an array is created to store the results of coin tosses. The coin is tossed until heads appears three times. The dimension of the dynamic array is adjusted after each toss while preserving the previously recorded toss results, thanks to the use of the keyword Preserve.
Changing the dimension of a dynamic array while preserving its contents
Dim Attempt() Dim i As Integer Dim score As Integer Dim coin As Integer i = 0 score = 0 Do i = i + 1 coin = Int(2 * Rnd()) ' 0 — tails ' 1 — heads If coin = 1 Then score = score + 1 ReDim Preserve Attempt(i) Attempt(i) = coin Loop Until score = 3
How to check whether a Variant variable contains an array of values?
The IsArray() function returns True if the specified variable of type Variant contains an array, and False otherwise. For example, the following procedure (Listing 2.3), which handles the SelectionChange event of the Worksheet object, displays a message if the selected range contains an array of cells.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim A As Variant A = Target.Value If IsArray(A) Then MsgBox "Contains an array of selected cells" Else MsgBox "Does not contain an array of selected cells" End If End Sub
Reinitializing an Array and Releasing the Memory Allocated to the Array
The Erase statement reinitializes the elements of fixed-size arrays and releases the memory allocated to a dynamic array. The Erase statement sets the elements of fixed-size arrays as follows:
- a numeric array or an array of fixed-length strings (assigns the value 0 to each element);
- an array of variable-length strings (assigns an empty string « » to each element);
- an array of type Variant (assigns the value Empty to each element).
For example, in the following code, the value 1 will be displayed first, and then 0:
Dim A(2) As Integer A(2) = 1 MsgBox A(2) Erase A MsgBox A(2)
The Erase statement also releases the memory used by dynamic arrays. Before the program can reference the dynamic array again, it is necessary to redefine the dimensions of the array variable using the ReDim statement. For example:
Dim B() As Integer ReDim B(6) Erase B ReDim B(3)