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

--
Ian
--

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

--
Ian

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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"
wrote in message
news:bPzbo.95173$GQ5.58285@hurricane...
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
--
Ian
--

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
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL

Hi Jim,
That's the first time I've ever seen a timed test result for this.
Thank you; much appreciated!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

In message of Fri, 20 Aug 2010
15:52:07 in microsoft.public.excel.programming, GS
writes
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

[snip]

'End Quote
-- Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL

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!


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_%28software%29 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_min m=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)
--
Walter Briscoe

On 21 Ago, 10:46, Walter Briscoe wrote:

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


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,

"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

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

'-----
"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

--
Jim Cone
Portland, Oregon USA

On 21 Ago, 11:44, Scossa wrote:
On 21 Ago, 10:46, Walter Briscoe wrote:



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

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

Hi Scossa.
We must urgently inform someone (Mr.Bruno).:-))
Saluti
Eliano

On 22 Ago, 02:20, eliano wrote:
Hi Scossa.
We must urgently inform someone (Mr.Bruno).:-))

You want me die! :-)))

Bye!
Scossa

On 22 Ago, 12:02, Scossa wrote:
On 22 Ago, 02:20, eliano wrote:

Hi Scossa.
We must urgently inform someone (Mr.Bruno).:-))

You want me die! :-)))

Bye!
Scossa

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

Hi Jim

Any idea what the time units are?

--
Ian
--

"Jim Cone" 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
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL

.
.
.

"IanC"
wrote in message
news:bPzbo.95173$GQ5.58285@hurricane...
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
--
Ian
--

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

..
..

"IanC"
wrote in message ...
Hi Jim
Any idea what the time units are?
--
Ian
--

"Jim Cone"
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
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL

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" wrote in message
...
Has to be Seconds. There is hardly anything going on inside the loops.
--
Jim Cone
Portland, Oregon USA

.
.

"IanC"
wrote in message ...
Hi Jim
Any idea what the time units are?
--
Ian
--

"Jim Cone"
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
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/ExtrasForXL