Excel & VBA

25 Feb 2018

How to Create Loops in VBA

Posted By

The ability to repeat the same (or similar) actions multiple time until an exit criteria is met is another powerful programming tool. The structues which allow repetition are commonly known as loops. There are 3 loop structures available in VBA, each of which will be discussed below.

For… Next Loops

When the number of repetitions required is known in advance, the preferred loop structure is the For… Next loop. For this structure, you will need to use an explicit loop counter variable (More on Variables Here). This loop counter is incremented between a lower bound and upper bound, executing the code between the For and Next keywords on each iteration. The size of the counter increment can be controlled with an optional step statement; which can be either positive or negative.

The general structure of this type of loop is shown below:

So lets try an put this to use. Let’s say you would like to sum all the ODD values between 1 and 100; we could use loops to achieve this!

In the example we used above, the lower and upper bounds are 1 & 100; with a step of 2 (giving us all the odd numbers…ie. 1, 3, 5 etc.). The key advantage for this loop is the fact we can use the counter variable in the statements of the loop. In this instance the value of i was used in the summation to arrive to the final Total. This has many great uses when dealing with complex code, in particular Arrays() which we will cover in depth in a later lesson.

While… Wend Loops

This type of loop will continue iterating while a given condition remains true. Warning: You could end up with a loop that will run forever, unless you provide it a suitable exit criteria; so tread carefully. This loop structure will allow for an indeterminate number of repetitions and hence is useful when it is not known in advance how many loops are required.

The While… Wend loop is illustrated in the example below, this time we’ll be summing all the numbers between 1 to 10. Notice that we have to manually increment the counter variable iĀ by using the statement i = i + 1 on Line 5.

It is important to notice that the condition (i < 5) is placed at the top of the loop. Consequently, if the condition is not met when the loop is first executed, the statements within the loop will never execute.

Do… Loop Loops

A Do Loop is very similar to a While Loop. However, in this instance, the loop condition can be placed at either the start or end of the loop. In addition, either While or Until conditions can be used. Consequently, the Do Loop is vastly more versatile than a While Loop. The four basic structures it can take are shown below.

With the condition at the top of the loop.

With the condition at the end of the loop.

It is important to note when the condition is placed at the bottom of the loop, the statements in the body of the loop will always execute at least once. An example of a Do Loop is shown below.

Effective loops will save you countless hours in your programming career! Enjoy!


They say the definition of insanity is doing the same thing over and over again, and expecting different results... (hopefully this is not true of our loops!

Leave a Reply