The While and Do loops in VBA
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 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 by 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 to 2.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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 loop kept on executing the statement until 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 dialog box appears by running this code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 |
What is better – While or Do While/Until?
Officially, the Do while/Until gives more flexibility than While loop. You may place the condition at first or at the end as using Do loops.