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.

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

Get Excel VBA 24-Hour Trainer, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.