Microsoft Office Tutorials and References
In Depth Information
Lesson 7: Understanding Objects and Collections
Lesson 5 introduced the topic of collections , which are objects that contain a group of like
objects. This lesson adds some detail to the topic and goes over some programming techniques
to deal with the most common types of object collections you will encounter: workbooks,
worksheets, cells, and ranges.
An Excel file is a Workbook object. You might wonder how workbooks have a collection,
seeing as you can only work in one workbook at a time, and even then you are usually
manipulating objects at a lower level, such as worksheets or cells.
Do not confuse the Application object with the Workbook object. In VBA, the
Application object is at the very top of the food chain; there is nothing higher
than Application in the Excel object model. Application represents the entire
Excel program, whereas Workbook represents an individual Excel file.
The Workbooks collection contains the references to every Workbook object that is open in the
same instance of Excel. You will need to call upon the Workbooks collection when you want
to do some task in every open workbook, or when you want to activate a particular workbook
whose name is not known.
Here is an example. In VBA, this will add a new workbook: