Collections

Introduction

A 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.  

A few linguistic standards

• 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 Oranges

The 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.

Examples

• 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
  ...
  Next ctr 
• For Each wksht in wkbk.Worksheets
  ...
  Next wksht
• When Added
  Set wkbk = Workbooks.Add
• When Opened 
  Set wkbk = Workbooks.Open(strDirectoryAndFileName)
  'strDirectoryAndFileName is a variable with the workbook's name and location