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:
- what the VBA Object model is
- what VBA Objects are intuitively
- an example of the VBA Object Model by doing a task:
- what are the advantages of using the VBA Object Model
- some useful tricks and pitfalls to avoid
- 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.
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.
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:
- Create a new workbook
- Save it as “SampleWB.xlsm” – make sure to set the Save As Type to “Excel Macro-Enabled Workbook (*.xlsm)”
- Hit Alt-F11 to open VBA
- Insert a new module
- Copy and paste the two examples below into the module
Please note that you need to click cell “A1” in worksheet “Sheet1” for this example to work.
ActiveCell.FormulaR1C1 = "3"
Selection.AutoFill Destination:=Range("A1:A3"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("A1:B3"), Type:=xlFillDefault
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.
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
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”
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.
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.
- Workbook “SampleWB.xlsm” (which contains the …)
- Worksheet “Sheet1” (which contains the …)
- 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))
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.