Maybe SkyNet Will Be Written In Excel VBA After All … OR … Why ThisWorkbook Can Save You Headaches With Your Macros

In my last post
, I joked that SkyNet from The Terminator would not be written in Excel VBA because you cannot make Excel VBA code aware that it exists within a particular workbook.

Turns out that I may have been wrong about SkyNet and that I was very wrong about needing to create a workbook object to ensure your code looks at the workbook in which it exists.

There is a way to force your macros to look at the workbook in which they exist.

You use the ThisWorkbook property.

OK, Give Me An Example

Of course. For the example below, create a workbook called “WhyThisWorkbookIsOKWithMacroCode.xlsm” and place the code below in a VBA module.

Sub NotSoConfusingThisWorkbook()

Dim whatYouThinkIsActiveWorkbook As Workbook
Dim whatActuallyIsActiveWorkbook As Workbook

Set whatYouThinkIsActiveWorkbook = Workbooks("WhyThisWorkbookIsOKWithMacroCode.xlsm")
Set whatActuallyIsActiveWorkbook = Workbooks.Add

whatYouThinkIsActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) = "this is what you think you will see"
whatActuallyIsActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) = "this is what you actually see since activeWorkbook confused you"

Debug.Print ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1)

' show how ThisWorkbook works
Debug.Print "Now Let's try it with ThisWorkbook"
Debug.Print ThisWorkbook.Worksheets("Sheet1").Cells(1,1)

Set whatYouThinkIsActiveWorkbook = Nothing
Set whatActuallyIsActiveWorkbook = Nothing

End Sub

As you can see after running this code, while ActiveWorkbook looks at the last activated or clicked workbook, ThisWorkbook refers back to the workbook in which the VBA Macro code exists.

SkyNet did not become self aware because it was written in Excel VBA … OR … Why you should almost never use ActiveWorkbook and ActiveSheet

NOTE: I wrote this without knowing about “ThisWorkbook”, which solves the workbook but not the worksheet problem. You can read about my discovery here.

“The SkyNet Funding Bill is passed. The system goes on-line August 4th, 1997. Human decisions are removed from strategic defense. SkyNet begins to learn at a geometric rate. It becomes self-aware at 2:14 a.m. Eastern time, August 29th” – The Terminator

“Three billion human lives ended on August 29th, 1997. The survivors of the nuclear fire called the war Judgment Day.” – Sarah Connor

Fortunately, this did not come to pass thanks to the Federal Government insisting that SkyNet be written in VBA since “They like working in Excel”.

While there is endless debate on whether artificial intelligence could become self-aware and a threat to humanity and while many great films (and even more awful ones) have been made about this, I am here to say that you will never have to worry about this happening with code written in Excel VBA.

OK, maybe I am exaggerating a bit, but one very aggravating thing that I have had to deal with Excel VBA is that the VBA code does not understand that it exists within a workbook. You have to explicitly tell it constantly. I will often have a public variable:

Public Const pubConstMacroWBName As String = "TheMacroWorkbook.xlsm"

That I refer too in most of my functions and procedures as:

Set wbMacro = Worksbooks(pubConstMacroWBName )

In order to ensure that the code knows which workbook the code actually exists.

The Difficulties of using ActiveWorkbook and ActiveSheet

When one is just starting out with the VBA Object Model, it is very tempting to use the following two properties

  1. ActiveWorkbook
  2. ActiveSheet

These two properties illustrate Excel VBA’s lack of self-awareness well. Using them tends to come back to haunt people, since these two objects are very unreliable. There value changes depending on whatever workbook and worksheet was last clicked or had the select/activate method used on it, which can cause code to not work as intended.

ActiveWorkbook is not the workbook where the excel VBA code is. It’s the last one clicked or had the activate method used on it with VBA.

ActiveSheet is the sheet you last clicked on or last used the select method on, not where VBA code located in a worksheet module resides. If you used it a worksheet module it can also cause problems.

These two facts provide another reason why “click independent” VBA programming is essential for reliable Excel Macros.

Below I am going to give 2 simple examples

  1. ActiveWorkbook causing confusing behavior
  2. ActiveSheet causing confusing behavior

ActiveWorkbook causing confusing behavior

Create a new workbook and save it as “WhyActiveWorkbookIsBadWithMacroCode.xlsm”.

Type Alt-F11 to get the VBA window.

Click Insert -> Module.

Copy and paste the code below into the module, then click one of the lines of it and press F5.

Sub ConfusingActiveWorkbook()

Dim whatYouThinkIsActiveWorkbook As Workbook
Dim whatActuallyIsActiveWorkbook As Workbook

Set whatYouThinkIsActiveWorkbook = Workbooks("WhyActiveWorkbookIsBadWithMacroCode.xlsm")
Set whatActuallyIsActiveWorkbook = Workbooks.Add

whatYouThinkIsActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) = "this is what you think you will see"
whatActuallyIsActiveWorkbook.Worksheets("Sheet1").Cells(1, 1) = "this is what you actually see since activeWorkbook confused you"

Debug.Print ActiveWorkbook.Worksheets("Sheet1").Cells(1, 1)

Set whatYouThinkIsActiveWorkbook = Nothing
Set whatActuallyIsActiveWorkbook = Nothing

End Sub

If not already open, open the immediate window by typing Ctrl+G. Look at the output in the Immediate window. It is not what you would think it was intuitively.

Why is this a problem?

If your macro is opening and closing multiple workbooks, you can easily find yourself
referencing the wrong workbook.

ActiveSheet causing confusing behavior

Make sure there are two worksheets, “Sheet1” and “Sheet2” in your workbook from above “WhyActiveWorkbookIsBadWithMacroCode.xlsm”.

In VBA project explorer, click on “Sheet1”. Copy and paste the code below into it. then click one of the lines of it and press F5.

Sub ConfusingActiveWorksheet()

Dim wbMacro As Workbook

Dim whatYouThinkIsActiveWorksheet As Worksheet
Dim whatActuallyIsActiveWorksheet As Worksheet

Set wbMacro = Workbooks("WhyActiveWorkbookIsBadWithMacroCode2.xlsm")

Set whatYouThinkIsActiveWorksheet = wbMacro.Worksheets("Sheet1")
Set whatActuallyIsActiveWorksheet = wbMacro.Worksheets("Sheet2")

whatYouThinkIsActiveWorksheet.Cells(1, 1) = "this is what you think you will see"
whatActuallyIsActiveWorksheet.Cells(1, 1) = "this is what you actually see since activeSheet confused you"

' pretend this is you clicking on the worksheet "Sheet2"
Debug.Print ActiveSheet.Cells(1, 1)

Set whatYouThinkIsActiveWorksheet = Nothing
Set whatActuallyIsActiveWorksheet = Nothing
Set wbMacro = Nothing

End Sub

If not already open, open the immediate window by typing Ctrl+G. Look at the output in the Immediate window. Again, It is not what you would think it was intuitively.

Why is this a problem?

This can be a problem if you have a worksheet macro that moves between multiple sheets. Though I avoid worksheet macros that are not events specific to that sheet, you may be stuck with a badly put together workbook with code that traps you in this. Unless you reference the explicit name of the sheet, you can quickly find that your macro goes to the wrong place.

OK, are ActiveWorkbook and ActiveSheet useful for anything?

Yes. If you are debugging and you want to find out information quickly, ActiveWorkbook and ActiveSheet are very useful in the immediate window.

If not already open, open the immediate window in the VBA window by clicking View -> Immediate Windows (or type Ctrl-G).

In the immediate window, type “? ActiveWorkbook.Name”. Look at the results.

Type “? ActiveSheet.Name”. Look at the results.

Note that VBA provides a drop down list of other attributes and methods.

Both can come in handy, but be very skeptical of them outside of the immediate window. Unless you are looking up values quickly in the immediate window, I strongly recommend avoiding these two properties.

How To Fix New England, New Jersey, and Caribbean Zip Codes in Excel

Assuming the zip codes are in column E, place the following code in column F:


Drag the formula down the column.

If you want to know why this works, click here.

If you want to know why this problem exists, click here.


Related books picked - and if possible read - by me. Sponsored by Amazon Associates.

How To Turn Your Excel Worksheet Into A Simple HTML Table

As I said in my NFL Playoff post, here is a brief description on how to convert an Excel worksheet into a simple HTML table.

What makes this tricky is that Excel tries to preserve all of the formatting from Excel in the HTML file, which adds an enormous amount of clutter that you do not need.

Here is what to do:

The Really Short Version

Save the worksheet as a “Single File Web Page”. Open it up in a text editor and erase everything above the <table> tag and below the </table> tag. Erase all of the attributes in the remaining tags.

The Longer And More Detailed Version

  1. Go to File, Click Save As.
  2. In the “Save as Type” drop box, choose the format “Single File Web Page (*.mht,*mhtml)”.
  3. Click Save.
  4. Click “Yes” in the pop up box to lose formating.
  5. Find the file where you saved it.
  6. Open it in a text editor like Notepad, though I highly recommend getting the free Notepad++
  7. Man there is a lot of code. Don’t worry. Just Search for the string “<table”. This is where the actual data from the worksheet is. It should contain a lot of <tr> and <td> tags. You should be able to spot your actual data inside the <td> and </td> tags.
  8. Erase all of the code before “<table”.
  9. Search for “</table”.
  10. Erase all of the code after “</table … > full tag.
  11. Erase all of the attributes inside all of the remaining tags such as “width=” and “style=”. A good short cut at this point is to a find and replace for what you are trying to erase and replace it with nothing since it is repeated in many of the tags.

That should do it.

Related books picked – and if possible read – by me. Sponsored by Amazon Associates.

HELP! Excel Dropped the Leading Zero from My New England 5 Digit ZIP Codes! – The New England Zip Code Problem, Part 1


Let me guess why you’re here:

Someone just gave you an Excel workbook where one of the columns is ZIP codes and many of them are 4 instead of 5 digits. The cities and states next to ZIP codes are all from New England,
and probably New Jersey and a few other places too
. You no longer have the original text file and are stuck with the workbook.

What’s the problem:

The problem is that Excel is not very good at preserving data types. A five digit ZIP code (which despite the postal service introducing nine digit ones with a dash between the 5th and 6th digit people still generally use) looks like an integer to Excel. Excel treats it as such and eliminates any zeros in the front. However, it is not an integer, it is a code. Specifically, it is a postal code, which you want to use to store as a text string. This isn’t a problem as long as your address is west of Philadelphia, but it becomes a real headache with New England, New Jersey, and Caribbean ZIP codes.

For example, the ZIP code for the neighborhood of Back Bay in Boston should be 02116. However, Excel assumes that it is an integer and gives you 2116.

Another way of thinking about the problem:

A good way of thinking about this is to look at the Postal Codes from other countries, which is what a ZIP Code really is. Here are a couple of examples what they look like in Canada and the United Kingdom, where they use alphanumeric postal codes.

Country Example Postal Code
Canada M5A 1N1
United Kingdom W11 2BQ

You would never store these codes as integers. You would store them as strings. Excel would not mistake them for integers, even if they started with zeros.

What’s the short term solution:

This solution assumes that all of your ZIP codes are in the original 5 digits, and not the new format of 5 digits + 4 digits.

The short term solution is fairly easy. Let’s say the zip codes are in column E. Insert a column to the right of the zip code column E. Next, in the first row of the new column F, type the following formula:


Replicate the formula down the column.

Wow, that formula is hard to read:

In plain english, what the formula says is:
"If the length of the value in cell E2 is less than the 5 digits in a ZIP code, then concatenate the correct number of zeros to the front of the value of E2 and convert it to a text string. Else, just convert the value in E2 to a text string."

Let’s break it up into its components.

It is all nested in an Excel IF function.

=IF(condition, do if true, do if false)

The condition clause

LEN(E2) < 5 – is the ZIP code less than 5 digits.

The Do if true clause

REPT(“0”,5-LEN(E2)) - repeat the character "0" (NOT the number 0, the text string value "0"). You want to make this a text code, not an integer. You want 5 minus the length of the less than 5
digit numbers because this is the exact number of zeros missing.

&TEXT(E2,”#”) – convert from an integer to a string
and concatenate the 4 or less character ZIP code to the string of zeros you just generated

The Do if false clause

TEXT(E2,"#")) – you found a 5 character ZIP code, convert it to a string from digits.

That's it.

Up Next:
Let’s say you had the original text file. You could prevent this
That will be discussed in The New England ZIP Code Problem: Part II

A Simple Example of How to use Class Modules for something useful in Excel VBA


When working in Visual Basic for Applications (VBA) and writing code, you often have to insert a new module (Insert->Module in the VBA Integrated Development Environment – IDE). A basic Module is for your normal code, while a UserForm is for creating simple Graphic User Interfaces (GUIs). However, one also sees the menu choice for a Class Module. Have you ever wondered for what one uses a Class Module?

[EDIT:Want to know what books I used to figure this out? Check out this post]


What is a Class Module?
A VBA Class Module is where you define a custom class from which to create objects.

Huh? What is a Custom Class?

Well, in my last blog entry, I described what the VBA Object Model was, which allows one to use the classes that are already part of Microsoft (MS) Office to create objects that you can use to program. Imagine being able to make your own classes. That is what a custom class allows one to do.

Alright, fine, but how does one even begin to understand how to create a VBA Custom Class?

When you create a Custom Class in a VBA Class Module, you are creating a blueprint for creating objects similar but not completely like the ones in the VBA Object Model.

OK, so what is in this blueprint called a Class?

A Class consists of two things:

  • Attributes
  • Methods

The easiest way to understand this is to think of the object as a Machine like a car. To make one, two, three, or a hundred thousand cars, one needs a blueprint. This blueprint will give attributes and methods for that car. The car’s blueprint is the Class.

So what are attributes?

A car usually has a choice of engine types (for example a V6 or V8). It can have a different number of doors, such as 2 or 4. It has a certain type of transmission, such as manual or automatic. The engine type, number of doors, and the transmission type are all attributes of everything that can be considered a car. These are the car’s blueprint’s attributes.

So what are methods?

The blueprint of a car (or certainly the modern Computer Aided Designs that have replaced blueprints) also gives the car blueprint’s methods, or actions that it can take once a car (an object) is made with it. It can turn either left or right. It can drive forward, backward or stay still. It can be in a certain gear. The potential actions of a car object described in the car’s blueprint class are methods.

Hold on a second – my Programming Friend just told me VBA is Not A True Object Oriented Language, therefore making custom classes not worth it in VBA. Why should I not just stop reading now?

While it is true that VBA is not truly Object Oriented in that it does not have inheritance, it does allow one to write classes that can still be useful.

Traditionally these class modules were used for:

  • Input/Output of text files
  • Error catching
  • Error logs

However, in my work with Excel VBA, I have found another use for VBA Class Modules.
Using Class Modules to Create Array Based Data Tables. By writing simple class modules that contain just attributes with a few simple methods, one can create data structures that allow easy loading and storing of data into VBA’s memory.

Now, you are probably saying to yourself, “If I need to put my data in a table in MS Office, why not just use Access?” You would often be right. You should usually Access instead. While you should always try to understand how a wheel works, never try to reinvent it. Unfortunately, sometimes, you will not be able to use Access.

As well, Access is not good at calculations or data cleaning. Being able to load your data into an easy to follow memory structure in Excel can be very handy.

OK, show me how to do one of your fancy schmancy Array Based Data Tables

Of course 😉 As I hope to make this the primary theme of this blog, I will now give you a simple example of writing a Class to be placed in an array of that class to act as a simple data table.

The purpose of the simple macro will be to read the three top rows and three left most columns (A1:C3) of an excel worksheet where the three columns store a first name, last name, and title. It will then print these to the immediate window as “[First Name] [Last Name] is a [Title]”.

To begin, save the workbook as “EmployeeInfo.xlsm”.

Next, while in Excel, type Alt-F11 on your keyboard to open the VBA IDE.
Next, in the VBA Integrated Development Environment (IDE), click Insert->Class Module.

Now the next part is easy to forget.
Click View->Properties Window
Click the Class Module you just inserted
Find the attribute (Name). Change it to cEmployee.
This is really important or VBA will not know what your class is called.

Now, a class must have attributes. It does not necessarily need methods. In this case, with the exception of a method that creates the output, the Class will just have attributes. There are Get and Let methods. This is to provide protection of the attributes. Type the following.

‘Class cEmployee

Private pFirstName as String
Private pLastName as String
Private pTitle as String

Now, each of these attributes should have Get/Let methods.

Public Property Get FirstName() as String
FirstName = pFirstName
End Property

Public Property Let FirstName (Value as String)
pFirstName = Value
End Property

Public Property Get LastName() as String
LastName = pLastName
End Property

Public Property Let LastName(Value as String)
pLastName = Value
End Property

Public Property Get Title() as String
Title = pTitle
End Property

Public Property Let Title(Value as String)
pTitle = Value
End Property

Methods are where the magic happens

Methods allow you to have functions and procedures that you can call right away to manipulate an objects attributes. To make this simple, we will just have a method that Prints

Function EmployeeFullInfo() as String
EmployeeFullInfo = FirstName & “ “ & LastName & “ is a “ & Title
End Function

The final class should like this

‘private attributes
Private pFirstName as String
Private pLastName as String
Private pTitle as String

‘Get/Let Methods
Public Property Get FirstName() as String
FirstName = pFirstName
End Property

Public Property Let FirstName (Value as String)
pFirstName = Value
End Property

Public Property Get LastName() as String
LastName = pLastName
End Property

Public Property Let LastName(Value as String)
pLastName = Value
End Property

Public Property Get Title() as String
Title = pTitle
End Property

Public Property Title Let (Value as String)
pTitle = Value
End Property

‘General Methods
Function EmployeeFullInfo() as String
EmployeeFullInfo = FirstName & " " & LastName & " is a " & Title
End Function

Now, let’s put our fancy schmancy class in a program.
Tab back into Excel.
Rename Sheet 1 “EmployeeInfo”
For the worksheet, “EmployeeInfo” type starting in A1

Jack Smith CEO
Steve Johnson CFO
Michael Andersen COO

Make sure each word is in a different cell so that 3 x 3 cells (A1:C3) are filled.

Now, go back to the VBA IDE. (Alt-Tab should get you there)
Insert a normal code module (Insert->Module)
Type in the following code

Sub LoadAndPrintBoard()
Dim CurrentBoardMember as CEmployee
Dim PrintBoardMember as CEmployee
Dim arrayBoardMemebrs() as CEmployee
Dim WSBoardMembers as Worksheet
Dim lngTotalRecords as Long
Dim lngRecordCounter as Long
Dim strFullNameAndTitle as String
Set WSBoardMembers = Worksheets(“EmployeeInfo.xlsm”)
lngTotalRecords = WSBoardMembers.UsedRange.Rows.Count
‘ Read in the data here
For lngRecordCounter = 1 to lngTotalRecords
Set CurrentBoardMember = New CEmployee
' This part is tricky and hard to read in html*
CurrentBoardMember.FirstName = WSBoardMembers.Range(WSBoardMembers.Cells(lngRecordCounter, 1), WSBoardMembers.Cells(lngRecordCounter, 1)).Value
CurrentBoardMember.LastName = WSBoardMembers.Range(WSBoardMembers.Cells(lngRecordCounter, 2), WSBoardMembers.Cells(lngRecordCounter, 2)).Value
CurrentBoardMember.Title = WSBoardMembers.Range(WSBoardMembers.Cells(lngRecordCounter, 3), WSBoardMembers.Cells(lngRecordCounter, 3)).Value
Redim Preserve arrayBoardMemebrs(1 to lngRecordCounter)
Set arrayBoardMemebrs(lngRecordCounter) = CurrentBoardMember
Set CurrentBoardMember = Nothing
Next lngRecordCounter

‘ print out everything to the immediate window
For lngRecordCounter = 1 to lngTotalRecords
Set PrintBoardMember = arrayBoardMemebrs(lngRecordCounter)
Debug.Print PrintBoardMember.EmployeeFullInfo()
Set PrintBoardMember = Nothing
Next lngRecordCounter

‘ clean up the objects
‘ clean up the objects in the array or you will quickly eat up Excel’s memory
For lngRecordCounter = 1 to lngTotalRecords
Set arrayBoardMemebrs(lngRecordCounter) = Nothing
Next lngRecordCounter
Set WSBoardMembers = Nothing

End Sub

*For an explanation of why the code is that complicated, please check out this post.

Click in the program module. Type F8 to step through the program. I highly encourage this. It will give you a much better sense of what is occurring.

The parts of the code that one should pay the closest attention to are the Dim and Set statements for the classes. What makes objects often difficult to follow for new comers is when declare an object of a certain class with a Dim statement, you are not really creating an object. You are creating something in which to hold am object of a certain class. You have to Set that holder to an object. You can create an entirely a new object with the New statement, or assign that holder to one that already exists. One big reason why you should type this code in is to see how drop boxes of a custom class’ methods become available once you set a holder of the class (Dim statement) to a new creation of an object of that class (New statement)

What is the advantage of these Class Modules?
The advantages are:

  • Accuracy
  • Control
  • Not having to type things over and over

Often, people will attempt something like this using a 2 dimensional array of strings.
This becomes a real headache because remembering what ArrayElement(1,2) – is column 2 the title? First name? becomes very difficult. You immediately get more accuracy. As well, one can specify different data types for different rows, which is much more difficult if not impossible in a multi-dimensional array.

While not shown in the example, it becomes much easier to find the data you want by doing simple searches through the array. As well, you can specify exactly what part of the data that you want quickly.

Not having to type things over and over:
By being able to re use the print method in this example saves a lot of hassle, especially if you want to use it in multiple places.


While not a truly Object Oriented Language, VBA does provide certain object oriented language capabilities. These capabilities can make data analysis much easier, especially by being able to crudely replicate the data tables one would find in more specialized Data Analysis packages such as SAS, Stata, and R.

Would you like to know more?
More can be found here.


These are the two books that helped me the most figuring out the above, both of which I reviewed here. If you trust me or just don’t feel like reading more, you can click on the links for them below. Sponsored by Amazon Associates.

What is the VBA Object Model and Why You Should Use It for Excel Macros

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"


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


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


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.



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))



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.