VBA Syntax uses only [ ]

[Biz]

Quick VBA question.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Syntax below work<o></o>
Method 1

With Range("L2:L" & LR)<o:p></o:p>
                .Formula = "=LEFT(M2,SEARCH(""_"",M2)-1)"<o:p></o:p>
                .Value = .Value<o:p></o:p>
End With<o:p></o:p>

<o> </o>
Tried changing it to <o></o>
<o> Method 2</o>

With [L2:L & LR]<o:p></o:p>
                .Formula = "=LEFT(M2,SEARCH(""_"",M2)-1)"<o:p></o:p>
                .Value = .Value<o:p></o:p>
End With<o:p></o:p>

<o> </o>
I get Error 2029.<o></o>
<o> </o>
I am trying to remove range and use [] to make code shorter.<o></o>

What code modification must I make to make Method 2 work with square brackets []?

Biz

 

[gregtx81]

I believe the info inside brackets is literal-- you can't use/build strings.

Check out documentation for worksheet.evaluate method.

 

[GTO]

Hi Biz,

I agree with Greg. I do not believe you can supply a string argument to Evaluate using the shorthand notation (the brackets). In reading the help topic, I would suggest disregarding the part about the brackets making the code shorter. When I started using vba, I thought that looked neat too, but you will quickly see the downsides to using Evaluate just to refer to a Range.

Evaluate is not without its costs.

Each time it is called, it must first decide whether it is returning an object or a value. That slows things down.

Further - you lose intellisense.

Overall, IMO, while Evaluate can be really neat for occasional use, it is no panacea.

 

[Biz]

Gregtx81 & Mark,

Thanks for your feedback. If understand right using [] does slow vba code?
Thanks I did not know dat.

Biz

 

[Michael M]

In addition to Gregs comments, which are correct, that you cannot use variables inside the square brackets.
While removing range will make the code shorter, it's not encouraged because of readability. Not a lot of people use the shorter version. So, if it's strictly for you only, go right ahead, but others may not follow the code.
You will also find that at some point you will have to use range for your code and then you have an issue with using different types in different places
Have a quick read here for a summary of it's use

http://support.microsoft.com/kb/104502