If you've read any of the other macro or VBA tutorials in Power Spreadsheets, you've probably noticed that some terms keep popping up over and over.
One of the concepts that keep coming up, and will continue to come up in future tutorials, is that of objects. The main reason for this is simple:
VBA is (loosely) based on Object Oriented Programming. At a basic level, this (roughly) means that the VBA paradigm mostly relies on working with (or manipulates) objects.
As a consequence of the above, if you want to really master Excel macros and Visual Basic for Applications, you must have a good understanding of the following 3 topics:
My 2 main purposes when writing this VBA tutorial are to:
More precisely, in this macro tutorial I explain the following topics:
Table of Contents
I'll say from the start that the topics of Excel's VBA object model and building VBA object references are not particularly simple. However…
Your knowledge and understanding of Excel's VBA object model and object references will improve as you continue to study, and work with, Visual Basic for Applications. Therefore, don't worry if, after reading this VBA tutorial things are not absolutely clear. This guide should provide you with a solid base and, with some work I'm sure you'll master this topic and know all you need about Excel VBA objects.
Let's begin by answering the first question that you probably have regarding the introduction I've made above by understanding…
Visual Basic for Applications is included in most products that are part of Microsoft Office. In addition to Excel, the list of applications that have VBA includes PowerPoint, Word and Access.
This underscores one of the great advantaged of learning VBA:
Once you know Visual Basic for Applications, you can immediately start writing macros for the other products that use VBA. In fact, you'll be able to create macros that work across all of those different applications.
One of the main topics you need to master in order to reach those levels of expertise is objects. At a basic level, VBA manipulates objects.
Each individual Application that works with VBA (for example, Excel, Word, PowerPoint, Outlook) has its own unique object model. Having a good understanding of the principles behind objects and object models helps you work with VBA in these different Applications.
OK. So Excel's VBA object model is clearly important. The next question you may have is…
At a basic level, the Excel VBA Object Model is a hierarchy of the objects you can use when working with Excel VBA.
Among other advantages, this hierarchy makes VBA objects easier to reference. Therefore, let's take a closer look at…
An object hierarchy looks as follows:
When you're working with a particular software application, the first object to consider is the application itself (the Application object). Generally, the application is at the top of the hierarchy.
In the case of Excel, the Application object is Excel itself.
Since Visual Basic for Applications can communicate with other applications and programs beyond Excel, this isn't strictly speaking the top level of the hierarchy. However, you'll usually see most people referring to the Application object itself as being the top of Excel's VBA object hierarchy. That's the convention I use in this macro tutorial.
The Application object contains other VBA objects. Some of the VBA objects contained by the Excel Application object are the following:
Each of these VBA objects, in turn, is capable of containing other objects. For example, some of the VBA objects that can be contained within a Workbook object are the following:
Again, these VBA objects can contain other objects. Continuing with the example above, a Worksheet object can contain the following VBA objects:
Graphically, the portion of Excel's VBA object hierarchy described above looks roughly as follows:
The image above illustrates only a very small portion of Excel's VBA object hierarchy. The Excel Object Model has a very large number of objects. A full diagram of Excel's VBA object hierarchy exceeds the scope of this Excel VBA Object Model Tutorial.
What can you do about this?
You can definitely master Visual Basic for Applications despite the huge amount of Excel VBA objects. There are several reasons for this, including the following:
Additionally, as you continue working with Visual Basic for Applications, you'll start noticing the logic behind the structure of the Excel VBA object hierarchy.
Collections are defined by 2 main characteristics:
In other words, collections are VBA objects that are used to group and manage other objects (which are related).
The fact you can group and manage several VBA objects by using collections is extremely useful in some situations. Imagine, for example, that you want to do something with or to a particular group of objects. If all of these objects are part of the same collection, you can structure your VBA code to go through each of the members of the collection and carry out the desired actions. As you can imagine, this structure is simpler than, for example, having to list each of the collection members individually.
In other words, collections allow you to work with a complete group of VBA objects at the same time, instead of working with each single object.
The following are examples of common collections:
In fact, if you go back up to the explanation of Excel's VBA object hierarchy, you'll find several other examples of collections. Basically, any VBA object which is listed there as containing other objects is a collection.
By now you probably have a firm grasp of what an object and a collection are. So let's get into the actual practice. Let's look at how you can start referencing VBA objects with Visual Basic for Applications:
Knowing how to refer to objects when writing VBA code is essential. The reason for this is that, obviously, when you want to start working with a particular VBA object, you must identify it.
The question is, how do you do it? How do you refer to an object in Visual Basic for Applications?
Let's take a look at some of the most common and basic situations. The purpose of this section is to serve as an introduction to VBA object references. There are many other more advanced cases. For example, I explain several ways to refer to VBA's Range object in Excel VBA Object Model And Object References: The Essential Guide which you can find in the Archives.
Let's start by taking a look at how to refer to an object by going through the whole hierarchy of Excel VBA objects. This is known as a fully qualified reference because you tell Excel exactly what VBA object you want to work with by referencing all of its parents.
As I explain in the following sections, you can usually simplify fully qualified references. However, you must learn how fully qualified references work. They are the basis of VBA object references and, in practice, you'll use them most of the time. Additionally, they're quite useful for purposes of understanding better the VBA code behind your macros.
You already know that the object at the top of the Excel VBA object hierarchy is Application. Referring to this object is very easy. In the Visual Basic Editor, you refer to Application by typing:
Application
From there on, you need to start moving along the hierarchy by using the dot (.) operator. In other words, you connect each VBA object to the previous one (the object parent) by using a dot (.). Those dots (.) are used to connect and reference members of Excel's VBA object model from the top down.
To see this in practice, let's go back to the example of the Excel VBA object hierarchy that I display above. Assume that you want to refer to a Range object. As shown in the graph displayed below, this object is at the bottom of the pyramid used in the example. There are 2 VBA objects and 3 steps between the Application and the Range object, as shown by the image below:
You already know that you simply need to connect the different objects with a dot (.) while you're going down the Excel VBA object hierarchy. In other words, you know that, in very general terms, you can refer to a Range object using the following basic structure:
Application.Workbooks.Worksheets.Range
This is, however, just a basic framework. You'll notice that this very basic structure is not actually identifying an individual VBA object. You may be wondering:
These questions can be summarized by the following:
How do you refer to a particular object within a collection?
Let's answer this question so that you can complete the fully qualified reference above.
It is likely that, most of the time, you'll be working with a particular VBA object from a collection. This is in contrast with the collection as a whole.
Note that you can also work with a collection as a whole. In fact, the ability to do this is one of the advantages of collections.
However, let's focus for now on how you can reference an object from a collection. For these purposes, you can use either of the following 2 options:
Option #1: Using The VBA Object Name.
In this case, the syntax that you must use to refer to an object is “Collection_name(“Object_name”)”. In other words:
For example, if you're working with an Excel Workbook that has 3 worksheets and you want to work with Sheet1, you can use either of the following:
Worksheets("Sheet1")
Sheets("Sheet1")
Option #2: Using Index Number.
If you choose to use this option, you refer to a VBA object using “Collection_name(Index_number)”. This structure is substantially the same as that above with the following 2 differences:
Going back to the example above, where you're want to work with Sheet1, you can use either of the following 2 options:
Worksheets(1)
Sheets(1)
Now that you know how to refer to an individual VBA object within a collection, let's go back to the fully qualified reference that I used as an example in the section above:
Application.Workbooks.Worksheets.Range
How can you complete this, assuming that the object you want to work with is cell A1 from Worksheet Sheet1 within Workbook Book1?
If you're using the object name to refer to each of the individual VBA objects (option #1 above), the fully qualified reference for this cell is:
Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1")
As you may guess, if you had to reference each and every single object using a fully qualified reference, your VBA code would get quite long very fast. From a typing perspective, this may get a little bit annoying. Additionally, and perhaps more importantly, these very long pieces of VBA code can be difficult to read.
There are some ways in which you can simplify object references, making the VBA code much shorter. Let's take a look at some of the methods that you can apply for these purposes…
The ability to simplify a VBA object reference has several advantages. Mainly, this allows you to shorten your VBA code and make it easier to read.
The main reason why you can simplify fully qualified object references is because Excel's VBA object model has some default objects. These default objects are assumed by Excel unless you enter something different. This leads me to a very important point, which is that…
Simplifying fully qualified object references is not without dangers. In particular, the second simplification method described below relies on you correctly identifying the current active Workbook and Worksheet. If you make a mistake by for example, thinking that the current active Worksheet is Sheet1 when in reality its Sheet2, you'll face problems. The most likely issues you'll encounter in these cases are:
Another possible disadvantage of simplifying fully qualified object references is related to execution speed. This happens, for example, if you're working with a particular macro that works with several Excel worksheets. In that case, you have to go through all of them to activate them. Needless to say, this isn't very efficient.
Considering the above, ensure that you're only using these simplification methods when appropriate. Perhaps more importantly, remember that you shouldn't blindly simplify fully qualified references all the time.
In fact, you should probably (as a general rule):
In other words, having a deep knowledge of Excel's VBA object model and using fully qualified references has 2 main advantages:
An alternative to the extremes of fully qualifying references or simplifying them is using With… End With statements. These statements simplify macro syntax by executing several statements which refer to the same VBA object. At the same time, due to their structure, they allow you to maintain fully qualified object references.
You can see a very simple example of a With…End With statement in this macro that deletes rows based on whether a cell in a given range is blank.
With the warning above in mind, let's take a look at the methods you can use to simplify fully qualified object references:
Simplification #1: The Application Object.
The main default VBA object is the Application object. As a general rule:
When creating macros, it is assumed that you'll be working with Excel. In other words, Excel assumes that you're working with the Application object. Therefore, as you may expect, you can generally omit this Excel VBA object from your object references.
Explicitly referring to (entering) the Application object makes sense in only a few cases.
Applying this shortcut to the statement referring to cell A1 in Sheet1 within Book1 that has been used as an example simplifies the reference as follows:
Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1")
Simplification #2: The Active Workbook and Worksheet.
The second group of default objects you can use to simplify fully qualified object references applies when you're working inside a standard module. Within the Visual Basic Editor, you can usually find standard modules in the Project Window under the Modules node:
In these cases, in addition to assuming that you're working with the Application object, Excel also assumes that you're working with the active Workbook.
Therefore, if you know that the current active Excel workbook is the Workbook you want to work with, you can omit that part of the VBA object reference. Continuing with the example above, the statement can then be shortened to the following:
Worksheets("Sheet1").Range("A1")
Finally, if you're sure that the Excel worksheet you want to work with is the current active Worksheet, you can also omit that part of the VBA object reference. The statement above can then be shortened even further:
Range("A1")
In addition to the dangers of using this simplification that I explain at the beginning of this section, there is a further aspect you must consider. The 2 assumptions that I've listed in Simplification #2 above only work as long as you're in a standard module. Therefore, you must avoid relying on these assumptions when working in another type of module. For example:
Excel's VBA object model is extremely important. You can't ignore this topic if you really want to become a master in Excel macros and Visual Basic for Applications.
Excel's VBA object model is not the simplest topic to understand but, if you practice and study, you'll eventually master the topic. Then, you'll be on your way to creating powerful macros that increase your productivity and efficiency when working with Excel.
If you've studied this particular VBA tutorial, you not only have a good understanding of what is Excel's VBA object model, but also know how to start building object references in Visual Basic for Applications. This ability to create appropriate VBA object references is what allows you to tell Excel which object you want to work with and manipulate. This is an essential skill that you now have in your VBA knowledge-box.
Due to the complexity and extensiveness of Excel's VBA object model, this is a topic that we're all constantly studying and learning about.
