Wrapping Your Mind around Collections

Collections are another key concept in VBA programming. A collection is a group of objects of the same type. And to add to the confusion, a collection is itself an object.

Here are a few examples of commonly used collections:

1 Workbooks: A collection of all currently open Workbook objects

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

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

1 Sheets: A collection of all sheets (regardless of their type) contained in a particular Workbook object

You may notice that collection names are all plural, which makes sense (at least I hope).

"What are collections for?" you may rightfully ask. Well, for example they are very useful when you want to do stuff with not just one worksheet, but with a couple of them:

Sub

ChangeTabColor()

Dim Sht As Worksheet

For Each Sht In Worksheets

Sht.Tab.ColorIndex = 3

Next

End

Sub

With a collection it is also very easy to find out how many objects there are of that kind. Worksheets.Count gives you the number of worksheets in the active workbook. You can refer to a member of a collection in two ways:

1 By the index number: Sheet(1). For sheets, this number reflects the relative position starting from the far left.

1 By using the member's name: Sheets ("Sheetl").

0 0

Post a comment