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.

Walkenbach & Mr. Excel: The Two Books to Get Stared in Excel VBA

If you want to get begin using Excel VBA, the best way to do so is to read these two books:

They will give you the foundation that you need to use Excel in powerful and just as importantly in reliable ways. If you choose to read them, make sure that you type out all of the examples in these books yourself in the Excel VBA IDE (what you see when type Alt-F11).

Excel VBA Programming For Dummies by John Walkenbach

This is the book with which to get started, especially if you do not know the first thing about programming. Walkenbach does an excellent job of holding your hand through the basic concepts that you need to know. As well, he sells a power utility pak that uses VBA to give Excel powerful and useful features. For extra money, you can see the code for it. I strongly recommend purchasing the pack and paying to see the code. You will see lots of examples of how to write practical applications for Excel in VBA.

VBA and Macros for Microsoft Excel Bill Jelen and Tracy Syrstad

WARNING: They have written similar books written since then, but previewing them on Amazon makes me think that they are missing key content. You need to buy the one written in 2004.

This book will take you to the next level. It is aimed at people who are more intermediate than beginner. It will show you just how powerful VBA in Excel can be. This is the book that allowed me to start getting my head around the VBA Object Model and writing VBA Classes.

While you should read the whole book, you should pay very close attention to the following chapters:

  • R1C1 Style Formulas
  • Names
  • Event Programming
  • User Forms
  • Automating Word
  • Event Programming
  • Creating Classes Records and Collections – this one is the most important
  • Handling Errors

I feel that these books are essential for getting started. You can buy them from the links below or at the top of the post:

VBA’s Very Handy but Little Known Debug.Print

This entry is going to be quite short.

Tired of endless Message Boxes when trying to discover the flaw in your code? Try the wonderful but surprisingly little known VBA command Debug.Print. This is a VBA command that I have used for years. I was recently surprised to discover that several of my friends who use VBA professionally did not know about it.
When debugging your VBA code, it always nice to follow the values of your variables and functions to try to discover the source of the problem. There are many ways to do this.
Message Boxes (Command: msgbox) have their uses but if you are trying to track the value of a loop counter they become extremely annoying very quickly. Having to click OK 40 times really gets on one’s nerves.

Say hello to Debug.Print

It’s an extremely simple command. Anywhere in your code, just type “Debug.Print” and the variable or function that you want afterwards. You can even customize it with a string.


Sub TestDebugDotPrint()
Dim lngTroublesomeVar as long
lngTroublesomeVar = 22
Debug.Print “The lngTroublesomeVar is: “ & lngTroublesomeVar
End Sub

If you look at the immediate window in the VBA IDE

(if you can’t see it, click View->Immediate Window or type Ctrl-G),

you should see:

The lngTroublesomeVar is: 22

Use it for all of your debugging needs!

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.