A good indicator as to whether a collection exists is to look in the Object Browser (F2 function key) and scroll down the Classes section to view any class file which is a plural. This will more than likely be a collection of the singular named class i.e. Workbooks and Workbook.

There are many others types of collections – refer to Excel VBA help for more information.

Example1:

'Working through the active workbook and identifying all worksheets
Sub HowManyWorksheets()
    Dim w As Worksheet
    For Each w In Worksheets
        MsgBox w.Name
    Next w
End Sub

The variable w is explicitly declared as a worksheet object. Using a For...Loop statement, we can iterate through each element (w) until the collection is completed.

Using the message box, one of the element’s properties (Name) simply displays each worksheet name.

Even if the variable w is implicit, it would still understand what variable w was because it becomes a member of the collection Worksheets.

Example2:

'This example saves changes to and closes all workbooks except
'the one that's running the example.
Sub CloseWorkbooks()
    Dim w As Workbook
    For Each w In Workbooks
        If w.Name <> ThisWorkbook.Name Then
            w.Close savechanges:=True
        End If
    Next w
End Sub

The above example will close all workbooks in Excel excluding the active workbook and automatically save any changes.

Like arrays, elements in a collection can also be referred to directly as an independent item. For example, to refer to the first worksheet in a workbook:

Sheets(1).Name

Worksheets(1).Name

The array element starts at 1 and increments for each member known to the collection. An error will happen if the element number is not known (a zero or a number higher than the upper bound number).

There are many methods and properties to many objects as the Object Browser has shown. Collections are being used in a lot of situations without the user even being aware.

For example:

Worksheets.Add Count:=2, Before:=Sheets(1)

The Add method and some of its arguments inserts two new worksheets to the collection worksheets and places them before the first element of the worksheets collection.

Next Topic: Message Box

Want to teach yourself Access? Free online guide at About Access Databases

VBA Keywords: MsgBox, For...Each, If...Then, Worksheets, Workbooks, ThisWorkbook, Close, Sheets and Add.