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 ?

 

[RoryA]

You can't build up the address string 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.

 

[DocAElstein]

......
What are the advantages/disadvantages to using this method ?

Dangers could be that there is a lot more to it than meets the eye at first glance. I try to elaborate on Rory’s point....( along the way..).

The square bracket pair
[]
is often said to be the shorthand of the Evaluate Method
Evaluate(“ “)

Consider the following codes. Initially Taking it back a stage, rather than selecting a Range, use VBA Range(" "), Evaluate(" "), and [] to try getting the Cell contents from Cell A1

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][/tr]
[tr][td]

1​

[/td][td]Somefink[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Tabelle1[/td][/tr][/table]

You can do that with Range(" ") and Evaluate(“ “) , and build up a string as shown in the following codes. The codes also shows however that this cannot be done with the [] brackets “shorthand”.
With Evaluate(“ “) one can , build up very complicated strings, including those to make up a Range as the code shows. The point is that you have the ability to combine, as it were, VBA Code and Spreadsheet things with Evaluate(" "). As Rory pointed out the [] sort of restricts you to code things. Because of the absence of an opening and closing quote in the “definition” as it were of [] we kind of have in this case
[ object to be evaluated / code world ]

Compared with

Evaluate( string )

Where the string can then be built up using those in / out pair things “ & and & “ which takes us form the string into , for example a code part.

Evaluate( string “ & Code world & “ string )

( The Code world bit here is as [] evaluates ) Another way of looking at it, approximately correct is that [] has restricted us to the middle bit here:

Evaluate(“” & ______ & “”)

Similarly Range( ____ ) is expecting a string argument.

[] is expacting a code thing

If you try to use a Quote in [ ] it will do just as a code does and tell VBA to expect a string, and as in the codes return a simple string rather than evaluating that string

It may also be peculiar to [] that it recognises something like A1 as a range, maybe as it is expecting a code thing.

Codes:

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] strEvalCellContent()
[color=blue]Dim[/color] CellContent [color=blue]As[/color] [color=blue]String[/color]
[color=blue]Dim[/color] strEval [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'String to be built up for Evaluate[/color]
[color=blue]Let[/color] CellContent = Evaluate("A1") [color=darkgreen]'This has been evaluated to a range object, hence the next line works[/color]
[color=blue]Let[/color] CellContent = Evaluate("A1").Value [color=darkgreen]'(The line above was an allowed Implicit of this line, that of getting the value when assinging  variable to a Cell Range)[/color]
[color=darkgreen]'Let CellContent = Evaluate(A1) ' This errors as it is expecting a string but sees an undefined, unfilled variable[/color]

[color=blue]Let[/color] CellContent = ["Al"] [color=darkgreen]'Gives "A1" and not cell contents[/color]
[color=darkgreen]'Let CellContent = [Al]'Gives incompatible types and..[/color]
[color=blue]Let[/color] CellContent = [A1].Value [color=darkgreen]'...this indicates that [] has evaluated to a range object[/color]

[color=blue]Let[/color] strEval = "A1"
[color=blue]Let[/color] CellContent = Evaluate(strEval)
[color=blue]Let[/color] CellContent = [strEval]

[color=blue]Let[/color] strEval = "A" & 1 & ""
[color=blue]Let[/color] CellContent = Evaluate(strEval)
[color=blue]Let[/color] CellContent = [strEval] [color=darkgreen]'Gives "A1" and not cell contents[/color]

[color=blue]Let[/color] CellContent = Evaluate("A" & 1 & "")
[color=blue]Let[/color] CellContent = Evaluate("A" & 1 & "").Value
[color=blue]Let[/color] CellContent = ["A" & 1 & ""] [color=darkgreen]'Gives "A1" and not cell contents[/color]

[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
[color=blue]Sub[/color] strEvalRangeSelect()
[color=blue]Dim[/color] strEval [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'String to be built up for Evaluate[/color]

Range("A1").Select
Evaluate("A2").Select
[A1].Select [color=darkgreen]'Note: This will not work ["A2"].Select. Error says Requires Object[/color]

[color=blue]Let[/color] strEval = "A2"
Range(strEval).Select

[color=blue]Let[/color] strEval = "A" & 1 & ""
Range(strEval).Select
Evaluate(strEval).Select
Evaluate("A" & 2 & "").Select

[color=darkgreen]'["strEval"].Select 'Run time error Object "Needed" - Here it has been given a String, which you cannot Select[/color]
[color=darkgreen]'[strEval].Select 'Complie error "Invalid identifier" It needs a VBA thing...so[/color]
[color=blue]Dim[/color] rng [color=blue]As[/color] Range: [color=blue]Set[/color] rng = Range("A1") [color=darkgreen] '.. here we define one ( identify rng ) as a Range Object ..and...[/color]
[rng].Select [color=darkgreen]' this will work...![/color]

[color=blue]End[/color] [color=blue]Sub[/color]

Not sure if that helps a bit. It is not the full story, and all a bit complicated and tied up with the confusing world of “Quotes in VBA”. Maybe a bit more detailed explanations here:
http://www.mrexcel.com/forum/excel-questions/696820-quotes-visual-basic-applications.html
Special concatenation - Page 3


Alan

 

[shg]

You can't build up the address string 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.

Also, using the brackets adds a function (and an out-of-process call at that) where none is needed. All else being equal, the more that gets done by the compiler (versus at runtime), the faster code will run.

 

[DocAElstein]

Hi

Also, using the brackets adds a function (and an out-of-process call at that) where none is needed. All else being equal, the more that gets done by the compiler (versus at runtime), the faster code will run.

Sorry but I am not sure what you are comparing with what?
Which brackets are you talking about here? What is hard coding? What is or isn’t being done at compile time.
I mean how does that fit in with

.....
Range("A1:A5").select
vs
[A1:A5].select
What are the advantages/disadvantages to using this method ?

Thanks
Alan

 

[shg]

[A1:A5].Select generates an unnecessary call to the Evaluate function.

Range("A1:A5").Select doesn't.

 

[DocAElstein]

[A1:A5].Select generates an unnecessary call to the Evaluate function.
Range("A1:A5").Select doesn't.

So I suppose
Evaluate("A1:A5").Select
also generates an unnecessary call to the Evaluate function.

So no advantages yet of [], only disadvantages.

 

[shg]

The advantage is that it's compact.

Evaluate("A1:A5").Select is just silly.

 

[DocAElstein]

OK

The advantage is that it's compact.
Evaluate("A1:A5").Select is just silly.

_ ... I guess it is a case of “compact” / “Silly looking” to who / what.
_ - Like with complicated Array codes, what may look complex or “silly” to us but are seen by VBA probably much “simpler” than a more “compact” to us looking one liner code using Objects, Methods, properties etc..
– VBA “sees” sometimes much more complicated things/ code lines behind all those things i guess, and is therefore sometimes slower than with more “complicated to us” looking codes.

_ So bottom line I guess you are saying [] just “looks” compacter to us, but often is to VBA seen more complicated ( and “silly” ) than it “sees” something like Range(“ “)?

Alan

P.s.
So,
Range("A1:A5").select looks silly compared to the “compacter looking”
[A1:A5].select .. but is a bit quicker ( not having the extra unnecessary call to the Evaluate function )
Evaluate("A1:A5").Select Looks silly, and has the extra unnecessary call to the Evaluate function,
_ so is really silly

[A1:A5].select has the extra unnecessary call to the Evaluate function. So VBA sees it as Silly. But it looks compacter to us. So we do not think it is silly ( Or maybe we do, because of that extra unnecessary call to the Evaluate function )

Hence final concise conclusion ( At least in terms of Sillyness ) :-

_ . - Evaluate("A1:A5").Select is just silly.
_ . - [A1:A5].select is Silly and also not silly, depending on your point of view
_ . -Range("A1:A5").select Is mostly not silly , but a bit because it does not look as nice as [A1:A5].select

_ .So we have the Silly aspect answered!

 

[kalak]

You can't build up the address string 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.

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

Range("A1:A5").Name = "x"

[x].Select

where one can use the [ ] notation without hardcoding inside the square brackets.
Obviously of little advantage for just selecting, but occasionally for other uses ...
Easier than writing
Range("x").Select
particularly if the named range exists for other purposes.