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

The Object Hierarchy – Connecting the Dots

What do the Dots Mean in VBA

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.

Excel Object Hiearchy - Intro to VBA

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.

Workbooks Member of Application Object - Intro to VBA

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.

Worksheets Member of Workbook Object - Intro to VBA


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.

Object Hiearchy in the House

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

Can you turn the lights on

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

Fully Qualified Line of Code in VBA

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.


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.


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


Your email address will not be published. Required fields are marked *

  • Where can I find the hierarchy which will show me the relations of the classes. For example, where is it documented that the Worksheets object is under the Workbooks object which is under the Applications Object, and where can I find the methods and properties of a given object? Surely all this must be documented somewhere?

    • Hi Dan,

      Great question! You can see it in the Object Browser by pressing F2 in the VB Editor. However, I find this library difficult to navigate.

      Another way is on the MSDN help pages. With the text cursor in an object, property, or method reference in the code module, press F1 on the keyboard to open the MSDN help page for the reference.

      For example, here is the help page for the worksheets property. You can see a list of it’s members (properties and methods) on the left menu of the page.

      I hope that helps. Thanks Dan!

      • Hi Jon,

        Yes, your reply was a big help.

        I am, however, still a bit confused about the relation between a WORKSHEETS object and a WORKSHEET object. I would have thought that WORKSHEET would be a ‘child’ of WORKSHEETS, but when I go to the MS doc you referred me to I see no such relation. How come?


        • Hi Dan,
          Great question! This can definitely be a bit confusing. Worksheets is a property that returns a collection of all the worksheets in the specified workbook. The Worksheets property contains an Index argument that allows you to reference the worksheet number (order in the workbook) or worksheet name.


          When we reference a single sheet like this with the Worksheets property, it is going to return a Worksheet object. So the Worksheet Object is a member of the Worksheets property (collection). Here is the help page for the Worksheet object that explains it.

          I also should have given you the link to the Workbook.Worksheets property. Here is the link to that as well.

          I hope that helps. Let me know if you have questions.

          • Jon,

            Thanks very much for your very clear explanation. Your discussion was so much better than any of the Microsoft documentation had to offer and I appreciate your time to do so. Your website will definitely be added to my bookmarks!

            All the best,

  • Hello, I read this second article with the same enthousiasme than the 1st one,

    thanks a lot for these explanations! Really Helpful !

  • Hello I’m using the code “Application.ActivePrinter = “HP06B424 (HP ENVY 7640 series) on Ne01:” but instead of changing the default printer, I get Error message: 1004 Methode ActivePrinter of object _Application failed.

    Do not know what is wrong about the code. I am running on a Windows 10 Desktop PC with all the latest update’s and I am using Office 365.

    Can you please help or gif a hint. I need to set a specific printer before I can make a printout of labels.

    The Mentioned HP Printer is just for testing, eventually it will be a “Dymo LabelWriter 450”.

    Kind regards,
    Eddy Exmann,

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter