3.5. Array Variables

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.

3.5.1. Fixed Arrays

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(0)to myArray(5).

myArray = Array(12,3,13,64,245,75)

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.

3.5.2. Dynamic Arrays

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.

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:

ReDim iDynamicArray(10)

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."

If lngCurPtr > UBound(varArray) Then
   ReDim Preserve varArray(UBound(varArray) + 10)
End If

The following snippet shows how to use a dynamic array to save multiple inputs from the user. When the user clicks on the cmdButton1 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(0) As String 'create a 1-element dynamic array 
Dim iIndex As Integer     'variable to track array index 
iIndex = 0                  'assign the first index number 

Sub cmdButton1_OnClick				
   'Store the user input in the array 
   sMyArray(iIndex) = txtText1.Text
   'increment the array counter by one      
   iIndex = iIndex + 1
  'increase the size of the array					         
   ReDim Preserve sMyArray(iIndex)      
   txtText1.Text = ""       'Empty the text box again
End Sub

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 intIndex variable. But VBA allows a much cleaner approach to the problem of finding out how many elements there are in the array.

3.5.2.1. 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 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:

iArraySize = UBound(array) - LBound(array) +  1

The UBound function is especially useful when dealing with dynamic arrays, as this snippet demonstrates:

Option Explicit

Private sValues() As String

Private Sub Form_Load()
    ReDim sValues(0)
End Sub

Private Sub Command1_Click()
    
    sValues(UBound(sValues)) = txtTextBox.Text
    ReDim Preserve sValues(UBound(sValues) + 1)
        
End Sub

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 to insure that the array has one element.

3.5.2.2. Setting the lower boundary

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:

Option Base 1

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

3.5.3. Multidimensional Arrays

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.

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

3.5.3.1. 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 myArray(10,5,2)
  ...
  ReDim Preserve myArray(10,5,4)
  ...

  
  

3.5.3.2. 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 U pper Bound ary. 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)