Excel VBA Do While: Looping it the Right Way

Visual Basic for Applications (VBA) is included with the standard Microsoft Office suite. With VBA, you can extend the functionality of these standard applications (like Excel and Word) by creating and executing your own programs. VBA is based on the Visual Basic language, which was designed by Microsoft. VBA lets you do everything much more quickly and efficiently than you would with the regular methods. If your work involves using Excel all the time or if you simply want to learn Excel to land a better job, you really should take our beginner Excel VBA course. If you’re already familiar with the basics, you could do a quick recap with this VBA tutorial before going further.

Loops in Excel

If you’re familiar with programming languages like C or Java, you know what a loop does already. To recap, a loop helps you carry out a series of repetitive tasks without having to write code for each and every one of them. In a regular work environment, loops are indispensable- you will be using them a lot, so it’s a good idea to get a good handle on them.

In this tutorial, we’re going to take a look at the do while loop in Excel. There are two loops in Excel that can be paired with the do command: the do while loop and the do until loop. You can also vary the code a little and write a do loop while statement- which will execute the code at least once and then check if the condition statement has been satisfied. First, let’s take a look at the do while loop.

The Do While Loop in Excel VBA

We’re going to assume you know how to access Visual Basic for Applications. We’re also going to assume you know how to attach the code to your spreadsheet and get it working. If you’re not familiar with that, you can easily learn it with this basic course in Excel and this course on using Macros with VBA.

The syntax for a do while loop is:

Do While [Condition Statement]
Code to control the number of loops
Loop
Code to be looped

The syntax is easy enough to understand. First, we check for a condition statement before we begin the loop. The condition can be anything. For example, you can ask the program to keep looping until the value of integer “i” is less than 10 (i<10).  Next, we execute the code you placed below the do while statement to help you control the number of loops. Finally, the code we want to loop will be executed, but only if the condition statement has not been satisfied. If the condition statement is satisfied, it will stop looping. If we continue with our example, if the value of integer “i” was greater than 10, the code would not have been executed even once (however, it would have been iterated).

Example: Let’s take a simple example to help you understand the concept better:

Sub example()
Dim i As Integer
i = 3
Do While (i < 10)
i = i + 1
Loop
MsgBox i
End Sub

Here, we’ve declared i as an integer. Next, we’ve assigned i the value of 3. Then we’ve created a do while loop. We’re instructing the computer to execute the code while the value of i is less than 10. In the loop, we’re increasing the value of i by 1. This means that the code below the loop statement, which is MsgBox I, will be executed until the value of integer i remains less than 10. Finally, MsgBox i will display a message box on the screen for the user.

Have you spotted a limitation with the do while loop? The code we place below the condition statement won’t be executed even once if the condition statement is satisfied. In other words, if the value of integer i was greater than 10, the computer won’t add 1 to it. Sometimes you need to execute the code in your program at least once before the loop is terminated – if you are going to be using i later in the body of your program, for example. You can do that changing your code just a little.

The Do Loop While Statement

If you want the value of i to change, even if the condition statement has been met, you can place the while keyword at the end of the loop statement. The syntax would be as follows:

Do
Loop While [Condition Statement]
Code to be looped

Example:

Sub example()
Dim i As Integer
i = 10
Do
i = i + 1
Loop While (i < 10)
MsgBox i
End Sub

Just like before, we’ve declared i to be an integer and initialized its value to 10. Then we’ve written a do loop, with the condition that the MsgBox i should be executed only if the value of integer i is less than 10. However, because the value of the integer i is 10, the code won’t be executed at all. However, the value of i will be incremented by 1.

Exiting the Do While Loop

You can exit the do while loop using the exit do statement. Let’s just add the statement to the first example we gave:

Sub example()
Dim i As Integer
i = 3
Do
i = i + 1
if (i=5) then Exit Do
Loop While (i < 10)
MsgBox i
End Sub

The code will stop executing when the value of the integer i is incremented to 5. This is a manual way to exit out of the do while loop. You won’t need to use it often – you should be able to structure your program without it. It just a neat trick to know.

If you want to learn more about using VBA for Microsoft Excel, sign up for our advanced Microsoft Excel VBA course. You’ll get an in-depth look at the other loops in VBA too – namely, the VBA for loop, the VBA do until loop and, of course, the VBA do while loop. Mr Excel’s one stop course for all things Excel and VBA is also a good place to stop by. As usual, the best way to learn programming is to practice – go loop some loops (in VBA ofcourse!)