Читать книгу Excel VBA Programming For Dummies - Dick Kusleika - Страница 72

Now you can work with the Application object and any object that’s just below it via one of its properties. To get another level below that you need collection objects.

Collection objects are objects that give you access to single objects contained inside them. In many cases, the collection object’s name is the plural of the single object’s name. For example, if you want to work with a specific Workbook object, you need to go through the Workbooks collection object to get there.

Here are a few examples of commonly used collections:

 Workbooks: A collection of all currently open Workbook objects

 Worksheets: A collection of all Worksheet objects contained in a particular Workbook object

 Charts: A collection of all Chart objects (chart sheets) contained in a particular Workbook object

 Sheets: A collection of all sheets (regardless of their type) contained in a particular Workbook object. To get to a Workbook object, you can start with the Application object like in the following code: Application.Workbooks.Item("Book1").Path

Just like Application had an AutoCorrect property to get to the AutoCorrect object, it has a Workbooks property to get to the Workbooks collection object. All collection objects have an Item property to get to a singular instance of an object they collect. In this example, the Item property returns a Workbook object because that’s the type of object in the Workbook collection.

A collection’s Item property takes one argument. That argument can be a string (enclosed in double quotes) or an integer. If you provide a string argument, Item returns an object whose name matches the string. In the example above, Book1 is the name of the workbook returned. If you provide an integer argument, Item returns the object whose position in the collection matches that number. The following code returns the first worksheet of the second workbook:

Application.Workbooks.Item(2).Worksheets.Item(1)

You might not always know how the objects are ordered in the collection. With worksheets, they are ordered the same way as in Excel. The left-most worksheet is the first worksheet in the collection. But workbooks aren’t so straightforward. There are hidden workbooks like Add-Ins and the Personal Macro Workbook that are in the collection but not visible in Excel.