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.
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
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 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
Which is better – While or Do While/Until?
Officially, the Do while/Until gives more flexibility than the While loop. You may place the condition at first or at the end as using Do loops.