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"

whatActuallyIsActiveWorkbook.Activate
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"

whatActuallyIsActiveWorkbook.Activate
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"
whatActuallyIsActiveWorksheet.Select
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:


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"

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.

facebooktwittergoogle_plusredditpinterestlinkedinmail