Intro to VBA: The Excel Object Hierarchy (Part 2 of 3)

The Object Hierarchy – Connecting the Dots

The VBA objects are organized in a hierarchy, which makes them easier to reference. At the top of this hierarchy is the Excel Application. All the objects within Excel are members or sub-members of the Application object.

Here is a fully qualified line of code that starts at the application level.

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value = 100

The dots or periods between each word allow us to reference members of the hierarchy from top down. So the Application is the top level of the hierarchy, and the Workbook is a member of the Application.  The Workbooks() property returns a collection of all the open workbooks on the computer.

We reference the workbook name in the Workbooks property to return a workbook object.  It might sound confusing, but it basically allows us to tell VBA which object we are referring to.

Below that we are referencing the Worksheets property which will return a collection of all the worksheets in the workbook. The worksheets are a member of the Workbook object.

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

Again, we are specifying the worksheet name “Sheet1” in the Worksheets property to return a worksheet object.

You can see how we are working our way down to the Range object and the value property of cell A1.

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value = 100

It's important to note that each object has many different members that we can use to reference various objects. For example, members of the Worksheet object can include: Ranges, PivotTables, Shapes, Charts, ListObjects, etc.

As an example, think about all the objects in your kitchen. There are probably dozens if not hundreds of objects that all have properties and can perform actions. These objects might be members of larger collections.

The Chef Knife is a member of a collection of all the kitchen tools you own. Each of these tools has different properties and can perform different actions.  Our object hierarchy for the knife in the kitchen might look like the following.

House.Rooms("Kitchen").Areas("Knife Block").Tools("Chef Knife").Clean = True

You can see how we are starting at the house and zooming in to locate the knife, then setting the property of it.

Qualifying Objects in VBA

If you are married or live with a significant other, then you are already know a lot about qualifying objects.

The other day my wife and I were in the kitchen and she said, “can you turn the light on, please?”

I said “sure!”, and turned on the kitchen light.

I made some assumptions about which light she was referring to because we were both in the kitchen, and our kitchen only has one light.

She didn't need to say, “can you please turn the light on in the kitchen that is over the sink and controlled by the wall switch?” The environment we were both in provided the context, and she was able to shorten her question.

VBA also allows us to make these assumptions when referencing objects. If you don't specify the workbook or worksheet in a line of code, then VBA assumes you are referring to the active workbook and active worksheet.

This allows us to shorten our code, making it easier to read and write.

1. Fully Qualified – Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1").Value = 100
2. Abbreviated (assumes ActiveSheet and ActiveWorkbook)  – Range("A1").Value = 100

The first line of code is considered to be “fully qualified” because it references all the parent members of the object being evaluated.

The second line of code above would do the same as the first, IF the active workbook was Book1 and the active sheet was Sheet1.

Although shortening our code can make our lives easier, it can also get us in trouble!

Assumptions Can Get You In Trouble & Cause Errors!

A few days ago I was in the bedroom, and my wife called out from the kitchen, “Hey can I throw this away???” Now even though I had just walked out of the kitchen, I didn't know what she was referring to. Has this ever happened to you?

More importantly, has this ever lead to an argument or rude response?  I know I'm guilty! Well, I didn't understand her question because I didn't have context. Sometimes we have such close relationships that we think our significant other can read our mind and see through our eyes, and we make assumptions that can turn into misunderstandings. Bless my wife for believing I'm smarter than I really am… 🙂

VBA has the exact same problems with misunderstandings. They cause errors, and can be dangerous! You have to be careful with how you shortcut your questions and commands.

For example, the following line of code will clear the values and formulas of all the cells on the active worksheet.

Cells.ClearContents

If you don't tell VBA which sheet in which workbook you want to clear, then this could spell disaster! You can't undo that action.

So you would want to qualify this line of code to tell VBA which workbook and worksheet you are referring to.

Workbooks("Book1.xlsx").Worksheets("Sheet1").Cells.ClearContents

This line of code prevents any misunderstandings and potential disasters.

What's Next?

In the next article we will look at some tips for reading, writing, and running your code.  We also look at some ways to improve the efficiency of your code.

Click here to go to Part 3  – Reading, Writing, & Running VBA Code >

Previous article – Part 1 – Programming Objects with VBA