The Do While Loop

The Do While loop is a lot easier to use than a For loop as you don't need to set a start condition, just the end condition. Here's the structure of a Do Loop:

Do While [CONDITION]

Loop

After Do While on the first line, you need a condition to test. This is usually the same sort of thing you sort with in the Conditional Logic section. For example:

Do While x < y

Do While counter < 5

Here, the loop goes round and round until the test condition at the start evaluates to FALSE. As soon as VBA detects that your test condition is FLASE it will exit the loop and continue on its way.

However, you do need a line of code to increase your loop variable, otherwise you'll create an infinite loop. In the code below, we're increasing (incrementing) the counter variable by 1 each time round the loop:

Do While counter < 5

​ counter = counter + 1

​ Loop

If we didn't increment the counter variable then there would be no way for VBA to reach the end condition of "Do while counter is less than 5".

If you like, you can add your condition at the end, just after the word Loop :

Do

​ counter = counter + 1

​ Loop While counter < 5

The difference here is that any code you have for you loop will execute at least once, if the condition is at the end. If the condition is at the start then the loop code won't get executed if the condition already evaluate to FALSE (counter might not be less than 5, in our code above).

Let's try some an example, though. Add a new Sub to your coding window. Call it DoWhileExample . Now add the following code:

We've set up an Integer variable called counter , and set it to a value of 1. The Do Loop goes round and round while counter is less than 5. So as not to set up an infinite loop, we have this line:

counter = counter + 1

If you're having trouble understanding this line, just start to the right of the equal sign to see what calculation is being performed:

counter + 1

Whatever value is currently in counter will get 1 added to it. But this value needs to be stored somewhere. The "somewhere" just happens to be the counter variable again, which is on the left of the equal sign. The old value inside of counter will be overwritten, to be replaced with the new value that VBA has just calculated on the right of the equal sign.

The other line in the code is this:

Cells(counter, "D").Value = counter

Because counter changes each time round the loop we can use it in between the round brackets of Cells , for the Rows value. The Column we've chosen is the D column.

Run your code and your spreadsheet will now look like this:

So we have the numbers 1 to 4 in the D column.

And here's the times table programme again, but this time using a Do Loop. Give it a try: