Excel VBA Do While Loop and (Do Loop While) – A Guide

VBA Do while is a loop in which you need to specify a condition and that condition must remain true for the loop to run.

In simple words, first, it checks that the condition you have specified is true or not and if that condition is true it runs the loop, otherwise nothing.

In each iteration, it tests for the condition and only then executes the statement. Imagine, you want to add worksheets in Excel while the full account of the worksheets is 12 or below.

In this case, you can use the do-while loop to write that code.

Syntax

Following is the syntax for the VBA For Each Next Loop.

Do While Condition
    [statements]
Loop

1. Condition: It is the condition that you specify, and this condition must be true to run the loop.
2. Statement: The line(s) of code are you want Do While Loop to execute condition is true.
3. Loop: It’s the end statement for one iteration of the loop and tells VBA to move back to test the condition again.

Example to Understand the DO While Loop

To understand Do While Loop, let’s write a code to add worksheets in a workbook hey while the total count of the worksheets is below twelve.

You do not need to tell VBA how many sheets to add and you will always have a total of 12 sheets every time when you run this code.

Use the following steps:

1. First, you need to type the keyboard “Do While”.
   
2. Next you need to write a condition that can check if the total number of worksheets in the workbook are lower than twelve.
3. For this, you need to use the below code, that count the total count of the worksheets then compare it with the number twelve.
   
4. Next you need to write the code to add a worksheet. This code will run when the condition that you have specified is true.
   
5. And, in the end, type the keyword “Loop” to end the code.
   

Here is the full code that you have just written:

Sub vba_do_while_loop()

    Do While ActiveWorkbook.Worksheets.Count < 12   
        Sheets.Add   
    Loop

End Sub

Now let me tell you how this code works: The condition you have specified checks for the total number of worksheets in the workbook and then you have a lower-than operator that checks for the count of the worksheets against twelve.

When the count of the worksheet is below twelve the loop will continue to run and keep on adding new worksheets but once the count of the worksheets will reach twelve it will stop the loop.

It’s in simple words the loop will continue to run while the count of the worksheets is below twelve.

Here’s the thing, with the code that you have just written, you don’t need to specify the count of the worksheets that you want to add, but if you go with the next loop you need to specify the count of the worksheets that you want to add.

Do Loop While

Do Loop While is an extended version of Do While Loop as it works in the same way but there is a slight difference while testing the condition.

In Do Loop While, it runs one iteration of the loop before testing the condition that you have specified and if the condition is true it will continue to loop.

Let’s say, you want to write a code to verify a password to run a code and continue to loop while the password is incorrect (we see it in the example).

Syntax

Following is the syntax for the VBA Do Loop While.

1. Do: It is the starting keyword for the Do Loop While.
2. Statement: It is the line(s) of the code that you want to run in the loop.
3. Loop While: It’s the keyword to continue the loop and test the condition that you have specified.
4. Condition: it is the condition that you want to test before the loop starts its second iteration and carries on the loop.

As you can see in the syntax of Do Loop While, it will first run the statement once and after that, it will go to the condition and test it, and if that condition is true, it will start the loop and continue it while the condition is true.

Example to Understand the DO Loop While

To understand the do loop while, let’s write a code to show an input box and ask the user to enter the password.

That input box will continue to show well the password entered by the user is incorrect up to five attempts and after that, the input box will disappear without running the procedure.

1. First, declare two variables that you need for storing the password and the counter for the attempts.
   
2. After that, type the “Do” keyword. Here you don’t need to define the condition first as you have seen in the syntax.
   
3. Next, you need to write a line of code to show the input box that asks the user to enter the password.
   
4. From here, you need to define a counter that can count upto five (adds up with each interation of loop).
   
5. Now, type the keyword “Loop While” and define two conditions that can verify the value entered by the user and into the input box and can test the value of the counter if it’s lower then than 5.
   
6. In the end, write the code that you want to run. Here I’m using the IF statement that will show a message box if the password entered by the user is correct.
   

Sub vba_do_loop_while()

Dim varPassword As Variant, i As Double
Do
    varPassword = InputBox _
    ("Enter the password to start the procedure:", _
    "Check Password 1.0")   
    i = i + 1
Loop While varPassword <> "CorrectPassword" And i < 5
If varPassword = "CorrectPassword" Then
    MsgBox "Your Password is Correct"
End If

End Sub

Let me explain this code.

• In the first part of this code, you have the do loop while which will show an input box to the user and ask for a password.
• And if the password is incorrect it will continue to run the loop until the user enters the correct password.
• But as you are using a counter to count the iterations of the loop and once that counter reaches the 5, the loop will stop.

Points to be Take Care

Here are some points to be take care of while using the Do While loop in VBA:

• Avoid Infinite Loops: To prevent your loop from running indefinitely, make sure it has a condition that will be met eventually. Infinite loops can cause your Excel to crash or freeze.
• Proper Increment/Decrement: Ensure that the variables used in the loop condition are appropriately incremented or decremented inside the loop. Please do so to avoid an infinite loop.
• Error Handling: Include error handling in your loop to manage any errors that may occur during execution. This can prevent your program from crashing if something unexpected happens.
• Correct Condition: The condition in the Do While loop should be correctly specified. An incorrect condition may result in the loop never being executed or executed more times than necessary.