What is the default scope of worksheets and cells and range?

by

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.

More Related Contents:

  1. Save excel file multiple times with file names in another workbook.
  2. Function to convert column number to letter?
  3. Read/Parse text file line by line in VBA
  4. How to use the Implements in Excel VBA
  5. How to pause for specific amount of time? (Excel/VBA)
  6. VBA code doesn’t run when cell is changed by a formula
  7. Easiest way to loop through a filtered list with VBA?
  8. What is the difference between dim and set in vba
  9. Quicker way to get all unique values of a column in VBA?
  10. Change date format using substitute or replace
  11. Parsing an ISO8601 date/time (including TimeZone) in Excel
  12. Getting the actual usedrange
  13. Delete a row in Excel VBA
  14. VBA: Convert Text to Number
  15. Concatenate cell data into another data if values matches
  16. How can I run VBA code each time a cell gets its value changed by a formula?
  17. How to convert 32 bit VBA code into 64 bit VBA code
  18. Referencing Excel Userform Control Name Value From String (VBA)
  19. Fill formula down till last row in column
  20. Outputting Excel rows to a series of text files
  21. Refreshing all the pivot tables in my excel workbook with a macro
  22. Countif With Multiple OR Criteria
  23. Vlookup to copy color of a cell
  24. every possible combination of the contents of 2 columns in excel
  25. How to delete rows in Excel based on criteria using VBA?
  26. Macro to export MS Word tables to Excel sheets
  27. VBA WinHTTP to download file from password proteced https website
  28. Access files with long paths (over 260)
  29. Concatenate multiple ranges using vba
  30. How to highlight selected text within excel

Leave a Comment