VBA Do While Loop – The Ultimate Guide with Examples

VBA Do While Loop

VBA Do while is a loop in which you need to specify a condition and that condition must remains 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 execute the statement. Imagine, you want to add worksheets in Excel while the total account of the worksheets is 12 or below. In this case, you can use 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 total of 12 sheets every time when you run this code.

Use the following steps:

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

GIF

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 for 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 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 keywords 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 loop and test the condition that you have specified.
  4. Condition: it is the condition that you want to test before loop to start it second iteration and carry on the loop.

As you can see in the syntax of Do Loop While, it will first run the statement for 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 Do Loop While, let’s write a code to show an input box and ask user to enter the password to run the procedure and and that input box will continue to show well the password entered by the user is incorrect upto the five attempts and after that, the input box will disappear without running the procedure.

Use the following steps:

  • First, declare two variables that you need for storing the password and the counter for the attempts.
    7-declare-two-variables-you-need-for-storing
  • After that, type the “Do” keyword. Here you don’t need to define the condition first as you have seen in the syntax.
    8-type-the-do-keyword
  • Next, you need to write a line of code to show the input box that asks the user to enter the password.
    9-write-a-line-of-code
  • From here, you need to define a counter that can count upto five (adds up with each interation of loop).
    10-define-a-counter
  • 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.
    11-type-keyword-loop-while-and-define-two-condition
  • 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.
    12-write-the-code-you-want-to-run

Here’s the full code…

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 that 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 if user is not entering the correct password.

But as you are using a counter to count the iterations of the loop and once that counter reach the 5, the loop will stop.

So, you have two conditions here to test.

GIF