Alternative for slow loop

[Jonathan78]

Hello all,

The following code loops through a range and does some stuff along the way.
However it's quite slow and am wondering if you know a faster way.

Please Login or Register  to view this content.

[shknbk2]

Try this code. I tested a sample down to row 3000. Running your code took 10 seconds using Debug.Print Now before and after running your code. Mine didn't even register a difference.

I'm also assuming that your second If statement is supposed to look for the offset being less than cell.value. If it is greater than like you have it, that If is never true because it's the same test as the first If statement, and if the first one is true, the values are set to be the same, so the second one is not true anymore.

Turning off ScreenUpdating would also help a little.

Please Login or Register  to view this content.

[mehmetcik]

This Has no loops and therefore should be much faster.

Please Login or Register  to view this content.

You can speed it up by preceding the code with this code:

Please Login or Register  to view this content.

You will need to reverse Optimiser after the code:

Please Login or Register  to view this content.

[Doc.AElstein]

HiJonathan78,
_.. I had started before the better ones came in!! - .. so i finished anyway..!!..

Some general points
What i hear most Profi’s say first is that every time you interact with a worksheet it is like slamming on the brakes.
So if you are playing around with things like values manipulation, as you are, then it is best to try and “capture” as much info from the spreadsheet as you can in one go into an Array, then do as much manipulation there to produce a corresponding Array, or set of Arrays to paste out as much as possible at a time. For most real circuits i have played around with this seems to have been mostly true, but not always

But you have to be very careful. At the end of the day i think it always best to check. One hears for example that Offsets are quite fast ( as the computer VBA Brain “works” in offsets - , so you save it a bit of conversion when you do that rather than giving particular cells ) , as is the pair With End With, both of which you are doing....

I did some time measurements: For this I have had a quick look at
your Code, wof,
An Array version, arh,
and a similar to yours but using cells explicitly rather than offsets version, WC
( EDIT: I included shknbk2, shk, code after i noticed he had posted something just before I was finished )
Edit 2 mehmetcik, mehm i did also
_......................................................................................................


This was my start point ( only first bit shown – I went up to about 65500 rows ). I did the speed test in two situations, one where the conditions were not met to do anything, and one where the data meant that the condition 1 was met to do stuff at every line, that is to say paste a value in to column F. – I did this last bit by putting a row of **1’s in column E ( And as shknbk2 said actually your first condition is the same as the second !! )

Using Excel 2007

Row\Col	C	D	E	F	G	H	I	J	K	L
7	7		7						7
8	8		8						8
9	9		9						9
10	10		10						10
11	11		11						11
12	12		12						12
13	13		13						13

BUFFER

Here some initial results in seconds:

	NotDoingStuff	DoingStuff
App.ScUpdt True	wof 3.6 arh 4.8 WC 6.51 snk 1.9	wof 19.4 arh 4.9 WC 23.67 snk 2.1
App.ScUpdt False	wof 3.5 arh 4.5 WC 6.5 shk 1.9 mehm 3.7	wof 18.5 arh 4.9 WC 24.3 snk 2.1 mehm 4

_............................
Note this is a particular computer at a particular time, and only the relative not the actual times ( in seconds ) are important
You need to do more test with particular data, but clearly the Arrays are better, as is a slight improvement using the Offsets. ( Screen Updating seems unimportant in this case ) ( EDIT: And i have a feeling shknbk2 knows what he is doing!! – think will be spending some time tomorrow looking at how that works!!!! ) (EDIT and the shknbk2 code is amazing! )

_ A few other things..
Also Be a bit careful though when you use worksheet functions, such as .Index, . Match and other things that can take Arrays or ranges as their Arguments. Those seem to have been optimised with Spreadsheet interaction in mind, and i got caught out, speed wise, recently creating Arrays and putting them in. Somehow i think VBA does a “pseudo” convert back to a Range in that case ( and you have the additional problem of the 255 “row” x 65535 “column” limit in the Arrays as arguments in Worksheet Functions, whereas you can use the full spreadsheet range for a range as argument , which for XL 2007 + is insanely large )

Alan

( BTW – That was a mean code to do speed test on.. i do averages... and i missed the point at first that on the first runs doing stuff it changes everything so it don’t need to do stuff next time around!!-
I am a bit slow, and that caught me out at first!! So I have a **Sub OneInE that puts 1’s in E so that the conditions are met to do something. – that needs to be commented out for a test on not doing anything. – BTW it is always good practice to do one run without timing first, as a first run can always give somewhat different results. As i do this then when commenting out the two Sub OneInE the result of the first untimed run is to put the sheet in )


The 4 ( 5 ) codes , I put here . They include the speed test codes i used

http://www.excelforum.com/showthread...t=#post4326240


EDIT 2 !!.. Just caught mehmetcik contribution!!


_...................................
This thread is getting very interesting.. maybe for completeness i will try an Evaluate with Range Tomorrow

[Jonathan78]

Thank you all for your suggestions!
I haven't worked with arrays yet but that seems to be the fastest way.

[Doc.AElstein]

@ Jonathan78
Hi Jonathan78

Thank you all for your suggestions!
......

Glad we could all help. Thanks for the feedback
_..........

........
I haven't worked with arrays yet but that seems to be the fastest way.

_.. If you want to take any of the Array codes further ( I suggest shknbk2’s ), then my latest version of his code with some explaining ‘comments is here:
http://www.excelforum.com/showthread...t=#post4326517

Alan

( P.s. Do not forget what we said about your Wonky second condition which can never be met.!!! .)

[Doc.AElstein]

_.....@ shknbk2 ( or anyone else interested in “Alternative for slow loop” ).....
______................I was really intrigued in the code from shknbk2 and have / am learning a lot from it so I took it a bit further.....

___. Initially I was surprised my code was slower...

_.. Just to update on the Theme of the Thread and the OP requirement..”.. code loops through a range and does some stuff along the way......... it's quite slow and am wondering if you know a faster way...”

_.. Up until now it looks like generally an Array approach is the one to go for..
_.......

_ my Code Captures the whole Worksheet Range Values into one Array in one go, does stuff possibly to That Array, and pastes the Array out again in one go. Sounds good
[COLOR="#0000FF"]Sub[/COLOR ArrayWonks()____( This code takes today 5.4 secs )

_......
_ shknbk2’s code Takes in a couple of Ranges ( Finite length columns ) into Arrays , does stuff in a loop to those two Arrays , increases the size of some new Arrays each time in the loop and puts the stuff possibly done into them instead, ( and modifies the other Arrays possibly ), Does another loop where it transposed both of the new Arrays, then pastes 4 Arrays output. Sounds bad. .....But it ‘aint and is a lot faster than mine Wot a Wonk Poo! ....So i felt like taking it a bit further.... And i did.

_ I ruined the code from shknbk2’s a bit with amongst other things some ‘ green comments, ....
___Sub shkDEarr() ___( This code takes today 2.4 secs )
____.....but it still worked.. and i did so more codes as well as re-running the codes from the earlier posts for a direct comparison of the same day, Same computer, same row numbers etc.. ( and just did the doing stuff versions and did all with .ScreenUpdating = False )

The Codes are all here:
http://www.excelforum.com/showthread...t=#post4326517 ( Speed test Codes also here )

http://www.excelforum.com/showthread...t=#post4326540
http://www.excelforum.com/showthread...t=#post4326570
http://www.excelforum.com/showthread...t=#post4326571


Some results and attemted Explanations

_ 1 ) WorksheetsCellsWonks '________Time Today 23.5 secs.

Full spreadsheet interaction type code. Slowest
_...........................

_2) WorksheetsOffsetWonks'________ 20 secs

Like 1 but using Offsets which seems a bit more suited to VBA / Computer “thinking” maybe...
_...............................

_3 ) ArrayWonks'___________________5.4 secs

My “Simple” “Good” code that wern’t too good
_..............................

_ 4 ) mehmetcik'____________________4.35 secs

Formula alternative

_ 5) shkDEarrAppWTFTrnspus'________3.2 secs

This was one of my experimenting. I took out the Code bit with the Transpose and did a Application.WorksheetFunction.Transpose at the pasting pout stage instead. The results were not too good, compared with the Loop to transpose in the code. This is often commented, that is to say a loop is often quicker than a transpose for an Array, and ties up a bit with my general comments on this in Post #4. Maybe the “Worksheet Function” Transpose is making allowances for transposing some obscure.... things...
_.................................................

_6 ) shkDEarrAppTrnspus'___________3 secs

This does a Application.Transpose instead and is a bit better. Sounds familiar as well:
http://www.excelforum.com/excel-prog...ml#post4199234
_.............................................................

_ 7 ) shkDEarrvar'__________________2.6 secs


This is the basic shknbk2 code but using some Variant Declarations on Variables instead of Strings. Seems to slow things down a bit. ( But that can be very unpredictable and sometimes Variants van give weird better results:
http://www.mrexcel.com/forum/excel-q...lstring-4.html
_................................................

_ 8 ) shkDEarr '_____________________2.4 secs

This is the basic shknbk2 code
And the next few codes were an attempt to understand that a bit..
_..........................................................

_9 ) shkDEarr1Loop'________________2.4 secs

I noticed it was possible to do away with the second ( simple Array Transpose ) Loop by modifying a bit
The line in that loop and sticking it in the first loop. Despite lots of tries, on average, I could not make out any difference from the code before. Maybe this is just saying the Loop for something like a simple “pseudo” 1 D transpose is just so fast it is undistinguishable from doing it “pseudo” without looping it, - that is to say with it doing it in another lop that is already there. If that makes sense? .. It don’t to me, yet. Or maybe it does... Seems ( to me ) like Offsets and Transposing from an original “Thing” with values, by it’s value or referencing where it started was.. is all what the fundamental working of Excel is about, so anything doing that sort of thing is about as efficient as you can get
http://www.eileenslounge.com/viewtopic.php?f=4&t=22534
As it makes the code a lot easier to follow,... ( and get right with some difficult indice ( “offsets” ) ),...then it is probably worth keeping the loop Outside.
_............................................................

_10 ) shkDEarrNoReDim '_____________2.35 secs

Within the first loop, some Arrays are increased in size ( using Redim Perverse ) as the loop continues. In this particular code those Arrays are of known final size, so I modified the code to do without the ReDim. There is hardly any Improvement. ( Even though it is called every time in this particular loop ) I think i noticed sometimes that the ReDim Perverse is very quick, and a lot of Pros seem to use it. In some similar codes where not always an entry made be made in an Array at each loop, this can be very effective in keeping a final Array to just as big as needed. So it is probably, like the transpose loop, a good thing to get in the hang of using.
_......................................................................

_ 11 ) ArrayWonks2'__________________2.45 secs

So based on what all i had learned i started from scratch, and did another Array Code. After i finished i saw it was hardly any different form the shkDEarr original. Maybe the way i brought the Ranges in was not ( as i was hoping ) the best in this case. That method suited better maybe when you are selecting a discontinuous range and is a good way to do that in one go......
http://www.excelforum.com/excel-prog...ml#post4238685
______....... but a .Value Method approach is probably better.
_......................................................................

_ 12 ) ArrayWonks3 '__________________2.35 secs

So this was my final ( as yet ) attempt... Surprise, surprise,.... it looks like the shkDEarr original
_..................................................................

Conclusions?

For now I can only see a few general Guidelines to doing these sort of codes efficiently. It seems that VBA is very efficient at doing stuff in Arrays of one dimension, such that the extra code lines to do that compared with working with a “simpler looking” Bigger Array seems to be worth it. And be a bit careful when using Worksheet functions with Arrays. They can be good at getting a value, or sometimes several values from a range, but the other way around, to get the values out of an array they may not be the best... ( Extracting out values through .Cells ( The whole spreadsheet Range ) seems particularly good sometimes )

Thanks for watching

Alan

[bakerman2]

@ Alan

Worksheet functions with Arrays

This might be interesting for you.

http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.16

[Doc.AElstein]

Hi Bakerman,
Thanks for the Reply.

...
http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.16

That is one of my favourite sites. I have learnt a lot about usong "Worksheet Functions" and Arrays, and Arrays in general from there.
So it is worth bringing it up

Thanks
Alan

[Doc.AElstein]

Hi

I did some further codes here as i was interested in wrapping up the speed test comparisons. ( I am still intrigued why the Array code from shknbk2)is so quick ).
In particular an alternative not yet considered but often given as a novel alternative to looping to speed things up is the Evaluate Range Alternative.

The basic idea there is within VBA to do this, Pseudo code ,

RCell’s Value = Evaluate(“Here you put the formula you would type in a RCell”)

This can be “coerced” to work when RCell is replaced by a Range

_ For a good comparison I wanted to do first a Formula, solution along the lines of that from mehmetcik then “do that” formula in an Evaluate and compare the speed of those with all the other codes so far.
_ I got a bit bogged down as in ignorance i could not understand those mehmetcik Formulas.
_ So I started again with some of mine, not as efficient but just to make a valid comparison in the speed test.

_ I will try to make it a bit easier to follow here, by “farming out” the codes and detailed explanations in the ‘Green comments in the referenced Appendix Posts whichh i refference as I go along.
_.............................................

_ So start again with the OPs original slow ( in a normal loop ) code snippet.:

Row\Col	C	D	E	F	G	H	I	J	K	L	M	N
6			cell	cell.Offset(0, 1).					cell.Offset(0, 6).	cell.Offset(0, 7).	Helper	Helper
7	7		1						7		=E7	=F7

BUFFER

I assume the criteria is actually

10~~~~~~~~If cell.Value < cell.Offset(0, 6).Value Then
20~~~~~~~~~~~~~cell.Value = cell.Offset(0, 6).Value
30~~~~~~~~~~~~~cell.Offset(0, 1).Value = "0:00"
40~~~~~~~~End If


110~~~~~~If cell.Offset(0, 6).Value < cell.Value Then
120~~~~~~~~~~~cell.Offset(0, 6).Value = cell.Value
130~~~~~~~~~~~cell.Offset(0, 7).Value = "23:59"
140~~~~~~End If

Note: I assume the correction of the < rather than > at Line 110
cell is a cell in column E. so taking the example of row 7, these pseudo formulas need to be done in sequence

_Adrs_a ) Developing the basic Spreadsheet Cell Address Type written In Formulas = E7 , If(E7<K7 etc.

10~~~~~~If~~( True~~;~~Do stuff~~;~~Put cell value back in~~- got from helper column cell**** )
20~~~~~~~~~~~~~E7 . Value = If(E7<K7,K7,E7)
30~~~~~~~~~~~~~F7 . Value = If(E7<K7,”0:00”,F7)

110~~~~~~If~~( True~~;~~Do stuff~~;~~Put cell value back in~~- got from helper column cell**** )
120~~~~~~~~~~~K7 . Value = If(K7<E7,K7,K7)
130~~~~~~~~~~~L7~~. Value =If(k7<EZ,”23.59”,L7) But you Note that you must do a Value = .Value thing in between each of those code lines, or the next one will error

**** I do not know of a formula that will leave a cell as it was? I am not sure if this is physically possible,? I think that is a fundamental difference between a Formula and a code? So the logic was a helper column(s) to initially store the value, then a simple formula, pseudo code like thing , where RCell is our cell where we want stuff done or not
So we Put in a Helper column Cell the RCell Value. This requires two lines, one to put = RCell Value, but then importantly always a following line to change the cell from the formula to the value it gives, otherwise the following formula will be indirectly referencing itself – chasing its own tail as it were, which excel will tell you about an and not let you do much else before you sort that out.
We must do all the stages sequentially so we can use the same helper column ( but using different ones will coincidentally be seen to allow putting some formulas in at the same time****)

The above is put into working code ( just for row 7 ) here:
Sub AlansForumlating()
'
http://www.excelforum.com/showthread...=8#post4329168

_...........................................................................

_RC_a) RC equivalent Formulas.

_ The point about using RC formula types is that instead of having for example in Cell B1

~~~~~~~~~=A1
You have a relative reference of
~~~~~~~~~=RC[-1]~~~ ‘ ‘ Here R is the same row~~C is~~1 row “back”
An advantage of this,~~for example,~~is that instead of writing a similar formula of the form in Cell B2
~~~~~~~~~=A2
You use the same formula
~~~~~~~~~=RC[-1]~~~~~~~
So re writing the formulas for 1 row from _ 1) in RC notation we are already get close to getting the equivalent to the code which is working over multi rows.

So I take the basic Formulas from the last code and do a bit of RC - ing about in a row in this code:
Sub AlanRCingInARow()

http://www.excelforum.com/showthread...=8#post4329219

****We see immediately that if we use different helper columns we have the same RC helper Formula, allowing us to simplify the code a bit. This is basically what mehmetcik has done much more effectively to simplify the entire formulas . Unfortunately i am not at a level to be able to do that yet! )

_..................................................................................................

_RC_b) RC ing over a range.

_The next stage is to do a bit of RC ing about over all the rows. The advantage discussed above of the RC notation shows that we are able to just paste the same RC notation formula over the entire range

A couple of codes do this here. They are basically the same codes. The second is tidied up a bit and forms the basis of the ~~~With~~~~End With~~~~RC code equivalent for comparison with the evaluate range codes I will develop in the next sections
'

Sub AlanRCingOverABigRange()

Sub AlanRCingABitBetterOverABigRange()

http://www.excelforum.com/showthread...=8#post4329259

_...........................................................................................................

_Adrs_b) Developing the Normal Address type formulas over multi rows is somewhat more difficult but well worth getting to grips with as reveals a lot of what actually goes on in Excel and can explain a lot of different things at once. It may be really too much here though, so I leave it “Farmed out” here:

At that appendix, There is a
b) (i) discussion of CSE ( Type 2 ) things, ( using parts of spreadsheets effectively as Arrays )
Followed by
b) (ii) trying to do similar to b)(i) within VBA using the VBA Evaluate Method

The end result of all that is the required two extra codes for the speed tests comparisons,
Here they are:

Sub AlanRCingABitBetterOverABigRange()
http://www.excelforum.com/showthread...t=#post4331043
_..........................................
And

Sub AlansEvaluateRange() '
http://www.excelforum.com/showthread...t=#post4331055
_...............................

As i had done along the way to developing that code ( Or rather in the explanations thereof ) a Code which places CSE ( Type 2 ) Forumals in the Worksheet, I included that also just for fun
Sub AlanConShiEntItAllWonk()
http://www.excelforum.com/showthread...t=#post4331058
_..................................

In addition I have redone the timer codes to unify the test conditions a bit.
http://www.excelforum.com/showthread...t=#post4331061
_...........................................

So Finally, Here are some relative results:
'Call WorksheetsCellsWonks '________23
'Call WorksheetsOffsetWonks '________20
'Call AlanRCingABitBetterOverABigRange ' 6.3
'Call AlanConShiEntItAllWonk '_______6.1
'Call ArrayWonks '___________________5.2
'Call AlansEvaluateRange '____________4.9
'Call mehmetcik '____________________4.0
'Call shkDEarrAppWTFTrnspus '________3.2
'Call shkDEarrAppTrnspus '___________2.7
'Call shkDEarrvar '__________________2.75
'Call ArrayWonks2 '__________________2.2
'Call shkDEarr '_____________________2.15
'Call shkDEarr1Loop '________________2.15
'Call shkDEarrNoReDim '_____________2.05
'Call ArrayWonks3 '__________________2.1

Clearly some improvement over my Formulas using Evaluate Range is to be seen but the performance is still below the Array Codes

Possibly applying the code versions of mehmetcik could get closer.

Maybe something for another day!


Thanks for watching
Alan

[Doc.AElstein]

Hi

I did some further codes here as i was interested in wrapping up the speed test comparisons. ( I am still intrigued why the Array code from shknbk2)is so quick ).
In particular an alternative not yet considered but often given as a novel alternative to looping to speed things up is the Evaluate Range Alternative.

The basic idea there is within VBA to do this, Pseudo code ,

RCell’s Value = Evaluate(“Here you put the formula you would type in a RCell”)

This can be “coerced” to work when RCell is replaced by a Range

_ For a good comparison I wanted to do first a Formula, solution along the lines of that from mehmetcik then “do that” formula in an Evaluate and compare the speed of those with all the other codes so far.
_ I got a bit bogged down as in ignorance i could not understand those mehmetcik Formulas.
_ So I started again with some of mine, not as efficient but just to make a valid comparison in the speed test.

_ I will try to make it a bit easier to follow here, by “farming out” the codes and detailed explanations in the ‘Green comments in the referenced Appendix Posts whichh i refference as I go along.
_.............................................

_ So start again with the OPs original slow ( in a normal loop ) code snippet.:

Row\Col	C	D	E	F	G	H	I	J	K	L	M	N
6			cell	cell.Offset(0, 1).					cell.Offset(0, 6).	cell.Offset(0, 7).	Helper	Helper
7	7		1						7		=E7	=F7

BUFFER

I assume the criteria is actually

10~~~~~~~~If cell.Value < cell.Offset(0, 6).Value Then
20~~~~~~~~~~~~~cell.Value = cell.Offset(0, 6).Value
30~~~~~~~~~~~~~cell.Offset(0, 1).Value = "0:00"
40~~~~~~~~End If


110~~~~~~If cell.Offset(0, 6).Value < cell.Value Then
120~~~~~~~~~~~cell.Offset(0, 6).Value = cell.Value
130~~~~~~~~~~~cell.Offset(0, 7).Value = "23:59"
140~~~~~~End If

Note: I assume the correction of the < rather than > at Line 110
cell is a cell in column E. so taking the example of row 7, these pseudo formulas need to be done in sequence

_Adrs_a ) Developing the basic Spreadsheet Cell Address Type written In Formulas = E7 , If(E7<K7 etc.

10~~~~~~If~~( True~~;~~Do stuff~~;~~Put cell value back in~~- got from helper column cell**** )
20~~~~~~~~~~~~~E7 . Value = If(E7<K7,K7,E7)
30~~~~~~~~~~~~~F7 . Value = If(E7<K7,”0:00”,F7)

110~~~~~~If~~( True~~;~~Do stuff~~;~~Put cell value back in~~- got from helper column cell**** )
120~~~~~~~~~~~K7 . Value = If(K7<E7,K7,K7)
130~~~~~~~~~~~L7~~. Value =If(k7<EZ,”23.59”,L7) But you Note that you must do a Value = .Value thing in between each of those code lines, or the next one will error

**** I do not know of a formula that will leave a cell as it was? I am not sure if this is physically possible,? I think that is a fundamental difference between a Formula and a code? So the logic was a helper column(s) to initially store the value, then a simple formula, pseudo code like thing , where RCell is our cell where we want stuff done or not
So we Put in a Helper column Cell the RCell Value. This requires two lines, one to put = RCell Value, but then importantly always a following line to change the cell from the formula to the value it gives, otherwise the following formula will be indirectly referencing itself – chasing its own tail as it were, which excel will tell you about an and not let you do much else before you sort that out.
We must do all the stages sequentially so we can use the same helper column ( but using different ones will coincidentally be seen to allow putting some formulas in at the same time****)

The above is put into working code ( just for row 7 ) here:
Sub AlansForumlating()
'
http://www.excelforum.com/showthread...=8#post4329168

_...........................................................................

_RC_a) RC equivalent Formulas.

_ The point about using RC formula types is that instead of having for example in Cell B1

~~~~~~~~~=A1
You have a relative reference of
~~~~~~~~~=RC[-1]~~~ ‘ ‘ Here R is the same row~~C is~~1 column “back”
An advantage of this,~~for example,~~is that instead of writing a similar formula of the form in Cell B2
~~~~~~~~~=A2
You use the same formula
~~~~~~~~~=RC[-1]~~~~~~~
So re writing the formulas for 1 row from _ 1) in RC notation we are already get close to getting the equivalent to the code which is working over multi rows.

So I take the basic Formulas from the last code and do a bit of RC - ing about in a row in this code:
Sub AlanRCingInARow()

http://www.excelforum.com/showthread...=8#post4329219

****We see immediately that if we use different helper columns we have the same RC helper Formula, allowing us to simplify the code a bit. This is basically what mehmetcik has done much more effectively to simplify the entire formulas . Unfortunately i am not at a level to be able to do that yet! )

_..................................................................................................

_RC_b) RC ing over a range.

_The next stage is to do a bit of RC ing about over all the rows. The advantage discussed above of the RC notation shows that we are able to just paste the same RC notation formula over the entire range

A couple of codes do this here. They are basically the same codes. The second is tidied up a bit and forms the basis of the ~~~With~~~~End With~~~~RC code equivalent for comparison with the evaluate range codes I will develop in the next sections
'

Sub AlanRCingOverABigRange()

Sub AlanRCingABitBetterOverABigRange()

http://www.excelforum.com/showthread...=8#post4329259

_...........................................................................................................

_Adrs_b) Developing the Normal Address type formulas over multi rows is somewhat more difficult but well worth getting to grips with as reveals a lot of what actually goes on in Excel and can explain a lot of different things at once. It may be really too much here though, so I leave it “Farmed out” here:

At that appendix, There is a
b) (i) discussion of CSE ( Type 2 ) things, ( using parts of spreadsheets effectively as Arrays )
Followed by
b) (ii) trying to do similar to b)(i) within VBA using the VBA Evaluate Method

The end result of all that is the required two extra codes for the speed tests comparisons,
Here they are:

Sub AlanRCingABitBetterOverABigRange()
http://www.excelforum.com/showthread...t=#post4331043
_..........................................
And

Sub AlansEvaluateRange() '
http://www.excelforum.com/showthread...t=#post4331055
_...............................

As i had done along the way to developing that code ( Or rather in the explanations thereof ) a Code which places CSE ( Type 2 ) Forumals in the Worksheet, I included that also just for fun
Sub AlanConShiEntItAllWonk()
http://www.excelforum.com/showthread...t=#post4331058
_..................................

In addition I have redone the timer codes to unify the test conditions a bit.
http://www.excelforum.com/showthread...t=#post4331061
_...........................................

So Finally, Here are some relative results:
'Call WorksheetsCellsWonks '________23
'Call WorksheetsOffsetWonks '________20
'Call AlanRCingABitBetterOverABigRange ' 6.3
'Call AlanConShiEntItAllWonk '_______6.1
'Call ArrayWonks '___________________5.2
'Call AlansEvaluateRange '____________4.9
'Call mehmetcik '____________________4.0
'Call shkDEarrAppWTFTrnspus '________3.2
'Call shkDEarrAppTrnspus '___________2.7
'Call shkDEarrvar '__________________2.75
'Call ArrayWonks2 '__________________2.2
'Call shkDEarr '_____________________2.15
'Call shkDEarr1Loop '________________2.15
'Call shkDEarrNoReDim '_____________2.05
'Call ArrayWonks3 '__________________2.1

Clearly some improvement over my Formulas using Evaluate Range is to be seen but the performance is still below the Array Codes

Possibly applying the code versions of mehmetcik in Evalute Range could get closer.

Maybe something for another day!


Thanks for watching
Alan