Working with Collections

A set of similar objects is known as a collection. In Microsoft Excel, for example, all open workbooks belong to the Workbooks collection, and all the sheets in a particular workbook are members of the Worksheets collection. In Microsoft Word, all open documents belong to the Documents collection, and each paragraph in a document is a member of the Paragraphs collection. Collections are objects that contain other objects.

No matter what collection you want to work with, you can do the following:

■ Refer to a specific object in a collection by using an index value. For example, to refer to the second object in the Worksheets collection, use either one of the following statements:

Worksheets(2).Select or


■ Determine the number of items in the collection by using the Count property. For example, when you enter in the Immediate window the statement:


VBA will return the total number of worksheets in the current workbook.

■ Insert new items into the collection by using the Add method. For example, when you enter in the Immediate window the statement:


VBA will insert in the current workbook a new worksheet. The Worksheets collection now contains one more item.

■ Cycle through every object in the collection by using the For Each... Next loop.

Suppose that you opened a workbook containing five worksheets with the following names: "Daily wages," "Weekly wages," "Bonuses," "Yearly salary," and "Monthly wages." Use this procedure to delete the worksheets that contain the word "wages" in the name:

Sub DeleteSheets() Dim ws As Worksheet Application.DisplayAlerts = False For Each ws In Worksheets

If InStr(ws.Name, "wages") Then ws.Delete End If

Next End Sub

While writing your own VBA procedures, you may come across a situation where there's no built-in collection to handle the task at hand. The solution is to create a custom collection. From Chapter 7, you already know how to work with multiple items of data by using dynamic or static arrays. Because collections have built-in properties and methods that allow you to add, remove, and count their elements, it's much easier to work with collections than arrays.

0 0

Post a comment