Arrays

An array variable is a collection of variables that use the same name, but are distinguished by an index value. For instance, to store the first 100 cells in the first row of a worksheet, we could declare an array variable as follows:

Dim Cell(1 To 100) As Range

(There is no Cell object in the Excel object model: a cell is a special Range object.) The array variable is Cell. It has size 100. The lower bound of the array is 1 and the upper bound is 100. Each of the following variables are Range variables (that is, variables of the object type Range):

Note that if we omit the first index in the declaration, as in:

Dim Cell(100) As Range then VBA will automatically set the first index to 0 and so the size of the array will be 101.

The virtue of declaring array variables is clear, since it would be very unpleasant to have to declare 100 separate variables! In addition, as we will see, there are ways to work collectively with all of the elements in an array, using a few simple programming constructs. For instance, the following code boldfaces the values in each of the 100 cells along the diagonal of the active worksheet:

Set Cell(i) = Cells(i,i) Cell(i).Font.Bold = True Next i

5.4.9.1 The dimension of an array

The Cell array defined in the previous example has dimension one. We can also define arrays of more than one dimension. For instance, the array:

Dim Cell(1 To 10, 1 To 100) As Range is a two-dimensional array, whose first index ranges from 1 to 10 and whose second index ranges from 1 to 100. Thus, the array has size 10*100 = 1000.

5.4.9.2 Dynamic arrays

When an array is declared, as in:

Dim FileName(1 To 10) As String the upper and lower bounds are both specified and so the size of the array is fixed. However, there are many situations in which we do not know at declaration time how large an array we may need. For this reason, VBA provides dynamic arrays and the ReDim statement.

A dynamic array is declared with empty parentheses, as in:

Dim FileName() as String

Dynamic arrays can be sized (or resized) using the ReDim statement, as in:

ReDim FileName(1 to 10)

This same array can later be resized again, as in:

ReDim FileName(1 to 100)

Note that resizing an array will destroy its contents unless we use the Preserve keyword, as in:

ReDim Preserve FileName(1 to 200)

However, when Preserve is used, we can only change the upper bound of the array (and only the last dimension in a multidimensional array).

5.4.9.3 The UBound function

The UBound function is used to return the current upper bound of an array. This is very useful in determining when an array needs redimensioning. To illustrate, suppose we want to collect an unknown number of filenames in an array named FileName. If the next file number is iNextFile, the following code checks to see if the upper bound is less than iNextFile; if so, it increases the upper bound of the array by 10, preserving its current contents, to make room for the next filename:

If UBound(FileName) < iNextFile Then

ReDim Preserve FileName(UBound(FileName) + 10) End If

Note that redimensioning takes time, so it is wise to add some "working room" at the top to cut down on the number of times the array must be redimensioned. This is why we added 10 to the upper bound in this example, rather than just 1. (There is a trade-off here between the extra time it takes to redimension and the extra space that may be wasted if we do not use the entire redimensioned array.)

0 0

Responses

  • Scott
    How to set uper bound and lower bound on number in execl programming?
    7 years ago

Post a comment