Understanding and identifying objects in VBA

In this recipe, we will learn how to identify objects. Whether you record a macro or manually enter code, you will be working with objects. In essence, the Excel object model is a hierarchy of objects contained in Excel. Each object has certain properties and can be manipulated to perform certain actions in Excel.

Once you understand this hierarchical structure, you will have a good understanding of object-oriented programming (OOP).

Getting ready

As long as you have Excel installed on your system, you have everything you need. You may not have been aware of it, but every time you've used Excel in the past, you've been using objects.

How to do it…

The steps for this recipe are as follows:

1. Open a blank workbook in Excel. The first object we're looking at here is the application itself. That's the familiar Excel interface we deal with every time we work in Excel.
2. Next, contained in Excel, the main object, there are other objects such as workbooks. The default workbook will be Book1, and every new workbook after that – Book2, Book3, and so on – are all separate objects.
3. Each workbook, in turn, contains its own set of objects such as worksheets. By inserting new worksheets into workbooks, the number of objects increases accordingly.
4. On a lower level, worksheets also contain objects such as names, comments, and ranges.

How it works…

The best way to explain the hierarchical structure of objects is with a diagram, as follows:

Figure 3.1 – The object hierarchy

In this scenario, the application is the main object on the left. It contains three Book objects, while each of the Book objects contains three Sheet objects. Sheet1 of Book1 contains a range object with a specific value.

If we had to refer to these objects in VBA, the syntax used would be vitally important. Similar to Figure 3.1, where the Application object is on the left, followed by the rest of the objects, typing the code also flows from left to right.

If we had to type code to describe the objects in Figure 3.1, it would be done as follows:

Application.Workbooks("Book1.xlsx").Worksheets(1).  _

Range("A1").Value

This code example is known as a fully qualified reference of the preceding diagram. The syntax dictates that each object is separated from the next by a dot (.).

Notice that the book name is in quotation marks. This is necessary for Excel to identify it as an object, instead of a variable name. For the worksheet, the number is not in quotation marks. That is because we refer to the object by its index number. The range name is in quotation marks again, and the value is just the value.

A simplified object reference for the same object would be the following:

Range("A1").Value

The reason for this much simpler reference is because the application object is always assumed. Further, if you're sure that Book1.xlsx is the active workbook, you can omit that too. Similarly, if you know that Sheet1 is the currently active worksheet, Excel will assume that reference.

Note that Excel does not have a cell object. A cell is simply a range object consisting of a single element.