What is the default scope of worksheets and cells and range?
When you just type worksheets() what is the default scope ActiveWorkbook or ThisWorkbook? For those who do not know these distinctions they are extremely important especially in Excel 2013 when you want macros to run when you switch to different workbooks.
In a standard module an unqualified Worksheets()
will always reference the ActiveWorkbook. In the ThisWorkbook
module, the implicit qualifier is Me
and that will reference the containing workbook.
Likewise, an unqualified Range()
or Cells()
(or Rows()
/Columns()
) in a standard module will reference the ActiveSheet, but in a sheet code module the implicit qualifier is Me
, and will reference the corresponding worksheet.
Unqualified... Where Implicit Qualifier
-------------- -------------- -------------------
Worksheets(), ThisWorkbook Containing workbook (Me)
Sheets() Any other module Active workbook (via [_Global])
Range(), Cells(), Sheet module Containing sheet (Me)
Rows(), Columns(), Any other module Active sheet (via [_Global])
Names()
The easy way to avoid having to remember any of this is to always fully qualify any Worksheets
, Sheets
, Range
, Cells
, or Names
reference.
Qualify the member call with Me
when referring to ThisWorkbook
in that module's code-behind, or when referring to Sheet1
in that module's code-behind.