VBA Assumes the Active Workbook and Active Worksheet in Excel
Bottom line: Learn 2 critical assumptions that VBA makes when running our macros in Excel, and avoid errors & disaster.
Skill level: Beginner
VBA Needs Context
When writing and running macros it's critical that we provide the right context.
We must specify the workbook and/or worksheet we want to run the code on. Otherwise, VBA will make that decision for us, and that can get us in trouble!
In my free webinar on “The 7 Steps to Getting Started with Macros & VBA”, I use this analogy of my wife and I in the kitchen to explain this rule. When we are communicating with someone it's important to provide context about what we are talking about. Otherwise they will be confused.
In this case, I ask my wife, “Hey, can you turn the light on please?” If we are both in the kitchen, and our kitchen only has one light, then it's easy for her to understand which light I'm referring to.
However, if she is in another room and I yell out the same question, she is going to be confused. Which light am I talking about???
This can lead to confusion and arguments, especially when communicating with your spouse… 🙂 VBA has a similar rule that can also get us in trouble.
VBA Assumes the Active Workbook and Active Sheet
If we don't specify which workbook or worksheet we want to run a line of code on, then VBA will run that code on the Active Workbook and Active Worksheet.
The ActiveWorkbook is the workbook that you (or the user) has selected before running the macro.
The ActiveSheet is the worksheet tab that is currently selected before running the macro. If multiple sheets are selected, the ActiveSheet is the sheet that is currently being viewed.
This assumption is not always what we want. So it's important that we tell VBA exactly where we want to run the code to modify an object on a sheet.
VBA Macro Examples
Sub ActiveWorksheet_Assumption() 'VBA assumes we are referring to the 'ActiveWorkbook and ActiveWorksheet if NOT specified 'Runs on the ActiveWorkbook and ActiveWorksheet Range("A1").Value = 100 End Sub Sub ActiveWorkbook_Assumption() 'Runs on the ActiveWorkbook Worksheets("Sheet2").Range("A2").Value = 100 End Sub Sub Fully_Qualified() 'Runs on the specified workbook and worksheet Workbooks("Book2.xlsx").Worksheets("Sheet3").Range("B3").Value = 100 End Sub
Referencing Workbooks and Worksheets
There are a TON of different ways to reference workbooks & worksheets, and provide the right context in VBA. The important thing is that you know this rule before running your macros.
I have a few videos from my VBA Pro Course on referencing workbooks and worksheets that I share after my webinar. So if you are interested in learning more about this, get registered for my free training webinar on “The 7 Steps to Getting Started with Macros & VBA”.
You can pick a day & time that works for you.
Please leave a comment below with any questions on this rule. Thank you! 🙂