About Objects and Collections

If you've worked through the first part of this chapter, you have an overview of VBA and you know the basics of working with VBA modules in the VBE. You've also seen some VBA code and were exposed to concepts like objects and properties. This section gives you some additional details about objects and collections of objects.

When you work with VBA, you must understand the concept of objects and Excel's object model. It helps to think of objects in terms of a hierarchy. At the top of this model is the Application object - in this case, Excel itself. But if you're programming in VBA with Microsoft Word, the Application object is Word.

The object hierarchy

The Application object (that is, Excel) contains other objects. Here are a few examples of objects contained in the Application object:

• Workbooks (a collection of all Workbook objects)

• Windows (a collection of all Window objects)

• AddIns (a collection of all AddIn objects)

Some objects can contain other objects. For example, the Workbooks collection consists of all open Workbook objects, and a Workbook object contains other objects, a few of which are as follows :

• Worksheets (a collection of Worksheet objects)

• Charts (a collection of Chart objects)

• Names (a collection of Name objects)

Each of these objects, in turn , can contain other objects. The Worksheets collection consists of all Worksheet objects in a Workbook . A Worksheet object contains many other objects, which include the following:

• ChartObjects (a collection of ChartObject objects)

• Range

• PageSetup

• PivotTables (a collection of PivotTable objects)

If this seems confusing, trust me, it will make sense, and you'll eventually realize that this object hierarchy setup is quite logical and well structured. By the way, the complete Excel object model is covered in the Help system.

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) 

Referring to objects

When you refer to an object using VBA, you often must qualify the object by connecting object names with a period (also known as a dot operator ). What if you had two workbooks open and they both had a worksheet named Sheet1 ? The solution is to qualify the reference by adding the object's container, like this:

 Workbooks("Book1").Worksheets("Sheet1") 

Without the workbook qualifier, VBA would look for Sheet1 in the active workbook.

To refer to a specific range (such as cell A1) on a worksheet named Sheet1 in a workbook named Book1 , you can use the following expression:

 Workbooks("Book1").Worksheets("Sheet1").Range("A1") 

The fully qualified reference for the preceding example also includes the Application object, as follows:

 Application.Workbooks("Book1").Worksheets("Sheet1").Range("A1") 

Most of the time, however, you can omit the Application object in your references; it is assumed. If the Book1 object is the active workbook, you can even omit that object reference and use this:

 Worksheets("Sheet1").Range("A1") 

And - I think you know where I'm going with this - if Sheet1 is the active worksheet, you can use an even simpler expression:

 Range("A1") 

Note  

Contrary to what you might expect, Excel does not have an object that refers to an individual cell that is called Cell . A single cell is simply a Range object that happens to consist of just one element.

Simply referring to objects (as in these examples) doesn't do anything. To perform anything meaningful, you must read or modify an object's properties or else specify a method to be used with an object.