By user4039065


2016-04-02 01:32:56 8 Comments

It is pretty much widely accepted that this is not 'best practise'.

dim rng as range
with thisworkbook    '<~~ possibly set an external workbook 
    with .worksheets("sheet1")
        set rng = .range(cells(2, 1), cells(rows.count, 1).end(xlup))
    end with
end with

The two Range.Cells properties that define the scope of the Range object will default to the ActiveSheet property. If this is not Sheet1 (defined as the .Parent in the With ... End With statement), the assignment will fail with,

Run-tim error '1004': Application-defined or object-defined error

Solution: use .Cells not Cells. Case closed.

But...

Is the . necessary in this Range object definition when both the Range.Cells properties inherit the .Parent worksheet property that is defined in the With ... End With statement?

How can this,

dim rng as range
with thisworkbook    '<~~ possibly set an external workbook 
    with .worksheets("sheet1")
        ' define rng as Sheet1!A2 to the last populated cell in Sheet1!A:A
        set rng = .range(.cells(2, 1), .cells(rows.count, 1).end(xlup))  '<~~ .range
    end with
end with
debug.print rng.address(0, 0, external:=true)

... be different from this,

dim rng as range
with thisworkbook    '<~~ possibly set an external workbook 
    with .worksheets("sheet1")
        ' define rng as Sheet1!A2 to the last populated cell in Sheet1!A:A
        set rng = range(.cells(2, 1), .cells(rows.count, 1).end(xlup))  '<~~ range not .range
    end with
end with
debug.print rng.address(0, 0, external:=true)

We use .range when the parameters that define the scope of the range are ambiguous; e.g. .range([A1]) The A1 cell could be from any worksheet and will default to the ActiveSheet property without the .. But why do we need to reference the parent of a range object when the scope that defines it has properly referenced its parent worksheet?

3 comments

@chris neilsen 2016-04-02 04:10:32

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.

@Siddharth Rout 2016-04-02 07:22:41

Makes more sense now :)

@Siddharth Rout 2016-04-02 06:16:30

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

@PatricK 2016-04-12 02:14:39

I am with Siddharth. It really depends on what's the "ActiveSheet", if the activesheet is a Chart Sheet, the bottom set of code errors out for sure, unless .Range(...) used.

@Comintern 2016-04-02 01:53:44

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.

@Comintern 2016-04-02 02:10:13

@Jeeped - I was actually a bit surprised when I was testing it. Put the line Debug.Print Range("A1").Parent.Name in a Worksheet, then activate something else and run it.

@Comintern 2016-04-02 04:30:57

@chrisneilsen - Actually, I'm not. It doesn't matter if the cell references are qualified or not - Range(Foo) called from a Worksheet module will be evaluated as Me.Range(Foo). Range(Foo) called from anywhere else will evaluate as ActiveSheet.Range(Foo). The arguments are irrelevant. In fact, since Cells has the same scope behaviour as Range vis-a-vis a Worksheet object exhibits exactly the same scope resolution issue.

@user4039065 2016-04-02 04:42:32

I have to admit that the Private nature of a worksheet code sheet overrides the nature of the 'lone wolf' range object but at the same time you cannot define a range on a worksheet with the scope of cells from another worksheet unless you only use their string addresses.

@Comintern 2016-04-12 00:03:56

@Jeeped - No worries, first doesn't mean the answer that you found most useful. If you feel Siddharth's answer is more helpful either personally or to anyone else who might stumble across the question, don't hesitate to accept it on my account.

@BrakNicku 2016-04-12 16:58:53

@Jeeped there's no such thing as 'lone wolf' range object. It's only a matter of scope - in worksheet's class module, you can reference all properties/methods of this sheet. If it's inside module - then it references Application.Range property. It's described in msdn.

@Mathieu Guindon 2017-05-19 18:05:18

@BrakNicku no, it's a matter of COM spaghetti. An unqualified Range doesn't magically scopes to Application, it's scoped to the hidden _Global module, and ultimately resolves to whatever the ActiveSheet is. MSDN is telling dumbed-down half-truths that are thoroughly misleading if you're looking at the actual internals. Unqualified Range used in a worksheet's code-behind resolves to that sheet only because that's how scoping works in VBA; Me has a Range property, so unqualified Range is like Me.Range, just like Me.TextBox1 is same as TextBox1 in a form's code-behind.

@Mathieu Guindon 2017-05-19 18:06:09

Hence, because of how the same code scopes to different things depending on context, it's definitely a good idea to always qualify Range calls, be it only for readability/maintainability.

@BrakNicku 2017-05-19 18:59:24

@Mat'sMug I agree almost 100% with your comments, and I'm fully aware how scope works inside a class module (in this case Me in Worksheet module class). I have never said that using using unqualified Range is a good idea. But do you have any example to show that _Global.Range behaves differently than Application.Range?

@Mathieu Guindon 2017-05-19 19:25:37

@BrakNicku not saying it behaves differently, just that both _Global.Range and Application.Range actually resolve to ActiveSheet.Range - MSDN is utterly confusing about that, and imply that unqualified Range is an implicit member call on Application which, it isn't. It ends up doing the same thing, and if you're a VBA programmer that's acceptable I guess, but if you're trying to parse VBA code and properly scope it exactly as the VBA runtime does, it's garbage.

@BrakNicku 2017-05-19 19:41:02

@Mat'sMug there's a sentence in MSDN When used without an object qualifier, this property is a shortcut for ActiveSheet.Range - do you think it is confusing? I know that there is no "magic" involved and you can use almost all properties and methods of class Application without qualifing it only because hidden _Global class exists and have probably a set of very simple functions, you said it's all about "internals". Do you have access to internals of _Global class?

@Mathieu Guindon 2017-05-19 19:47:25

@BrakNicku Yes we do! Rubberduck devs have both hands knee-deep into the internals of every type library referenced by any VBA project, including the Excel object model, the VBA standard type library, and the completely messed-up MSForms library (UserForms).

@BrakNicku 2017-05-19 20:02:54

You are definitely doing a great job! I have never seen this tool before and if I ever come back to using VBA more actively, I'll definitely give it a try. You also definitely know way more than me about internals of _Global class, but I still think my comment you objected is valid. Shorthand but valid.

Related Questions

Sponsored Content

9 Answered Questions

1 Answered Questions

[SOLVED] Excel stops responding while executing loop

  • 2017-08-30 03:58:10
  • user1955215
  • 123 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba

2 Answered Questions

[SOLVED] VBA opening a few worksheets and copying cell value to the current

  • 2018-12-30 14:19:35
  • Wasteland
  • 28 View
  • 0 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

[SOLVED] excel VBA code to ignore an email attachment if missing from folder

  • 2018-07-26 15:42:02
  • Classmaz
  • 410 View
  • 1 Score
  • 1 Answer
  • Tags:   vba excel-vba

2 Answered Questions

[SOLVED] Copy Range Sheet1 Paste in Active Cell Sheet 2

0 Answered Questions

vba copy value if cell on workbook A matches cell on workbook B?

  • 2017-01-13 11:37:35
  • Princess.Bell
  • 255 View
  • 0 Score
  • 0 Answer
  • Tags:   excel vba

1 Answered Questions

[SOLVED] Conditional copy Excel File-2 data to excel file-1?

1 Answered Questions

[SOLVED] Excel 2010 Importing Data into first blank cell

  • 2013-10-30 20:42:15
  • user2939057
  • 952 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba

1 Answered Questions

[SOLVED] Using range-variable in multiple worksheets

  • 2015-01-22 15:57:43
  • bananabombo
  • 543 View
  • 2 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

Sponsored Content