About collections

Another key concept in VBA programming is collections. A collection is a group of objects of the same class, and a collection is itself an object. As I note earlier, Workbooks is a collection of all Workbook objects currently open. Worksheets is a collection of all Worksheet objects contained in a particular Workbook object. You can work with an entire collection of objects or with an individual object in a collection. To reference a single object from a collection, you put the object's name or index number in parentheses after the name of the collection, like this:

Worksheets("Sheet1")

If Sheet1 is the first worksheet in the collection, you could also use the following reference:

Worksheets(1)

You refer to the second worksheet in a Workbook as Worksheets(2), and so on.

There is also a collection called Sheets, which is made up of all sheets in a workbook, whether they're worksheets or chart sheets. If Sheet1 is the first sheet in the workbook, you can reference it as follows:

Sheets(1)

0 0

Post a comment