Microsoft Office Tutorials and References
In Depth Information
Boundaries in Arrays
BoundAriEs in ArrAys
Arrays have two boundaries: a lower boundary, which is the position of the first data element, and
an upper boundary representing the count of elements in the array. VBA keeps track of both
boundaries’ values automatically, with the LBound and UBound functions.
When you declare an array, you can specify only the upper index boundary.
In the example, you have Dim myDays(6) As String but it could have been
written as Dim myDays(0 to 6) As String . The “0 to” does not need to be
present because the lower index boundary is always assumed to be 0 (or 1 if
Option Base 1 has been stated at the top of the module). Under the default
setting of Option Base 0 , the number you include in the declaration (which
was 6 in this example) is the upper index number of the array, not the actual
number of elements.
Here is an example to demonstrate the LBound and UBound functions in practice. In this example,
you fill an array with a number of cell addresses, and the macro enters the word Hello in that array
of cell ranges.
‘Declare your variables
Dim sheetName As Variant, i As Integer, TargetCell as Variant
‘Populate the array yourself with the known worksheet names.
TargetCell = Array(“A1”, “B5”, “B7”, “C1”, “C12”, “D13”, “A12”)
‘Loop from the lower boundary (the first array element)
‘to the upper boundary (last element) of your sheetName array.
For i = LBound(TargetCell) To UBound(TargetCell)
Range(TargetCell(i)).Value = “Hello”
‘Continue looping through the array elements to completion.
‘End the macro.
dEclAring ArrAys WiTH fixEd ElEMEnTs
Early in this lesson you saw this array declaration:
Dim myDays(6) As String