Looping Vs Non Looping approach

[shrivallabha]

Please refer this thread:
http://www.vbaexpress.com/forum/showthread.php?t=39777
I created a new thread so that the original thread discussion doesn't meander!

As you can see, both approaches are likely to work.
App #1] Looping through the range one by one.
App #2] Assigning a formula and converting it to values.

What I'd like to understand is:
What criteria shall a person (I don't consider myself a programmer for lack of skills) apply to select the approach out of these two? Do we have to test these based on time-they-take trials every time or is there any thumb rule to this (I am keeping out personal preference)?

Of course, I realize that the coding's end result shall be reduced human effort and accurate results.

[mdmackillop]

Have look at Charles Williams' site here regarding speeding up code.

[Paul_Hossler]

My 2 cents ....

In general,

1. Unless there's MAJOR performance concern, I'd opt for streight-forward read-able code, even if there's a small performance hit. I think the rule of thumb is that speed has to double before it 'feels' faster to the user

2. Get the program working first, and then look for MAJOR bottlenecks if the overall performance is not acceptable

3. It's always worth spending some extra time to properly structure and maintain the structure so that the code is easily readable and maintainable

4. Almost all of my significant improvements have been from using a better designed algorithm, a more appropriate function or a built-in Excel capability instead of my own VBA.

5. VBA is not VB. VBA works with the MS Office object models. I've seen many macros that are structured as if they were intended to be stand-alone executable .EXE's, and really did not use the power of Excel (e.g. wrote their own routine with For loops to do what Excel does already)

Just my 'rules' and I break them occationally of course, but I like to use them as a check just to make sure that I really do have a reason when I break them

Random thoughts ...

Paul

[Aflatoon]

As a general rule of thumb, do not loop through cells, reading and writing each one in turn. Pretty much any other (reasonable) approach will be faster - the most common being:
1. Load the whole range into an array, process that array and then output the results to the worksheet in one pass.
2. Add a formula to the entire range in one go and then convert to values, again in one go.
3. Use evaluate to shortcut version 2.

There may be times that you do need to loop one cell at a time but they are, in my experience at least, quite rare.

[shrivallabha]

Thank you for your replies.

I read (& tried to understand) information on DecisionModels.com. It is little beyond my reach at the moment.

If read-ability is criterion then I can read (understand) the standard loops more easily than the non-looping ones. Almost hands down choice would be (for me) loops.

This thought started recurring after I saw mickericson's replies (here & mrexcel), some of Aflatoon's & Bob's replies here. And there was one particular thread started by Paul (lot of data and looping taking time).

Up till now, I have almost always used loops. So before spending some time on non-loopers, I wanted hear from you guys. I appreciate your inputs and time.

I have a query:
When we use a non-loop, where we use xlFormulas, how much bearing does the application's calculation time (result evaluation) will have on processing time?

[Aflatoon]

Looping is ok if you loop through an array rather than reading/writing one cell at a time.
If you use formulas then the bulk of your processing time will be Excel's calculation time.

[shrivallabha]

I was googling for this. Most have recommended loading range data into array and processing.

Can you please explain why array processing is faster for the same size of data?

[Aflatoon]

I believe it is because all the processing occurs in memory and within one library (the VBA dll) and there are only two cross-library communications required - reading from the range at the beginning and writing back to it at the end.