The Base of a Collection

Note that collections can be either 0-based or 1-based. In a 0-based collection, the first member has index 0, and in a 1-based collection, the first member has index 1. Most, but not all, collections in the Excel object model and in VBA itself are 1-based. However, some older collections tend to be 0-based. (I guess that Microsoft got a lot of complaints about 0-based collections so they decided to switch.)

It is important to determine the base of any collection before trying to access members by index. This can be done by checking the help system (sometimes) or trying some sample code. For instance, the code:

For i = 1 To Selection.Cells.Count

Debug.Print Selection.Cells(i).Value Next i is correct, since the Cells collection is 1-based. However, the UserForms collection, which represents all currently loaded user forms in Excel, is 0-based, so the code:

For i = 1 To UserForms.Count

Debug.Print UserForms(i).Name Next i will produce an error. The correct code is:

For i = 0 To UserForms.Count - 1 Debug.Print UserForms(i).Name Next i

(Note that this reports the number of loaded forms in the project.)

0 0

Post a comment