Do While Loop in VBA – Usage with Examples

We know about a number of loops like For Next Loop and For Each Loop. Today we will learn about another important type of loop which is the Do While loop in VBA. It is a very important topic to understand and this article covers everything from scratch.

So let us begin learning.

When to use Do While Loop?

The Do While Loop is used to execute a given block of statement multiple times. The block of statements is executed based on the condition. If the condition is satisfied then the loop body will be executed.

Syntax of Do While Loop

Do While(<condition>)
statement 1
statement 2 
.
.
statement n
Loop

Here the condition must return a logical TRUE or FALSE. In this loop, the condition will be checked before the first iteration, if the condition is TRUE, then the Statements will be executed otherwise, the loop body will not be executed. After the last statement in the loop body executes, the condition is rechecked. If the result of the condition is TRUE then the next iteration of the block of statements is executed. The loop keeps iterating until the <condition> becomes FALSE.

Examples to Implement Do While Loop

In this blog section, we will implement some practical examples to learn the usage of the Do While loop in VBA.

Example 1- Simplest Example to Print the Value of Variable in Each Iteration

In this example, we want to print the counting from 1 to 5. First, we will try to do this without taking the help of the Do While loop.

Sub examplePrintCount1to10()
'declare count variable
Dim count As Integer
'initialize the value of count
count = 1
MsgBox count
'increment by 1 and print the value of count variable
count = count + 1
MsgBox count
count = count + 1
MsgBox count
count = count + 1
MsgBox count
count = count + 1
MsgBox count
End Sub

• Press Alt and F11 keys to open the VBE. Go to the Insert tab and insert a new module.
• Paste the above Sub procedure into the new module.
• Place the cursor in the code in a module and press the F8 keys multiple times to see the line-by-line execution of the code.

We can use the Do While loop to minimize the total number of lines in our code.

Sub examplePrintCount1to10DoWhile()
'initialize the count variable
Dim count As Integer
'initialize count variable
count = 1
'the loop executes until count is less than or equal to 5
Do While (count <= 5)
    'print the value of count
     MsgBox count
    'increment the value of count
    count = count + 1
Loop
End Sub

The same output is produced with this Sub Procedure.

Example 2 – Adding Worksheets to Workbook

In this example, we will add new worksheets to the workbook until there are five total worksheets.

We are using the Worksheets Collection to add the new worksheet to the workbook.

Sub addFiveWorksheets()
'checks the condition
Do While Worksheets.Count < 5
'adds the worksheet
Worksheets.Add
Loop
End Sub

When you run this Sub Procedure with the help of F8 keys, the line-by-line execution will add new worksheets to the workbook.

This brings us to an end.

You can also learn about Do Loop While which is similar to this loop.

Thank you for reading.