IntroductionA recurring structure of the Microsoft object model is the Collection object.
- A Collection is an "bag" or "box" holding all of the objects of a certain type within a specified scope.
- A Collection is homogeneous, meaning each can contain only one type of object.
- The name of an object type has its first letter capitalized; e.g. Worksheet, Shapes
- "Collection" is an type of object.
- The name of a collection is a plural ", e.g. Worksheets, Shapes
Comparing Apples and OrangesThe Apples Collection would contain only fruits of the type Apple.
The Oranges Collection would include only the fruits of the type Orange.
The Fruits collection might contain both apples and oranges, as long as they are defined as Fruit objects.
- Workbooks: Workbooks currently open
- Windows: Worksheets of a Workbook
- Rows: Rows of a Worksheet
- Cells of a Range
- Names in a Workbook
- Shapes on a Worksheet
How to access objects in collections
- By Name
Set wkbkInput = Workbooks(strFileName)
- By Number. Not very useful by itself, but see "Scrolling" (below).
Set wkbk = Workbooks(1)
- "Scrolling" through the collection.
An advantage to scrolling through is the ability to use criteria other than the object's Name, which is not always the default value for the collection's objects.
- For ctr = 1 to Workbooks.Count
- For Each wksht in wkbk.Worksheets
- When Added
Set wkbk = Workbooks.Add
- When Opened
Set wkbk = Workbooks.Open(strDirectoryAndFileName)
'strDirectoryAndFileName is a variable with the workbook's name and location