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! 🙂

 

2 comments

Your email address will not be published. Required fields are marked *

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly