Have you ever written an Excel macro only to discover that it only works by clicking the workbook in a specific manner that is difficult to remember and easily prone to user error? Is your code full of clutter from the Macro Recorder and difficult to read?  Have you ever wanted to do something about it? Would it not be nice to finally be able to get Excel to do EXACTLY what you want it do every time? Well, there is a way – use the Object Model.

[EDIT: If you want to discover the books from which I learned how to do this, check out this post]

This article is about how to use the VBA Object Model to write better Excel Macros. It will explain:

1. what the VBA Object model is
2. what VBA Objects are intuitively
3. an example of the VBA Object Model by doing a task:
   1. without the object model
   2. with the object model
4. what are the advantages of using the VBA Object Model
5. some useful tricks and pitfalls to avoid
6. a quick wrap up

While I have tried to write this article for someone who only has a basic understanding of Excel VBA, it will help if you have some basic knowledge of object oriented programming. If you find the following confusing, I would advise googling terms like “Basic Introduction to Object Oriented Programming” and poking around until you find a site that helps you understand. I will admit that it has taken me many years to get the grasp that I have now.

 

So what is the VBA Object Model?

The VBA Object Model is the hierarchy of all of the programming objects in Excel.

 

Great, what on earth does that mean?

Well, think of every part of Excel that one is used to dealing with as a thing. The workbooks are things. What makes up a workbook? Worksheets mostly, but other things as well such as charts. Each of those worksheets is a thing that helps make up the thing that is the Workbook. One can create variables in Excel VBA that directly refer to these things.

 

An example of the object model

Now that I have a basic explanation out of the way, I want to say that I am a strong believer that in order to understand programming, one should get to simple code examples as soon as possible. I am going to give two very simple examples below that consist of putting the value 3 in the range “A1:C3”, first using traditional Excel VBA and then using the object model.

To follow the example:

1. Create a new workbook
2. Save it as “SampleWB.xlsm” – make sure to set the Save As Type to “Excel Macro-Enabled Workbook (*.xlsm)”
3. Hit Alt-F11 to open VBA
4. Insert a new module
5. Copy and paste the two examples below into the module

 

Without the object model

Please note that you need to click cell “A1” in worksheet “Sheet1” for this example to work.

Sub SimpleObjectExampleNoObj()

ActiveCell.FormulaR1C1 = "3"

Range("A1").Select

Selection.AutoFill Destination:=Range("A1:A3"), Type:=xlFillDefault

Range("A1:A3").Select

Selection.AutoFill Destination:=Range("A1:B3"), Type:=xlFillDefault

Range("A1:B3").Select

End Sub

With the object model

Now, we will try this by declaring every part of the workbook that you will use as an object, including the workbook itself. Please make sure to erase everything in worksheet “Sheet1” before continuing.

 

Sub SimpleObjectExampleWObj()

Dim wbMacro as Workbook

Dim wsSheet1 as Worksheet

Dim rngA1C2 as Range

Set wbMacro = Workbooks("SampleWB.xlsm")

Set wsSheet1 = wbMacro.Worksheets("Sheet1")

Set rngA1C2 = wsSheet1.Range(wsSheet1.Cells(1,1), wsSheet1.Cells(3,2))

rngA1C2.Value = 3

Set rngA1C2 = Nothing

Set wsSheet1 = Nothing

Set wbMacro = Nothing

End Sub

 

Now, what did the second set of code do? It has object references that are then set to point to various objects within Excel

wbMacro points to the Workbook SampleWB.xlsm

wsSheet1 points to the worksheet in Workbook SampleWB.xlsm “Sheet1”

rngA1C2 points to the range in the worksheet “Sheet1” “A1:C2”

 

What are the advantages of this?

The advantages of this are RELIABILITY AND ACCURACY. Using the object model above allows you to say exactly in which workbook you are, exactly in which worksheet in that exact workbook you are, and exactly in which range in that exact worksheet in that exact workbook you are. This allows you to create “click independent” macros. It does not matter what the current active workbook, worksheet, cell, or range is. It does not matter where the user last clicked. The macro will ignore that and do exactly what you want.

 

Pitfalls

Pitfall 1: follow the object hierarchy

When you set object variables equal to objects, you have to do that according to the hierarchy of the objects. In the example above, that hierarchy is the workbook, which contains the worksheet, which contains the range.

1. Workbook “SampleWB.xlsm” (which contains the …)
2. Worksheet “Sheet1” (which contains the …)
3. Range “A1:C2”

Hence, you need to set the workbook variable equal to the workbook object you want first, then the worksheet, and finally the range.

Pitfall 2: You must be as specific as possible, especially with ranges

As well, when one wants to set a range using nothing but the object model, there is a trick of which you must be aware. You would think that the following code would be OK:

Set rngA1C2 = wsSheet1.Range(Cells(1,1),Cells(3,2))

You would be wrong.

Range in this case will not assume Cells refers to the worksheet of which it is a part. Therefore, one needs to indicate which worksheet the cells are as shown here:

Set rngA1C2 = wsSheet1.Range(wsSheet1.Cells(1,1),wsSheet1.Cells(3,2))

 

Conclusion

The VBA Object model allows one to access the full power of VBA and the methods of Excel. In addition, it greatly increases the accuracy and reliability of using VBA in Excel. However, you will need to really think of every “thing” in Excel as an object and all of the menu options in excel as methods that operate on that thing. While this can confuse at first, the reward is worth it.