Tom,

Can you share where you got this timing info. I was particularly interested
to read your comments on the [C3] type syntax. I personally find it ugly and
inelegant in code, but I recall another poster frequently posts an
alternative based upon that style. To hear it is slower is very interesting.

Bob

"Tom Ogilvy" wrote in message
...
do a double transpose in Excel (not VBA) to get a single dimension
horizontal array, when you could just use the original 2D array or do a
straight assignment

Sheets(1).Range("A2:IT2").Value = Sheets(2).Range("C6:IV6").Value

varr = Sheets(2).Range("C6:IV6").Value
Sheets(2).Range("A2").Resize(,Ubound(varr)).Value = varr

Why?

Anyway, I used Microsoft's code for timing and got the following

( 101959058.8107 - 101959076.2179 - 0.0006 ) / 119.318
10000 assignments took 0.145884108013879 seconds ' Evaluate
( 101960815.814 - 101960822.4361 - 0.0006 ) / 119.318
10000 assignments took 5.54945607536164E-02 seconds ' Range

'Ratio
? 0.145884108013879/5.54945607536164E-02
2.62880012081855


Private Sub Time_Addition()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency, A As Long, I As Long
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop
For I = 1 To 10000
A = Range("A1").Value
'A = [A1].Value
'A = Cells(1,1).Value
Next I
QueryPerformanceCounter Ctr2
Debug.Print "("; Ctr1; "-"; Ctr2; "-"; Overhead; ") /"; Freq
Debug.Print "10000 assignments took";
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq; "seconds"
End Sub

http://support.microsoft.com/default...b;en-us;172338
HOWTO: Use QueryPerformanceCounter to Time Code


for cells I got:
( 101992290.0282 - 101992297.9884 - 0.0005 ) / 119.318
10000 assignments took 6.67099683199517E-02 seconds

slightly slower.

--
Regards,
Tom Ogilvy




"Nate Oliver" wrote in message
...
I came up with ~30% time gain with the Range Object over the Evaluate
Method (.000116 seconds) [versus ~1,400%]. Point noted however and note,
it
should vary with the language, I can't necessarily advocate the Method's
use
here, even though it probably isn't going to break the camel's back in a
routine. Also noteworthy at this point, I was able to get a similar pick
up
with the Cells Property over the Range Object @ ~23%. All variance times
noted on 30-run averages per sub.

Public Declare Function QueryPerformanceFrequency _
Lib "kernel32.dll" ( _
lpFrequency As Currency) As Long

Public Declare Function QueryPerformanceCounter _
Lib "kernel32.dll" ( _
lpPerformanceCount As Currency) As Long

Sub EvalMethod()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency, f As Variant
QueryPerformanceFrequency y
QueryPerformanceCounter str

f = [a1].Value

QueryPerformanceCounter fin
n = (fin - str)
Debug.Print Format(n, "##########.############") / y
End Sub

Sub RangeObj()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency, f As Variant
QueryPerformanceFrequency y
QueryPerformanceCounter str

f = Range("a1").Value

QueryPerformanceCounter fin
n = (fin - str)
Debug.Print Format(n, "##########.############") / y
End Sub

Sub CellsProp()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency, f As Variant
QueryPerformanceFrequency y
QueryPerformanceCounter str

f = Cells(1, 1).Value

QueryPerformanceCounter fin
n = (fin - str)
Debug.Print Format(n, "##########.############") / y
End Sub

Note, this does not account for the double-digit microsecond overhead on
the 2nd QueryPerformance call.

Regards,
Nate Oliver

I put my information in the post you are responding to - It showed 2.6 times
slower for [C3]. I got the original figure a while back using a less robust
timer - but I mistated - it wasn't 14 times, but 2 to 3 times, consistent
with the above. I couldn't find my original post where I did that - sorry.
For this timing, the code and the source of the code are in the msg you
responded to. It just makes sense that having to interface into Excel to do
an evaluate and resolve the string to a range reference would be slower than
furnishing a range reference in VBA. Not to knock evaluate or the shorter
square brackets - there are plenty of uses for them, but seems using them
for a range reference is abusive <g.

Tushar did some comparison of Dir, Evaluate, InstrRev and a looping approach
to get a file name stripped from a fully qualified path. Not directly
related, but interesting
http://tinyurl.com/32adx


--
Regards,
Tom Ogilvy



"Bob Phillips" wrote in message
...
Tom,

Can you share where you got this timing info. I was particularly
interested
to read your comments on the [C3] type syntax. I personally find it ugly
and
inelegant in code, but I recall another poster frequently posts an
alternative based upon that style. To hear it is slower is very
interesting.

Bob

"Tom Ogilvy" wrote in message
...
do a double transpose in Excel (not VBA) to get a single dimension
horizontal array, when you could just use the original 2D array or do a
straight assignment

Sheets(1).Range("A2:IT2").Value = Sheets(2).Range("C6:IV6").Value

varr = Sheets(2).Range("C6:IV6").Value
Sheets(2).Range("A2").Resize(,Ubound(varr)).Value = varr

Why?

Anyway, I used Microsoft's code for timing and got the following

( 101959058.8107 - 101959076.2179 - 0.0006 ) / 119.318
10000 assignments took 0.145884108013879 seconds ' Evaluate
( 101960815.814 - 101960822.4361 - 0.0006 ) / 119.318
10000 assignments took 5.54945607536164E-02 seconds ' Range

'Ratio
? 0.145884108013879/5.54945607536164E-02
2.62880012081855


Private Sub Time_Addition()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency, A As Long, I As Long
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop
For I = 1 To 10000
A = Range("A1").Value
'A = [A1].Value
'A = Cells(1,1).Value
Next I
QueryPerformanceCounter Ctr2
Debug.Print "("; Ctr1; "-"; Ctr2; "-"; Overhead; ") /"; Freq
Debug.Print "10000 assignments took";
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq; "seconds"
End Sub

http://support.microsoft.com/default...b;en-us;172338
HOWTO: Use QueryPerformanceCounter to Time Code


for cells I got:
( 101992290.0282 - 101992297.9884 - 0.0005 ) / 119.318
10000 assignments took 6.67099683199517E-02 seconds

slightly slower.

--
Regards,
Tom Ogilvy




"Nate Oliver" wrote in message
...
I came up with ~30% time gain with the Range Object over the Evaluate
Method (.000116 seconds) [versus ~1,400%]. Point noted however and note,
it
should vary with the language, I can't necessarily advocate the Method's
use
here, even though it probably isn't going to break the camel's back in a
routine. Also noteworthy at this point, I was able to get a similar pick
up
with the Cells Property over the Range Object @ ~23%. All variance times
noted on 30-run averages per sub.

Public Declare Function QueryPerformanceFrequency _
Lib "kernel32.dll" ( _
lpFrequency As Currency) As Long

Public Declare Function QueryPerformanceCounter _
Lib "kernel32.dll" ( _
lpPerformanceCount As Currency) As Long

Sub EvalMethod()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency, f As Variant
QueryPerformanceFrequency y
QueryPerformanceCounter str

f = [a1].Value

QueryPerformanceCounter fin
n = (fin - str)
Debug.Print Format(n, "##########.############") / y
End Sub

Sub RangeObj()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency, f As Variant
QueryPerformanceFrequency y
QueryPerformanceCounter str

f = Range("a1").Value

QueryPerformanceCounter fin
n = (fin - str)
Debug.Print Format(n, "##########.############") / y
End Sub

Sub CellsProp()
Dim n As Currency, str As Currency, fin As Currency
Dim y As Currency, f As Variant
QueryPerformanceFrequency y
QueryPerformanceCounter str

f = Cells(1, 1).Value

QueryPerformanceCounter fin
n = (fin - str)
Debug.Print Format(n, "##########.############") / y
End Sub

Note, this does not account for the double-digit microsecond overhead
on
the 2nd QueryPerformance call.

Regards,
Nate Oliver

Thanks Tom.

I saw the bit in the msg, but when you referred to '... I used Microsoft's
code for timing ...' it was that that I was asking about.

2-3 times is not quite so impressive as 14 times, but still a better
justification than my 'I don't like it'.

Thanks again

Bob

"Tom Ogilvy" wrote in message
...
I put my information in the post you are responding to - It showed 2.6
times
slower for [C3]. I got the original figure a while back using a less
robust
timer - but I mistated - it wasn't 14 times, but 2 to 3 times, consistent
with the above. I couldn't find my original post where I did that - sorry.
For this timing, the code and the source of the code are in the msg you
responded to. It just makes sense that having to interface into Excel to
do
an evaluate and resolve the string to a range reference would be slower
than
furnishing a range reference in VBA. Not to knock evaluate or the shorter
square brackets - there are plenty of uses for them, but seems using them
for a range reference is abusive <g.

Tushar did some comparison of Dir, Evaluate, InstrRev and a looping
approach
to get a file name stripped from a fully qualified path. Not directly
related, but interesting
http://tinyurl.com/32adx


--
Regards,
Tom Ogilvy