Many times, in Visual Basic when you iterate through zero-based
indexed collections, the language forces you to subtract one from
the final count. For instance, if you wanted to loop through all
the fields in an ADO recordset, you might use code similar to:
For x = 0 to Recset.Fields.Count -1
Debug.Print Recset.Fields(x)
Next x
The reason you must add the -1 portion to the loop is because while
a record may contain 6 fields, ADO indexes them from 0 to 5. The
Count property, on the other hand, would return 6. However, if
you tried to use 6 as an index number, Visual Basic would generate
an error. (Of course, to avoid this completely, you could use a
For Each...Next statement instead, but you get the point we're
trying to make.)
As a result of this very common accommodation that developers must
make for zero-based indexes, it's only natural that many people
operate under the mistaken assumption that UBound() works in a
similar fashion--that is, it returns the total number of items
in an array, and therefore you must subtract 1 from this number
when looping through an array, like so:
For x = LBound(myArray) to UBound(myArray) -1
Debug.Print myArray(x)
Next x
In fact, the UBound() function returns the maximum subscript (or
index) available to the array, not the total number of items. For
instance, the following code snippet:
Dim Array1(0 to 7) As String
Dim Array2(1 to 7) As String
Dim Array3(5 to 7) As String
Debug.Print UBound(Array1)
Debug.Print UBound(Array2)
Debug.Print UBound(Array3)
prints 7 in all three cases, even though the number of potential
items contained in each array is 8, 7 and 3, respectively.
Comments