Good Day,

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?

Probably should discuss this in the thread in question eh,
but, why not? The madness to the method was for the sake of
supplying functional vba. Obviously I'm using Excel
Object's native Transpose Function, as I'm trying to stay
away from a loop. Here's the good and bad news:

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

Is functional on my PC, and yes, much more efficient than
flipping an array twice. Each flip could be costing you
100% lead-time... But it's not dynamic as such, which may
or may not be fine, so be it.

Dim myArr2 As Variant
myArr2 = Sheets(2).Range("C6:IV6").Value
Sheets(1).Range("A2").Resize(, UBound(myArr2)).Value = myArr2

I would love to pursue this, but it doesn't work with my
software (Excel '00 SP2). I simply get the first element
returned to A2 in my destination sheet. Flip it a few
times, and you're in like Flynn. A performance hit, but it
actually does what it's intended to do.

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

Note, the only material difference in MS' code and the code
I provided is the accounting for overhead, ~19
microseconds. What is a bit of an issue here is that we're
talking apples to oranges in a certain respect. Using MS'
timing procedure, we'll add a couple of api calls to the
kernel in a module:

Declare Function QueryPerformanceCounter _
Lib "kernel32" ( _
X As Currency) As Boolean

Declare Function QueryPerformanceFrequency _
Lib "kernel32" ( _
X As Currency) As Boolean

Now we'll look at single assignments with:

Private Sub Time_Addition1()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency, A As Long
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop

A = Range("A1").Value
Rem A = [A1].Value
Rem A = Cells(1, 1).Value

QueryPerformanceCounter Ctr2
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
End Sub

I run this 53 times for each, and here are the averages I'm
looking at:

Range Object: 9.28E-05 Seconds
Evaluate Method: 1.31E-04 Seconds
Cells Property: 7.45E-05 Seconds

So, with the single assignment, I get a 41.5%
performance-hit variance with the Evaluate Method relative
to the Range Object (a difference of .000039 seconds), and
I get a 24.6% performance-hit variance with the Range
Object relative to the Cells Property.

Now we loop 20,000 times:

Private Sub Time_Addition2()
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 20000
Rem A = Range("A1").Value
Rem A = [A1].Value
Rem A = Cells(1, 1).Value
Next

QueryPerformanceCounter Ctr2
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
End Sub

I run this 35 times for each, and here are the averages I'm
looking at:

Range Object: ~.289 Seconds
Evaluate Method: ~.539 Seconds
Cells Property: ~.276 Seconds

I see a 186.8% performance-hit variance with the Evaluate
Method relative to the Range Object (a difference of .25
seconds), and I get a 4.4% performance-hit variance with
the Range Object relative to the Cells Property.

So, it appears the performance of various approaches aren't
moving linearly together, and the Evaluate Method's
performance decays to a greater extent with application
relative to the Range Object. As 41.5% and 186.8% don't
strike me as small variances, I would have to say, the
short hand isn't worth it...

As for the Evaluate Method being ugly with respect to Range
References, I under the opposite impression; I think it
actually looks better than longer reference approaches. I
tend to like shorter code if the performance holds up. And
yes, based on these numbers, it is abusive with VBA, while
being less abusive on my keyboard. It seems one's loss is
another gain. <g

Regards,
Nate Oliver

There was a typo on the Array approach.

These were fine in Excel 97 and later:

Sub Tester1()
Dim myArr2 As Variant
myArr2 = Sheets(2).Range("C6:IV6").Value
Sheets(1).Range("A2").Resize(, UBound(myArr2, 2)).Value = myArr2

cols = 254
Sheets(1).Range("A4").Resize(1, cols).Value = _
Sheets(2).Range("C6").Resize(1, cols).Value

End Sub

But it's not dynamic as such

as written no it wasn't, but it can be as dynamic as any other method.
Limited only by the imagination.

--
Regards,
Tom Ogilvy



"Nate Oliver" wrote in message
...
Good Day,

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?

Probably should discuss this in the thread in question eh,
but, why not? The madness to the method was for the sake of
supplying functional vba. Obviously I'm using Excel
Object's native Transpose Function, as I'm trying to stay
away from a loop. Here's the good and bad news:

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

Is functional on my PC, and yes, much more efficient than
flipping an array twice. Each flip could be costing you
100% lead-time... But it's not dynamic as such, which may
or may not be fine, so be it.

Dim myArr2 As Variant
myArr2 = Sheets(2).Range("C6:IV6").Value
Sheets(1).Range("A2").Resize(, UBound(myArr2)).Value = myArr2

I would love to pursue this, but it doesn't work with my
software (Excel '00 SP2). I simply get the first element
returned to A2 in my destination sheet. Flip it a few
times, and you're in like Flynn. A performance hit, but it
actually does what it's intended to do.

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

Note, the only material difference in MS' code and the code
I provided is the accounting for overhead, ~19
microseconds. What is a bit of an issue here is that we're
talking apples to oranges in a certain respect. Using MS'
timing procedure, we'll add a couple of api calls to the
kernel in a module:

Declare Function QueryPerformanceCounter _
Lib "kernel32" ( _
X As Currency) As Boolean

Declare Function QueryPerformanceFrequency _
Lib "kernel32" ( _
X As Currency) As Boolean

Now we'll look at single assignments with:

Private Sub Time_Addition1()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency, A As Long
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop

A = Range("A1").Value
Rem A = [A1].Value
Rem A = Cells(1, 1).Value

QueryPerformanceCounter Ctr2
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
End Sub

I run this 53 times for each, and here are the averages I'm
looking at:

Range Object: 9.28E-05 Seconds
Evaluate Method: 1.31E-04 Seconds
Cells Property: 7.45E-05 Seconds

So, with the single assignment, I get a 41.5%
performance-hit variance with the Evaluate Method relative
to the Range Object (a difference of .000039 seconds), and
I get a 24.6% performance-hit variance with the Range
Object relative to the Cells Property.

Now we loop 20,000 times:

Private Sub Time_Addition2()
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 20000
Rem A = Range("A1").Value
Rem A = [A1].Value
Rem A = Cells(1, 1).Value
Next

QueryPerformanceCounter Ctr2
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
End Sub

I run this 35 times for each, and here are the averages I'm
looking at:

Range Object: ~.289 Seconds
Evaluate Method: ~.539 Seconds
Cells Property: ~.276 Seconds

I see a 186.8% performance-hit variance with the Evaluate
Method relative to the Range Object (a difference of .25
seconds), and I get a 4.4% performance-hit variance with
the Range Object relative to the Cells Property.

So, it appears the performance of various approaches aren't
moving linearly together, and the Evaluate Method's
performance decays to a greater extent with application
relative to the Range Object. As 41.5% and 186.8% don't
strike me as small variances, I would have to say, the
short hand isn't worth it...

As for the Evaluate Method being ugly with respect to Range
References, I under the opposite impression; I think it
actually looks better than longer reference approaches. I
tend to like shorter code if the performance holds up. And
yes, based on these numbers, it is abusive with VBA, while
being less abusive on my keyboard. It seems one's loss is
another gain. <g

Regards,
Nate Oliver