Collections

Many objects belong to collections. A city block is a collection of high-rise buildings. A high-rise building is a collection of floor objects. A floor is a collection of room objects. Collections are objects themselves — objects that contain other objects that are closely related. Collections and objects are often related in a hierarchical or tree structure.

Excel is an object itself, called the Application object. In the Excel Application object, there is a Workbooks collection that contains all the currently open Workbook objects. Each Workbook object has a Worksheets collection that contains the Worksheet objects in that workbook.

Note that you need to make a clear distinction between the plural Worksheets object, which is a collection, and the singular Worksheet object. They are quite different objects.

If you want to refer to a member of a collection, you can refer to it by its position in the collection, as an index number starting with 1, or by its name, as quoted text. If you have opened just one workbook called Data.xls, you can refer to it by either of the following:

Workbooks(l) Workbooks("Data.xls")

If you have three worksheets in the active workbook that have the names North, East, and South, in that order, you can refer to the second worksheet by either of the following:

Worksheets(2) Worksheets("East")

If you want to refer to a worksheet called Datalnput in a workbook called Sales.xls, and Sales.xls is not the active workbook, you must qualify the worksheet reference with the workbook reference, separating them with a period, as follows:

Workbooks("Sales.xls").Worksheets("DataInput")

When you refer to the B2 cell in Datalnput, while another workbook is active, you use:

Workbooks("Sales.xls").Worksheets("DataInput").Range("B2")

The following section examines objects more closely and explains how you can manipulate them in VBA code. You need to be aware of two key characteristics of objects to do this. They are the properties and methods associated with an object.

0 0

Post a comment