best practice for Loops in VBA

[dinakar.yadav]

Hi Guys,
I just want to ask you guys about your experience while using loops. What do you think is most preferred/best way of writing codes involved with loops?

1. using Do while
2. using Do until
3. using for ????

[stnkynts]

My opinion: Usually try to do without a loop. Most things can be done without one. If a loop is required almost always For/Next. Certain situation require Do/Loop While.

[RudiS]

IMHO, each loop type is specific to the scenario you are coding. Although you can tweak the loops so one loop can simulate another, the general idea for having these 3 types of loops are as follows:

A For Each Loop is specific to objects.
For Each cell in Range
For Each sheet in Worksheets
It makes it easy to loop through a collection of objects without having to fuss with counting them.
For Each loops are said to be faster than For loops (with a counter)

A For loop is the most flexible loop structure.
It can loop a set amount of times: For i = 1 to 10
It can simulate a For Each loop: For i = 1 to Worksheets.Count
You can start the loop at any value: For i = 15 to 200 - If you wanted to loop through rows 15 to 200 specifically
You can even loop backward if needed: For i = 100 to 1 Step -1
A very handy loop to know!

Last is the Do Loop - specifically for use when there is no way to know how many times you need to loop!
This loop is conditional. It stops looping while (or until) a condition is TRUE
There are time when you just need your code to continue looping until something meets a condition. This loop type does the job.
Do Until Activecell.Value = 100 ... Loop
Do While Activecell.Value <> 100 ... Loop
Do ... Loop Until/While Activecell = 100 (If you want the code to run AT LEAST once!)
Or a short version: While Activecell<>100 Wend

So, you can choose any loop really, but the choice usually comes down to what your code needs, or what will be most optimal for the code, and to write!
The choice is yours

[dinakar.yadav]

totally agreed Rudi! Nice examples!

[Richard Buttrey]

Not quite what you're asking but since questions about loops are always cropping up first decide whether a loop is the most efficient way of doing what you want. In most cases it isn't. A lot of people invoke loops to test a condition in each cell in a range and then do something like delete that cell's row. In cases like this something like an autofilter with a subsequent block delete is much quicker and more efficient.