Is the . in .Range necessary when defined by .Cells?

No, the . is not required where the cell references inside the brackets are qualified, unless the code is in a Worksheet module. That said it is faster to run set rng = .range(.cells(...), .cells(...)) than it is to run set rng = range(.cells(...), .cells(...)) so including the . does some good.

For a Worksheet module, the . is required.


The answer seems to be: only if the code is located in a Worksheet object. I strongly suspect that this is because the Worksheet objects are the only ones that are both extensible and have a Range function. When Range is called from a Worksheet, that object's Range function has scope. When the code is located in ThisWorkbook or a user module or class, the Range function with the closest available scope is the global Range object (assuming of course that there isn't a user defined Range function). That one is tied to the Application, which has to resolve it based on the passed parameters and forward the call to the correct Worksheet.


My opinion is slightly different here.

YES it is required. You can't always control where the user may run the code from.

Please consider these few test cases

SCENARIO

Workbook has 2 worksheets. Sheet1 and Sheet2


TEST 1 (Running from a module)

Both Code give same result

TEST 2 (Running from a Sheet code area of Sheet1)

Both Code give same result

TEST 3 (Running from a Sheet code area of Sheet2)

'~~> This code fails
set rng = range(.cells(2, 1), .cells(rows.count, 1).end(xlup))

You will get Application Defined or Object defined error

enter image description here

And hence it is always advisable to properly qualify your objects so that the code can run from anywhere

Tags:

Excel

Vba

Range