VBA Range("A1:A5") vs [A1:A5] - benefits/dangers

[Nyanko]

I was doing some research and found that I can refer to a range using [] rather than with ("") .

Range("A1:A5").select
vs
[A1:A5].select

What are the advantages/disadvantages to using this method ?

 

[DocAElstein]

Hi

Not disputing anything, but FWIW I occasionally find it useful to do something ...

_ interseting contribution, thanks...I guess in my naive way of explaining the [] sees your x as a VBA Thing and not a string, so accepts it

I was just thinking of something along those lines, but much more silly:

.... if you use the [] notation - it has to be whatever you typed when you wrote the code. That alone is enough for me not to use it as I try to avoid ever hardcoding ranges.

Isn’t this doing what the OP did with [], but not hardcoding

[color=blue]Dim[/color] rng [color=blue]As[/color] Range

[color=blue]Dim[/color] strAddress [color=blue]As[/color] [color=blue]String[/color]
[color=blue]Let[/color] strAddress = "A" & 1 & ":A" & 5 & ""
[color=blue]Set[/color] rng = Range(strAddress)

[rng].Select

( But of course is very very “Silly”, as

[rng].Select

Not only has a extra unnecessary call to the Evaluate, but is also less compact than this

rng.Select



But we are both just tackling the Hardcode or not hardcoding issue, and shg's unnecessary call to the Evaluate remains an important issue ( as well as the extra initial defining , admitedly in your case it is minimal or none at all if you already did it for some other reason )

 

[RoryA]

Isn’t this doing what the OP did with [], but not hardcoding

[color=blue]Dim[/color] rng [color=blue]As[/color] Range

[color=blue]Dim[/color] strAddress [color=blue]As[/color] [color=blue]String[/color]
[color=blue]Let[/color] strAddress = "A" & 1 & ":A" & 5 & ""
[color=blue]Set[/color] rng = Range(strAddress)

[rng].Select

You're still hardcoding the rng part. The rest is just really convoluted and to my mind destroys any benefit that might be claimed for using [] in the first place.

At the end of the day it's all personal preference. I've never seen a need, or real benefit, for the [] notation whereas I know some others value brevity over everything else.

 

[DocAElstein]

Hi

You're still hardcoding the rng part.....

I must have misunderstood what i tried to google on what Hard coding meant: - I thought it, in simple terms, meant being able to change what the code line did, or , as in this case, what Range therein was referenced , at some other code point , typically at the start, rather than actually having to change that code line. ( The point then not having to go through changing every occurrence of the said range, should that same range be used in more parts of the code and all needed to be changed. )

But I think we are getting the point that the compactness or brevity of the [] is probably lost on the other disadvantages.

Those disadvantages being:
_ 1 ) I think we have the extra unnecessary call to the Evaluate disadvantage discussed.

_ 2) Some Compile, Runtime difference. Not quite sure what shg was talking about in regards to the compiler (versus at runtime) as i am not clear what the differences are there – that is to say which method does compiler or runtime. Maybe there aint any differences there and he just made that bit up

Alan

P.s. I see that Name is a VBA Object. And all this works

Range("A2:A5").Name = "x"
[x].Select

Names.Add Name:="y", RefersTo:=Range("A1:A5")
[y].Select

Range("x").Select
Evaluate("x").Select
Range("y").Select
Evaluate("y").Select

And I see it would not let me do this

Names.Add Name:="A1"

I wonder if this answering one of my questions about [] recognising something like A1 as cell ( 1, 1 ) and is telling me that something like A1 is a “reserved” named range for the first cell,
And strictly speaking the convention here is
Range( strRangeName )
As noted “ tells VBA a string is coming, another “ says it is finished. Hence
Range(“A1”)
Or in the named ranges here and what kalak made,
Range(“x”)

I learnt some good basic stuff here again. This Range referencing stuff goes on and on...

P.p.s
still have not got it 100% sure what Evaluate does
-- sort of converts the thing ( object) ( or maybe Fuction ( Worksheet or Application ( VBA ) ) to its sort of value , Hence the named range Object becomes it's actual range etc...

 

[kalak]

But I think we are getting the point that the compactness or brevity of the [] is probably lost on the other disadvantages.

Those disadvantages being:
_ 1 ) I think we have the extra unnecessary call to the Evaluate disadvantage discussed.

Given the capacity of modern computers, the "extra unnecessary call" wouldn't take very long.
Maybe somewhat less additional time than the extra typing to avoid this call?

Looking at the prolixity involved in typing out some of the VBA codes on this and other forums, it seems to me some brevity could often be of benefit.

 

[DocAElstein]

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:

[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

[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

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