Collection objects in VBA are fairly straightforward—they are exactly what the name implies: a group or collection of the same object types. Referring to the bicycle example again, consider a collection of bicycles. The bicycle objects in your bicycle collection can be different sizes, colors, and types, but they are all bicycles.
Collection objects allow you to work with objects as a group rather than just working with a single object. In VBA, collection objects are typically denoted with the plural form of the object types that can belong to a collection (not all can). For example, any Workbook object belongs to a Workbooks collection object. The Workbooks collection object contains all open Workbook objects. The Excel window shown in Figure 5.2 contains three open Workbook objects (Book1, Book2, and Book3).
Excel Workbook objects.
Excel Workbook objects.
To select a Workbook object from the Workbooks collection object, the code would look like this:
This line of code uses the Workbooks property of the Application object (more on this later) to return a single Workbook object from the Workbooks collection object and then uses the Activate() method of the Workbook object to select the desired object.
Hijiir The required syntax when addressing objects in VBA is object.property or object .method. You may also specify multiple properties in order to reach the desired property or method. For example, Application.ActiveSheet . RangeC'Al"). Font.Bol d = True is of the form object, property, property .property .property because ActiveSheet, Range("A1"), and Font all represent properties that return objects. Bold is a Boolean property of the Font object and its value is set to true. As you may have guessed, this line of code turns on bold formatting in cell Al of the current worksheet.
So, from the collection of Workbook objects shown in Figure 5.2, which Workbook object does the previously mentioned line of code return? If you answered Book2, you'd be wrong, although that is the intuitive answer. The number in parentheses refers to a relative index number for each Workbook object as it was created. (In this case, Bookl was created first, Book2 second, and Book3 third.) The confusing part is that an index value of 1 is reserved for the currently selected Workbook object, regardless of when that Workbook object was created. So to select Book2 you would actually have to use an index value of 3 in the above line of code. An index value of 2 would return Bookl and an index value of 1 or 4 would return Book3.
There will always be two choices of an index for the currently selected Workbook object, the value 1 because it is reserved for the currently selected object, and the value corresponding to its sequence in being created. The behavior of the Workbooks collection object can be confusing, but with practice, patience, and above all, testing, I'm sure you can figure it out.
To avoid confusion, you can select a workbook unambiguously—if you know the name of the desired Workbook object—using the following line of code.
Here you simply include the name of the object as a string in place of the index number. Obviously, this is much less confusing and makes your code easier to read, so I recommend doing it this way whenever possible.
When you need to step through several objects in a collection, use a loop and a looping variable to represent the index of the object to be returned.
If Workbooks(I).Saved Then Workbooks(I).Close
Other examples of collection objects include Worksheets, Windows, and Charts. For example, each of the Workbook objects in Figure 5.2 contains three Worksheet objects that belong to separate Worksheets collection objects. There are three Worksheets collection objects in this example because they are lower in the object hierarchy than the Workbook object.
Was this article helpful?