Bottom line: Learn 2 critical assumptions that VBA makes when running our macros in Excel, and avoid errors & disaster.
Skill level: Beginner
Click here to register for the free webinar on Macros & VBA
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! 🙂
hi jon
what this massage recived me “This webinar has expired!” ?
what this massage received “This webinar has expired!” ?
Truly, Jon, Restart your webinar on VBA. it was a great one!
I have a problem where this works on all active worksheets. I need it to work on only Sheet3. I have looked all over google and still could not find the golden egg. If any one can help I would appreciate it.
(MODULE 1)
Option Explicit
Dim RunTimer As Date
Sub Weather()
RunTimer = Now + TimeValue(“00:00:20”)
Application.OnTime RunTimer, “Weather”
Range(“B2”).Select
ActiveCell.FormulaR1C1 = “KFFO”
End Sub
(SHEET 3)
Private Sub Worksheet_Activate()
Call Weather
End Sub
How can one make sure that when the excel is attached to the email based on an entry in the excel or a submit button, that the macros are also included in that copy? it seems that the excel being forwarded, although correct, has not any macros left in it, hence it will not work for the person receiving it. the file is saved as an .xlsm format, however the macros from the original file are not in the excel being sent as an attachment.
I’m having issues where this doesn’t work. I am copying one spreadsheet to a new workbook. I’m running Access VBA code against the new workbook, It moves through the new workbook correctly, but it puts one part of the code into the first spreadsheet, and the next part of the code in the new workbook. how do I keep it from doing that?
Please disregard my last comment. I found my issue. I forgot to put xlApp. in front of the range I was using in Access VBA.
Thank you.