Square brackets to specify a range

  • Thread starter Thread starter IanC
  • Start date Start date
I

IanC

In a recent response to another query, the solution featured square
brackets. Wishing to understand more, I delved into the VBA Help file to
find that it equates to "Evaluate" and suggested that a range could be
referred to as [A1] instead of Range("A1").

In a workbook with many instances of Range statements, substituting square
brackets will significantly reduce the number of characters in the code (and
therefore the file size), but is there a downside to this. Does the method
use more resources, or take longer to run?

Many thanks
 
IanC formulated the question :
In a recent response to another query, the solution featured square brackets.
Wishing to understand more, I delved into the VBA Help file to find that it
equates to "Evaluate" and suggested that a range could be referred to as [A1]
instead of Range("A1").

In a workbook with many instances of Range statements, substituting square
brackets will significantly reduce the number of characters in the code (and
therefore the file size), but is there a downside to this. Does the method
use more resources, or take longer to run?

Many thanks
Click to expand...

The downside is that VBA has to analize what's between the brackets in
order to "evaluate" the value. Specifying Range("A1") is faster and
uses less resources at runtime.<IMO><g>
 
Yes.

Extract from a post by Tushar Mehta (six years ago)
'Start quote...
10,000 loops consisting of 4 statements, each setting the same variable
to a different range:
Time Rank
Set r = Range("$A$1", "$A$1") 0.090113 1
Set r = Range("A1", "A1") 0.1058 2

Set r = Range("$A$1") 0.177712 3
Set r = Range("A1") 0.180887 4
Set r = Cells(1, 1) 0.19815 5

Set r = Cells(1, "A") 0.308837 6
Set r = [A1] 0.621438 7

[The times are the average over 10 cycles and represent the time for
40,000 Set operations and one procedure call, since each test was in a
separate procedure.]
For me most of the results were a surprise.
Range(x,x) was faster than Range (x) by a factor of just under 50%
Cells(m,n) was slower than Range(x) by a bit
[x] was slower than Range(x) by a factor of 4
Cells(m,"x") was even possible, and slower than Cells(m,n) by 50%!
'End Quote
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL

..
..
..

"IanC" <[email protected]>
wrote in message
In a recent response to another query, the solution featured square
brackets. Wishing to understand more, I delved into the VBA Help file to
find that it equates to "Evaluate" and suggested that a range could be
referred to as [A1] instead of Range("A1").

In a workbook with many instances of Range statements, substituting square
brackets will significantly reduce the number of characters in the code (and
therefore the file size), but is there a downside to this. Does the method
use more resources, or take longer to run?
Many thanks
 
It happens that Jim Cone formulated :
Yes.

Extract from a post by Tushar Mehta (six years ago)
'Start quote...
10,000 loops consisting of 4 statements, each setting the same variable
to a different range:
Time Rank
Set r = Range("$A$1", "$A$1") 0.090113 1
Set r = Range("A1", "A1") 0.1058 2

Set r = Range("$A$1") 0.177712 3
Set r = Range("A1") 0.180887 4
Set r = Cells(1, 1) 0.19815 5

Set r = Cells(1, "A") 0.308837 6
Set r = [A1] 0.621438 7

[The times are the average over 10 cycles and represent the time for
40,000 Set operations and one procedure call, since each test was in a
separate procedure.]
For me most of the results were a surprise.
Range(x,x) was faster than Range (x) by a factor of just under 50%
Cells(m,n) was slower than Range(x) by a bit
[x] was slower than Range(x) by a factor of 4
Cells(m,"x") was even possible, and slower than Cells(m,n) by 50%!
'End Quote
Click to expand...

Hi Jim,
That's the first time I've ever seen a timed test result for this.
Thank you; much appreciated!
 
In message <[email protected]> of Fri, 20 Aug 2010
15:52:07 in microsoft.public.excel.programming, GS
It happens that Jim Cone formulated :
Yes.

Extract from a post by Tushar Mehta (six years ago)
'Start quote...
10,000 loops consisting of 4 statements, each setting the same variable
to a different range:
Time Rank
Set r = Range("$A$1", "$A$1") 0.090113 1
Set r = Range("A1", "A1") 0.1058 2
Click to expand...
[snip]
'End Quote
-- Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL
Click to expand...
Click to expand...

What is this? I followed the link, but did not download as I had no
notion what you were giving.
Hi Jim,
That's the first time I've ever seen a timed test result for this.
Thank you; much appreciated!
Click to expand...

Hear hear. I also thank IanC for the question.
I had learned [A1] was "bad", but had no idea of relative speed.

Lacking a "lint" <http://en.wikipedia.org/wiki/Lint_(software)> for
VBA, can somebody point to a list of expensive technique alternatives?

Is the original post online?
I got 98 hits, none of which seemed relevant with <http://groups.google.
com/groups/search?as_q=A1+cells+range+&as_epq=&as_oq=&as_eq=&num=100&sco
ring=d&lr=&as_sitesearch=&as_qdr=&as_mind=1&as_minm=1&as_miny=2010&as_ma
xd=1&as_maxm=1&as_maxy=2010&as_ugroup=&as_usubject=&as_uauthors=Tushar+M
ehta&safe=off>

Most of my code parses web output. I believe the Internet access time
dominates, but have not profiled the code. (Some 5+ second accesses.)
Amdahl's Law applies <http://en.wikipedia.org/wiki/Amdahls_law>

I use the Document Output Model (DOM) to analyse output from connections
with CreateObject("InternetExplorer.Application"). I have not bothered
to find a method which avoids IE. I do use the registry to avoid
downloading pictures as that can lead to random widely-variable timings.

Has anybody got suggestions on practical Excel profiling? (I use 2003)
 
Hear hear. I also thank IanC for the question.
I had learned [A1] was "bad", but had no idea of relative speed.
Click to expand...

Is bad if used to ref a cells range, but is ok to ref a named range
(MS docet).
Sorry, i not speak english so well to explain.


Bye!
Scossa
 
Walter,

Re: "What is this? http://tinyurl.com/ExtrasForXL
I followed the link, but did not download as I had no notion what you were giving."

The explanatory details at the MediaFire website don't always show.
Hide/show details is listed at the left side of the web page
"Extras for Excel" is a commercial add-in that adds additional features to Excel...
"Table of Contents (with links), Sort Sheets (in true alpha/numeric order), Clean Data, Insert Rows, _
Format Fonts, Update Recent Files List, Classic menu for XL2007, Five new functions and more"

The entire list of add-ins and some free stuff are at:
http://www.mediafire.com/PrimitiveSoftware

'-----
Re: "Is the original post online?"
I don't believe so. I posted most of it.

'-----
Re: "Has anybody got suggestions on practical Excel profiling? (I use 2003)"
No, but some code to test execution speed would look something like this...

Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub HowLongHeAsked()
Dim lngRw As Long
Dim Time1 As Long
Dim Time2 As Long
Dim rngTest As Excel.Range
Dim x As Variant

'a single cell
Set rngTest = Range("Sludge")
Time1 = timeGetTime

For lngRw = 1 To 50000
'x = rngTest.Value
x = Range("Sludge").Value
Next

Time2 = timeGetTime
MsgBox Format((Time2 - Time1) / 1000, "###,0.0##")
End Sub
 
Hear hear. I also thank IanC for the question.
I had learned [A1] was "bad", but had no idea of relative speed.
Click to expand...

Is bad if used to ref a cells range, but is ok to ref a named range
(MS docet).
Sorry, i not speak english so well to explain.

Bye!
Scossa
Click to expand...

Hi Scossa.
We must urgently inform someone (Mr.Bruno).:-))
Saluti
Eliano
 
You want me die! :-)))

Bye!
Scossa
Click to expand...

Absolutely not! Some time ago, that matter was the subject
of conflict between Mauro and Bruno, and as you can see,
their current relationships are still friendly.
It was just for a smile.
However, the Tushar_Mehta_test reported by Jim Cone seems
very interesting and will be checked, although unfortunately
I cannot find the original post.
So long, Scossa, so long
Eliano
 
Has to be Seconds. There is hardly anything going on inside the loops.
--
Jim Cone
Portland, Oregon USA

..
..

"IanC" <[email protected]>
wrote in message Hi Jim
Any idea what the time units are?
 
Hi Jim

I guessed it might be, but wanted to be sure. So if 40000 operations are
taking less than a second, the time taken in the case of my code is
insignificant. I just wanted to be sure it was seconds and not minutes, or
even hours!

--
Ian
--

Jim Cone said:
Has to be Seconds. There is hardly anything going on inside the loops.
--
Jim Cone
Portland, Oregon USA

.
.

"IanC" <[email protected]>
wrote in message Hi Jim
Any idea what the time units are?
--
Ian
--

"Jim Cone" <[email protected]>
wrote in message
Yes.

Extract from a post by Tushar Mehta (six years ago)
'Start quote...
10,000 loops consisting of 4 statements, each setting the same variable
to a different range:
Time Rank
Set r = Range("$A$1", "$A$1") 0.090113 1
Set r = Range("A1", "A1") 0.1058 2

Set r = Range("$A$1") 0.177712 3
Set r = Range("A1") 0.180887 4
Set r = Cells(1, 1) 0.19815 5

Set r = Cells(1, "A") 0.308837 6
Set r = [A1] 0.621438 7

[The times are the average over 10 cycles and represent the time for
40,000 Set operations and one procedure call, since each test was in a
separate procedure.]
For me most of the results were a surprise.
Range(x,x) was faster than Range (x) by a factor of just under 50%
Cells(m,n) was slower than Range(x) by a bit
[x] was slower than Range(x) by a factor of 4
Cells(m,"x") was even possible, and slower than Cells(m,n) by 50%!
'End Quote
Click to expand...
Click to expand...
 
In addition to the execution time, there's a chance of incurring
substantial redevelopment time down the road if you want to adapt the
code to working on other than the active sheet. It's relatively easy to
change Range("A1") to Worksheets("My Sheet").Range("A1"), but changing
[A1] to Worksheets("My Sheet").Range("A1") takes more effort, and
cognitive effort at that.

I only use bracketed expressions in the immediate window. Otherwise I
reference things the obsessive-compulsive way, often to the point of
naming the sheet even if it's the active sheet.

Don't assume the defaults.

- Jon
 
Back
Top