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
- ActiveWorkbook
- 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
- ActiveWorkbook causing confusing behavior
- 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.
by John Walkenbach
by Bill Jelen and Tracy Syrstad