Loopy loops 2

[ClulessChris]

In thread707-1407494 Change1234 indicated a lesser perormace from a For To Next loop than other loops such a Do Until.
I've not heard this before, and don't understand why a for loop would be less efficent.
Is this the case?

Everybody is somebodys Nutter.

 

[rmikesmith]

You would typically use For..Next and Do..Until (or Do.. While) loops in different situations. Obviously, the For..Next construct lends itself to situations where you need to iterate a known number of times (over all of an array's subscripts, for example). A Do..While would be used when you need to test a more general exit condition (e.g. String1 = String2).

I did some performance timing of For..Next and Do..Until based on the same number of iterations and the For..Next was slightly faster. This was a few tenths of a second over 1M iterations, so for most practical situations there would be very little difference. I simply use the loop construct that makes the most sense and is clearest for a given situation.


Regards,
Mike

 

[rmikesmith]

See also Firefytr's reply in the referenced thread. He conducted some timing tests, as well.

Mike

 

[ClulessChris]

Many thanks guys. Just checking if I was missing something.

Everybody is somebodys Nutter.

 

[firefytr]

Speeds are insubstantial. They are methods, so it only depends on how the methods are used. For example, if you load a byte array of data and loop through it, as compared to creating a range union in a loop, the array will be faster always because you're accessing objects, not to mention using the union in a loop (very expensive memory-wise). They key is to, as Mike says, use the right tool for the right job. All work in the same manner, just in different ways.

The For/Next loops are great because they have boundaries. You have a starting point and an ending point. You tell it exactly when to start and stop, and you can exit anytime you want with whatever given conditions you set forth (see Exit For).

A Do/Loop is great because you can use it as a conditional loop, such as a dynamic range or dynamic list. You can use it to loop through all of your data until it gets to the end, even if you do not know where that end will be, by specifying it in the Do (see Do Until).

You can also use the While/Wend loop while something is currently going on or another process is running. You might see this while you are waiting on a process such as a web browser to load up and navigate to a web page. Each computer/connection will give you different speeds and you cannot write VBA code effectively if you do not wait for a web page to load. Not if you want to manipulate via code that is. So the While/Wend is great for that, even a Do Until/Loop is good for that.

These are comparable to the rounding functions Excel has, there are many. RoundUp, RoundDown, Trunc, Round, etc. What do they all do? They all round. They just go about it in slightly different ways. The loop tools (methods) are build on the same principles, they just implore different techniques/tactics.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci