3

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.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
CodeCamper
  • 6,609
  • 6
  • 44
  • 94
  • It is Activeworkbook unless otherwise specify in preceding codes – Alex Feb 10 '15 at 18:51
  • @Alex is there a way to change the default behavior with preceding code so that it always references ThisWorkbook? Or would I have to simply declare ThisWorkbook for each time I grab something (this is how I do it currently) . – CodeCamper Feb 13 '15 at 16:55
  • As Tim's answer below, you could put your code in thisworkbook module and unqualified worksheets will be default to thisworkbook – Alex Feb 17 '15 at 15:17
  • @Alex what is an unqualified worksheet vs a qualified worksheet? – CodeCamper Feb 18 '15 at 20:20
  • sorry it was poorly explained. I meant to say an unqualified worksheet() object. – Alex Feb 18 '15 at 20:31
  • @Alex Just making sure there was no secret way to change this default behavior in certain objects. Thanks. – CodeCamper Feb 18 '15 at 20:36
  • I am not sure if there are tricky ways, say for e.g. changing registry for the allocated object properities (this goes beyond normal vba coding). Other than that it is pretty much straight forward as Tim's answer mentioned. – Alex Feb 18 '15 at 20:41

1 Answers1

18

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(),|  --------------------------------------------------
Names()           |  Any other module  | Active sheet (via [_Global])
-----------------------------------------------------------------------

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.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125