1.8 Resize
Home and Learn - Free Excel VBA Course
5.4 Excel VBA Do Loops
There are two kinds of Do loops in Excel VBA, Do While and Do Until. Let's take a look at the Do While loop first.
The Do While Loop
The Do While loop is a lot easier to use than the For loop you used in the previous lesson, as you don't need to set a start condition, just the end condition. Here's the structure of a Do While 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 did 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 FALSE 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 an example, though. Add a new Sub to your coding window from the
previous lesson. 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 While Loop. Give it a try:
Do Until Loop
A loop that's closely related to the Do While loop is the Do Until loop. You use them the same way, except instead of typing While you type Until. When you're using Until, though, the end condition will change slightly. Here it is for the Do While Loop:
Do While counter < 5
counter = counter + 1
Loop
And here is the Do Until Loop with a condition on the end.
Do Until counter > 5
counter = counter + 1
Loop
Now we're saying Do Until counter is greater than 5.
If you need to bail out early from any loop then you only need the word Exit followed by the loop name:
Do Until counter > 5
If counter = 0 Then
Exit Do
Else
End If
Loop
The above applies to For loops, as well: just put Exit For if you need to bail out early.
In the next section, we'll take a look at something called an Array.
Next Lesson: 6.1 Arrays >