Microsoft Office Tutorials and References
In Depth Information
(If the currently active object is not a chart, then the variable chrt will be set to the special value
Nothing . We will discuss Nothing later.)
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):
Cell(1), Cell(2),..., Cell(100)
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
For i = 1 To 100
Set Cell(i) = Cells(i,i)
Cell(i).Font.Bold = True
188.8.131.52 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.
184.108.40.206 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.