VBA While, Do While, Do Until loops (Excel & Simple Demos)

The VBA while loop is used to execute the given statements as long as the condition is True.

If the condition is false on the first check, the execution moves out of the while loop without executing the given statements even once.

The Do While loop is also used to execute the given statements as long as the condition is True.

However, it can be used for at least executing the statements once, inside the loop, even if the condition is False at first. (That is the difference between While and Do while)

The Do Until loop runs the given statements until the condition becomes True.

Note: The While..Do While and Do Until loops are generally used if you have no idea about the number of iterations. If you know in advance for the number of loops, the better choice can be a VBA For..Next loop.

See the following section for examples and how to use all these loops:

An example of VBA While loop

In this example, a variable i is initiated with the value of 1.

The while loop should keep on displaying a message box as long as the value of the variable i is less than or equal to 3:

Private Sub while_exmamples()

Dim i As Integer

While i <= 3

    MsgBox "The value of i = " & i

 i = i + 1

Wend



End Sub

The while construct is ended by using the Wend keyword in VBA.

A Do..While loop example

For this example, the Excel sheet is used for demonstrating the Do..While loop.

For that, a loop will run ten times and fill the cells with the current value of the variable in column A.

Another statement is used to update the cells in column B after multiplying the corresponding column A’s value by 2.

Private Sub while_loop_Click()

Dim num As Integer

num = 1

Do While num <= 10

    Cells(num, 1).Value = num

    Cells(num, 2).Value = Cells(num, 1).Value * 2

    num = num + 1

Loop

End Sub

You saw, as the code executed on the click event of the button, the columns are updated by using a Do While loop.

An example of Fahrenheit to Celsius by Do..Until loop

For this example, the Do..Until loop is used for converting the Fahrenheit temperatures in A columns to Celsius in B columns.

Private Sub while_loop_Click()

Dim num As Integer
num = 2

Do

  Cells(num, 2).Value = (5 / 9) * (Cells(num, 1).Value - 32)

  num = num + 1

Loop Until num >= 7

End Sub

You see, the Do Until is used as the last line in the loop, and the loop kept on executing the statement until the condition became True.

The example of using Exit statement

The Exit statement is used to terminate a loop for the remaining iterations.

The following example shows how to use the Exit statement.

A Do..While loop is initiated with a condition if num >= 1.The initial value of the variable is set as 5.

In each iteration of the loop, the value will be decremented by -1.

In that way, the loop should run four times and display the message box with the current value of the variable.

However, a VBA if statement is used to check the value of the variable.

If this is equal to 3, the Exit statement will execute and see how many times the dialog box appears by running this code:

Private Sub while_exmamples()

Dim x As Integer

num = 5



Do While num >= 1

  MsgBox "The variable value = " & num

  num = num - 1

    If num = 3 Then

        Exit Do

    End If

Loop

End Sub