2

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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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.

VBA Needs Context Slide 1

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???

VBA Needs Context Slide 2

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.

VBA Code Runs on the Active Workbook and Active Sheet if Not Specified

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”.

Macros and VBA Webinar Banner - Join Me - 550x200

You can pick a day & time that works for you.

Please leave a comment below with any questions on this rule.  Thank you! 🙂

 

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 2 comments
Sandeep Kothari - May 21, 2018

what this massage received “This webinar has expired!” ?

Truly, Jon, Restart your webinar on VBA. it was a great one!

Reply
mesi - February 6, 2018

hi jon
what this massage recived me “This webinar has expired!” ?

Reply

Leave a Reply: