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: