Hi
_ . While struggling with a problem here just now, relating to Named Range Referencing, .....
Copying part of a worksheet in one workbook to another workbook
_ ..... I noticed a possible advantage ( or disadvantage depending on your opinion! ) of the
[] in comparison with
Range(“ “)
_ . Consider the code here:
Code:
[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] TestANamedRangeReferrenceDonk_1()
[color=blue]Dim[/color] WB [color=blue]As[/color] Workbook: [color=blue]Set[/color] WB = ThisWorkbook
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = WB.Worksheets.Item(7) [color=darkgreen]'Seting ws to 7th Tab (from left in This workbook) Note ## This sets the Scope to Workbook for Normal Module or to the Worksheet of The Sheet Module if the Code is in a Sheet Module[/color]
Names.Add Name:="y", RefersTo:=Range("A1")
[y].Value = """[y]"""
Range("y").Value = """Range(""y"")"""
Evaluate("y").Value = """Evaluate(""y"")"""
Names.Add Name:="w", RefersTo:=ws.Range("A1")
[w].Value = """[w]"""
Range("w").Value = """Range(""w"")""" [color=darkgreen]' "Application or Object-defined" error if code not in normal module or 7th Tab Sheet Module[/color]
ws.Range("w").Value = """Range(""w"")""" [color=darkgreen]' ## "Application or Object-defined" error if code not in normal module or 7th Tab Sheet Module ( Due to "Scope" problem )[/color]
Evaluate("w").Value = """Evaluate(""w"")"""
[color=blue]End[/color] [color=blue]Sub[/color]
_ . It will work fully in both a Normal module and also in the Sheet Module if that Sheet Module is that referred to by the line
Code:
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = WB.Worksheets.Item(7) [color=darkgreen]'Seting ws to 7th Tab (from left in This workbook) Note ## This sets the Scope to Workbook for Normal Module or to the Worksheet of The Sheet Module if the Code is in a Sheet Module[/color]
_ . It will, however, error at two lines as indicated in the
' comments , should the code be in a different Sheet Module to that specified in the ws defining code line .
_ . This is suggesting
_ a ) that somehow
[] makes an unqualified range reference refer to the Application.Range as is the case in a normal module.
http://www.mrexcel.com/forum/excel-...-range-range-value-anomaly-2.html#post4038308
Range(“ “) in a Sheet Module attempts to find the Named Range in the Sheet of the sheet module in which the code is. This explains the first line which errors.
_ b ) For a Sheet Module, this line
Code:
Names.Add Name:="w", RefersTo:=ws.Range("A1")
Appears to set the “correct wanted” Range, but sets the “scope” to the sheet that the code is in. This somehow explains the second line which errors: -
[] seems to ignore this “Scope” – It somehow “knows where to go.
(
Note in passing if the Named range is set manually, then by default the Scope is set to the workbook ( as does the Names.Add by the code in a Normal module ) , in which case only Range("w").Value line errors for the case of the code in a different worksheet module, the “scope”, presumably in this case being valid for all sheets..(. somehow ) Or it is “ignored” )
_...........................................
So:
_ . 1) Clearly there is something more to the way a Range is “called” by Evaluate, – it follows a “different” route resulting in the phenomenon’s described above.
_ . 2) So one could say “it allows less explicit referencing in a sheet Module, in the case of a named range” (whereas
Range(“ “) does not!) Somehow
[] knows where to go, ( ignoring ( By-Passing ) the “Scope” and the normal Sheet reference conventions for Modules) ( And if the named range is set manually, it can be referenced in a code where no Sheets are defined – Again the “brevity” thing)
_ . 2a) I guess again that is a matter of opinion if that is an advantage.
Just my opinion: That is a disadvantage. I think one should be punished as much as possible for relying on Implicit defaults!! ( and i do not agree that Brevity in writing a code is an advantage, (
but i would fight to the death for anyone else to have the right to have the opposite opinion ) )
Alan