To create a dynamic arrayIf you've programmed in other languages, you're probably familiar with the concept of arrays. An array is a named collection of variables of the same data type. Each array element can be distinguished from other elements by one or more integer indexes. For example, if the sheetNames array contains three names, you can set and return the names as shown in the following example.
sheetNames(1) = "sheet1" sheetNames(2) = "module1" sheetNames(3) = "sheet2" MsgBox sheetNames(2)
Arrays allow you to group related variables in a way that makes it easier for you to keep track of, access, and manipulate them all at once, while still being able to access each variable individually. This helps you create smaller and simpler macros in many situations, because you can set up loops using index numbers to deal efficiently with any number of cases.
When you create an array, its size is determined by the number of dimensions it has and the by the upper and lower bounds for the index numbers in each dimension. Arrays in Visual Basic can have up to 60 dimensions; the number of elements is limited by the amount of available memory. The sheetNames array in the earlier example has one dimension and three elements; the lower bound is one and the upper bound is three.
Because Visual Basic allocates space for every possible element in a declared array, whether or not you actually store a value in that element, you should avoid declaring an array any larger than is necessary. If you know ahead of time what size your array needs to be, you can declare a fixed-size array, or static array, and stipulate the exact dimensions and upper and lower bounds from the outset. If you don't know how many values you'll need to store in an array, you can declare a dynamic array without specifying the number of dimensions or the size of each dimension.
All the elements in an array must have the same data type. If you want to create an array whose elements can contain different kinds of data (objects, strings, numbers, and so on), you should declare an array of the Variant type. You can declare an array of any of the fundamental data types, including user-defined types (described in "User-Defined Data Types" later in this chapter) and object variables (described in Chapter 4, "Objects and Collections").
You must explicitly declare an array before you can use it; you cannot implicitly declare an array. The procedure for declaring an array is very similar to the procedure for declaring a variable. You use the Private, Public, Dim, and Static keywords to specify the array scope and lifetime, you use integer values to specify the upper and lower bounds for each dimension, and you use the As keyword to specify the data type for the array elements.
The rules for determining the scope of an array are identical to those for variables. For more information, see "Specifying Variable Scope and Lifetime" earlier in this chapter.
When you declare an array, you specify the upper and lower bounds for each dimension within the parentheses following the array name. The upper and lower bounds must be integers. The values can be positive, negative, or 0 (zero). You must use constant values to specify the bounds of fixed-size arrays (for information about using variables to specify the bounds of a dynamic array, see "Creating Dynamic Arrays" later in this chapter).
If you specify only one value for a dimension, Visual Basic interprets the value as the upper bound and supplies a default lower bound. The default lower bound is 0 (zero) unless you set it to 1 using the Option Base statement. For example, the following code declares one-dimensional arrays containing 15 and 21 elements, respectively.
Dim counters(14) As Integer Dim sums(20) As Double
If you haven't changed the default lower bound, the index numbers in these arrays range from 0 to 14 and from 0 to 20, respectively. If you wish the index numbers to start at 1 instead of 0, you can change the default lower bound to 1 by placing an Option Base statement in the declarations section of a module:
Option Base 1
Tip
You should always include Option Base 1 in your modules. Microsoft Excel collections are always 1-based, and any arrays that Microsoft Excel methods or properties return are also 1-based. If you assign a 1-based array to a 0-based array, the same information exists in both arrays, but each element is off by one index. This can make your macros confusing and hard to debug.
You can also specify the lower bound of a dimension explicitly. To do this, separate the lower and upper bounds with the To keyword, as in the following examples.
Dim counters(1 To 15) As Integer Dim sums(100 To 120) As String
In the preceding declarations, the index numbers of counters range from 1 to 15, and the index numbers of sums range from 100 to 120.
Tip
You can use the Visual Basic LBound and UBound functions to determine the existing lower and upper bounds of an array.
After you've declared an array, you can use it in your code. You access each individual element in the array by using the element's index value. For example, the following code fills a 10-element array with random numbers and then displays the fourth element in the array.
Sub RandomArray()
Dim i As Integer, rnums(10) As Integer
For i = 1 To 10
rnums(i) = Int(Rnd() * 6) + 1
Next
MsgBox rnums(4)
End Sub
If you don't know how large to make an array, or if you want to be able to change the size of the array at run time, you can use a dynamic array. A dynamic array can be resized at any time. Dynamic arrays are among the most flexible and convenient features in Visual Basic, helping you manage memory efficiently. For example, you can use a large dynamic array for a short time and then free up system memory when you're no longer using the array.
To create a dynamic array
1. Declare the array just as you would declare a fixed-size array, but without specifying dimension sizes within the parentheses following the array name. For example:
Dim dynArray()
2. Later in the macro, allocate the actual number of elements with a ReDim statement, as in the following example.
ReDim dynArray(X + 1)
The ReDim statement can appear only in a procedure. Unlike the Dim and Static statements, ReDim is an executable statement — that is, it makes the application carry out an action at run time. Each ReDim statement can change the number of elements, the lower and upper bounds for each dimension, and the number of dimensions in the array.
The following example first declares the dynamic array ovalLeftCoords, allocates space in the array based on the number of ovals on Worksheet 1, and fills the array with the left coordinates of the ovals.
Sub FillArray()
Dim ovalLeftCoords() As Integer
Dim i As Integer, ovalCount As Integer, ovs As Ovals
Set ovs = Worksheets(1).Ovals
ovalCount = ovs.Count
ReDim ovalLeftCoords(ovalCount)
For i = 1 To ovalCount
ovalLeftCoords(i) = ovs(i).Left
Next
End Sub
Each time you use 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 0 (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 reduce the size of the array so that it occupies minimal memory. However, sometimes you may want to change the size of the array without losing the data in it. You can do this by using ReDim with the Preserve keyword. For example, you can enlarge an array by one element without losing the values of the existing elements.
ReDim Preserve myArray(UBound(myArray) + 1)
The UBound function returns the value of the upper bound for the specified dimension of an array. For more information, see "UBound" in Help.
Only the upper bound of the last dimension in a multidimensional array can be changed when you use the Preserve keyword; if you change either bound of any of the other dimensions, or the lower bound of the last dimension, a run-time error occurs. Thus, you can do the following:
ReDim Preserve matrix(10, UBound(matrix, 2) + 1)
But you cannot do this:
ReDim Preserve matrix(UBound(matrix, 1) + 1, 10)
With Visual Basic, you can declare arrays of up to 60 dimensions simply by specifying the size of each dimension within the parentheses following the array name. Separate the sizes of different dimensions with commas. For example, the following statement declares a two-dimensional, 10-by-10 array within a procedure.
Static matrixA(10, 10) As Double
Either dimension, or both, can be declared with explicit lower bounds.
Static matrixA(1 To 10, 1 To 10) As Double
You can extend this to more than two dimensions, as in the following example.
Dim multiD(4, 1 To 10, 1 To 15)
The preceding declaration creates an array with three dimensions, whose sizes are 4, 10, and 15. The total number of elements is the product of these three dimensions, or 600.
Tip
When you start adding dimensions to an array, the total storage needed by the array increases dramatically, so use multidimensional arrays with care. Be especially careful with Variant arrays, because they're larger than arrays of other data types.
You can efficiently process a multidimensional array by using nested For...Next loops. For example, these statements initialize every element in matrixA to a value based on its location in the array.
Dim i As Integer, j As Integer
Static matrixA(1 To 10, 1 To 10) As Double
For i = 1 To 10
For j = 1 To 10
matrixA(i, j) = I * 10 + J
Next j
Next i