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