Before we look at the types of arrays at our disposal, let's quickly cover some of the terminology used when talking about arrays. Creating an array is called dimensioning the array (i.e., defining its size). The individual data items within the array
are known as elements, and the number used to access an element is known as an index. The lowest and highest index numbers are known as bounds or boundaries. In VBA, there are four types of arrays: arrays can be either fixed or dynamic, and arrays can also be either one-dimensional or multidimensional.
Most of the time, we know how many values we need to store in an array in advance. We can therefore dimension it to the appropriate size, or number of elements, prior to accessing it by using a Dim statement like the following:
Dim myArray(5) As Integer
This line of code creates an array, named myArray, with six elements. Why six? All VBA arrays start with location 0, so this Dim statement creates an array whose locations range from myArray(O) to myArray(5).
Populating Arrays: The Array Function
If you want to populate an array with a series of values, use the Array function. The function allows you to quickly assign a range of comma-delimited values to an array. For instance:
myArray = Array(12,3,13,64,245,75)
To use the Array function, simply declare a variant variable, then assign the values of the array to the variable using the Array function. Any data type (even mixed data types) can be used with the Array function. Another point to remember about arrays built using the Array function is that they are always based at 0, regardless of the Option Base setting, which is used to define the lower boundary of an array.
But what happens if you try to access an element greater than five or less than zero? You get an error message, "Subscript out of range." In the next section you'll see how to check the size of the array before attempting to access a given element.
Fixed arrays are fine when we know in advance how many values or elements we need. But there are many cases where we do not have prior knowledge of this, and we need a way to expand our array should we have to. For example, one convenient use of an array is to store input from the user and allow the user to input as many items of data as he or she likes. Our application therefore has no way of knowing how to dimension the array beforehand. We can handle this situation by declaring and using a dynamic array. Dynamic arrays allow you to expand the number of array elements using the ReDim statement to redimension the array while the program is running.
40 Chapter 3 - VBA Variables and Data Types
A dynamic array is declared by leaving out its number of elements, like this:
Dim iDynamicArray() As Integer
When you need to resize the array, use the ReDim keyword:
You can also declare a dynamic array and specify the initial number of elements at the same time by using ReDim:
ReDim anyDynamicArray(4) As Integer
There is no limit to the number of times you can redimension a dynamic array, but obviously, messing around with variables in this way carries an element of risk. As soon as you redimension an array, the data contained within it is lost. Don't panic; if you need to keep the data, use the Preserve keyword:
ReDim Preserve myDynamicArray(10)
In fact, ReDim creates a new array (hence its emptiness). Preserve copies the data from the old array to the new array. Another important point to note is that if you resize an array by contracting it, you always lose the data in the deleted array elements.
Note that while you can resize an array by modifying its upper bound, you can't resize the lower bound of an array; this generates runtime error 9, "Subscript out of range."
ReDim Preserve's Performance
As you have seen, dynamic arrays are resized using the Preserve keyword by creating a new array in memory and copying the contents of the old array into the new. This can obviously have an adverse affect on application performance. The larger and more complex the array becomes, the longer it takes to resize. Wherever possible, you should use fixed size arrays. And if you do choose to use dynamic arrays, you should avoid resizing the array each time you want to add an element; instead, you should add an arbitrary number of elements at a single time, as the following code fragment illustrates:
If lngCurPtr > UBound(varArray) Then
ReDim Preserve varArray(UBound(varArray) + 10) End If
In this case, we add 10 elements each time we redimension varArray. Depending on the expected size of the array, we could select any number greater than one, or we could even double the size of the array as long as we were reasonably certain that the array would remain fairly small (geometric progression has a habit of consuming memory very rapidly).
The following snippet shows how to use a dynamic array to save multiple inputs from the user. When the user clicks on the cmdButtonl button, the contents of the text box are added to sMyArray, an array that is dynamically resized beforehand:
Option Explicit 'require variable declaration
ReDim sMyArray(O) As String 'create a 1-element dynamic array Dim ilndex As Integer 'variable to track array index ilndex = 0 'assign the first index number
'Store the user input in the array sMyArray(intlndex) = txtTextl.Text 'increment the array counter by one ilndex = ilndex + 1 'increase the size of the array ReDim Preserve sMyArray(ilndex)
txtTextl.Text = "" 'Empty the text box again
The above example is fine as it stands, except that, as you can see from the source code, we have to keep track of the size of the array by using the intlndex variable. But VBA allows a much cleaner approach to the problem of finding out how many elements there are in the array.
Determining array boundaries
The UBound and LBound functions can find the upper index and the lower index, respectively, of an array.
The syntax for UBound is:
x = UBound( arrayname)
UBound returns the highest index number of an array. The actual number of elements in the array depends upon the starting point of the array. If the default lower boundary of 0 has been used, then UBound is one less than the actual number of elements in the array. For example, if sMyArray has 10 elements and a lower boundary of 0, Ubound(sMyArray) returns the number 9. So we would determine the total number of elements in an array as follows:
iArraySize = UBound( array) + 1
If, however, the lower boundary has been set to 1, UBound returns the actual number of elements is the array. It therefore makes sense to use the LBound function in conjunction with the UBound function to determine the actual number of elements in the array, as follows:
The UBound function is especially useful when dealing with dynamic arrays, as this snippet demonstrates:
Private sValues() As String
42 Chapter 3 - VBA Variables and Data Types
Private Sub Form_Load()
ReDim sValues(0) End Sub
Private Sub Command1_Click()
sValues(UBound(sValues)) = txtTextBox.Text ReDim Preserve sValues(UBound(sValues) + 1)
Note that using the UBound function on an uninitialized array generates a Subscript Out of Range error; therefore, the Form_Load event is used to redimension the array to 0 to insure that the array has one element.
By default, VBA arrays start with element 0. However, you can change this on a per-module basis by using the Option Base statement in the declarations section of your module. For example:
generates arrays starting with element 1. The Option Base statement must be used in the module before any variable declarations.
Another method used to set the lower boundary is to specify both the lower and upper boundaries when the array is dimensioned, as the following syntax shows:
Dim arrayname (lowerboundary To upperboundary) As datatype
The arrays we have looked at so far are single-dimension arrays; they hold one element of data in each index location, which is fine for most needs. However, sometimes you need a full set of data for each element; this is called a multidimensional array.
In a single-dimension array, the data held within has no structure; it's accessed sequentially, and there is one piece of data for each element. When you need to store more than this one piece of data for each logical element, you should use either a multidimensional array or a user-defined type (which is discussed in the next section).
A multidimensional array allows you to have a separate array of data for each element of your array. Therefore, each element of the array in turn contains an array. The structure of a multidimensional array resembles that of a database table. The rows (or records) of the table represent the first dimension, and the columns (or fields) represent by the second dimension, as the following table illustrates.
Array Element (0,0)
Array Element (0,2)
Multidimensional arrays can contain up to 60 dimensions, though it's extremely rare to use more than two or three dimensions.
To define a multidimensional array, use the following syntax:
Dim arrayname(upperboundDimension1, _
upperboundDimension2, ) As Datatype
As with single-dimension arrays, you can also specify the lower boundary within the array definition, and you can specify different lower boundaries for each element. For example:
Private myArray(1 To 20, 0 To 50) As String Dynamic multidimensional arrays
Like single-dimension arrays, multidimensional arrays can be dynamic, and the rules for redimensioning them are similar. But since you have more than one dimension to think about, you have to take care how you use and redimension your array. The rules for using a dynamic multidimensional array are:
• You can ReDim a multidimensional array to change both the number of dimensions and the size of each dimension. This is illustrated by the following, where the myArray dynamic array is originally defined as a two-dimensional array with 11 elements in the first dimension and 6 in the second, but is then redimensioned into a three-dimensional array with 5 elements in the first dimension, 11 in the second, and 3 in the third.
Private myArray() As Integer Private Sub cmdButtonOne_OnClick
ReDim myArray(10,5) End Sub
Private Sub cmdButtonTwo_OnClick
ReDim myArray(4,10,2) End Sub
• If you use the Preserve keyword, you can only resize the last array dimension, and you can't change the number of dimensions at all. For example:
ReDim Preserve myArray(10,5,4)
44 Chapter 3 - VBA Variables and Data Types
Using UBound and LBound with multidimensional arrays
As you saw earlier, the UBound function returns the highest subscript (element number) in an array—that is, its Upper Boundary. You can also use UBound with a multidimensional array, except that to find the largest element of a multidimensional array, you need to also specify a dimension:
largestElement = UBound( arrayname, dimensionNo)
The same is true of the LBound function:
smallestElement = LBound(arrayname, dimensionNo)
Was this article helpful?