Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 7Understanding 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.

Workbooks

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.

NOTE 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 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, the following command adds a new workbook:

Workbooks.Add

When this code line is executed, the active workbook becomes the new workbook you added, same as the effect of manually adding a new workbook from your existing one, when the workbook you added becomes ...