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

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

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.

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.

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

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 10 comments
Paul - August 16, 2017

Cheers Jon! I always find your articles helpful, and required reading in my own job as an analyst. Thanks again

Reply
Duncan Morrison - July 28, 2017

Really helpful, thanks!

Reply
bromberg - June 9, 2016

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?
Thanks,
Dan

Reply
    Jon Acampora - June 18, 2016

    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.

    https://msdn.microsoft.com/en-us/library/office/ff840672.aspx

    I hope that helps. Thanks Dan!

    Reply
      dan - June 19, 2016

      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?

      Thanks,
      Dan

      Reply
        Jon Acampora - June 22, 2016

        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.

        Worksheets(1).Select
        Worksheets("Sheet1").Select
        

        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.

        https://msdn.microsoft.com/en-us/library/office/ff194464.aspx

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

        https://msdn.microsoft.com/en-us/library/office/ff835542.aspx

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

        Reply
          dan - June 26, 2016

          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,
          Dan

          Reply
          Jon Acampora - June 28, 2016

          Awesome! Happy to hear it helped. Thanks Dan!

          Reply
mano - August 21, 2015

Dear Jon
Thankfullllllllllllllllllllllllllllly

Reply

Leave a Reply:

Free Webinar: The 5 Secrets to Understanding Pivot Tables

x