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.
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:
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 the active workbook.