Excel VBA Do Until Loop
Do until loop will continue to repeat the statements until the condition/criteria become TRUE. It will execute the statements as long as the conditions are FALSE. As soon as the condition/criteria become TRUE, it terminates the loop. It can be looked as opposite to Do While loop, where loop runs as long as the criteria are TRUE and get terminated as soon as the criteria are FALSE.
See the flow diagram below which explains the working of Do Until loop:
Syntax of Do Until Loop in Excel VBA
Do Until Loop has two kinds of syntax in Excel VBA.
Syntax 1:
Do Until [Condition] [Statements to be executed] Loop
Syntax 2:
Do [Statements to be executed] Loop Until [Condition]
The basic difference between these two syntaxes is of execution. In the first syntax, the loop will always check if the condition is True or False. If it is False, it will repeat the loop again. As soon as the condition/criteria are true, it will terminate the loop. In this case, there is a possibility that the loop gets terminated at first iteration itself (if the condition is True). Hence, you will not get any output in that case. However, in second syntax the loop will initially execute the statements and then check for the condition whether it’s True or False. If the condition is False, it will again execute the same set of statements and check for the condition. If the first iteration itself gives condition as True, this loop terminates, but before that, it will have a statement executed. In that case, we will get at least one iteration (As null output).
Finally, the output of the two codes will be the same. However, the execution is something that differs these two.
Example of Excel VBA Do Until Loop
Let’s see the examples of Do Until Loop in Excel VBA.
Example #1 – VBA Do Until Loop when conditions are checked at the start
Follow the below steps to apply the Do Until loop in Excel VBA.
Step 1: Insert a new module under Visual Basic Editor (VBE) to be able to write code.
Step 2: Define a sub-procedure which can store the macro code you will be writing.
Code:
Sub Do_Until_Ex1() End Sub
Step 3: Define a new variable “X” with data type as “Long”. This variable can be used as a condition/criteria under Do Until loop.
Code:
Sub Do_Until_Ex1() Dim X As Long End Sub
Step 4: Set the initial value to 1 for the variable X.
Code:
Sub Do_Until_Ex1() Dim X As Long X = 1 End Sub
Step 5: Start the loop with keywords Do Until.
Code:
Sub Do_Until_Ex1() Dim X As Long X = 1 Do Until End Sub
Step 6: Specify the criteria as X = 11 under Do Until. This is a condition which allows the loop to break when is True. It means that the loop will be running until the value of X = 11. As soon as the value for X equals to 11, the loop will terminate.
Code:
Sub Do_Until_Ex1() Dim X As Long X = 1 Do Until X = 11 End Sub
Step 7: Now, use VBA Cells. Value function to be able to add the square of each number until X under the first column.
Code:
Sub Do_Until_Ex1() Dim X As Long X = 1 Do Until X = 11 Cells(X, 1).Value = X * X End Sub
Step 8: Again, we need to set the increment in X by 1, so that every time the loop runs and checks the condition it will go to the next number and store a squared value of it in the respective cell until number 11.
Code:
Sub Do_Until_Ex1() Dim X As Long X = 1 Do Until X = 11 Cells(X, 1).Value = X * X X = X + 1 End Sub
Step 9: Complete this Do Until loop by entering the keyword “Loop” at the end of the code.
Code:
Sub Do_Until_Ex1() Dim X As Long X = 1 Do Until X = 11 Cells(X, 1).Value = X * X X = X + 1 Loop End Sub
In this code, We wanted to have squared values for numbers starting from 1 until 11 (As soon as a value is 11, the loop terminates). Under each cell of excel sheet (until cell number 11), the values will get stored. Increment of 1 unit allows a number every time to be increased by 1 in previous value of X and the squared value of that number is printed in the respective cell. For example, Squared value for 3 will be printed under the 3rd cell of the active excel sheet. This loop runs as long as X is not equal to 11. Therefore, under cell A1 to cell A10, we get the squared values of number starting from 1 to 10 (at number 11 loop terminated).
Step 10: Hit the Run button or press the F5 key to run this code and see the output.
Example #2 – Do Until loop when conditions are checked at the end of the loop
Follow the below steps to apply the Do Until loop in Excel VBA.
Step 1: Define a new sub-procedure under VBE module.
Code:
Sub Do_Until_Ex2() End Sub
Step 2: Define a variable “Y” with data type as “Long”. This variable will be useful in looping up the conditions.
Code:
Sub Do_Until_Ex2() Dim Y As Long End Sub
Step 3: Set the initial value of variable “Y” as 1.
Code:
Sub Do_Until_Ex2() Dim Y As Long Y = 1 End Sub
Step 4: Add Do condition by typing Do on next line after you set the initial value of Y to 1.
Code:
Sub Do_Until_Ex2() Dim Y As Long Y = 1 Do End Sub
Step 5: Add the piece of code which you want to be executed under Do loop. Here we will be taking the same example as of above (it will help us getting know, how the outputs are same but compilations are different for two of these codes).
Code:
Sub Do_Until_Ex2() Dim Y As Long Y = 1 Do Sheets("Example 2").Cells(Y, 1).Value = Y * Y End Sub
Step 6: After every iteration of the loop, you wanted the value to be increased by 1 unit, set a command for that under Do section.
Code:
Sub Do_Until_Ex2() Dim Y As Long Y = 1 Do Sheets("Example 2").Cells(Y, 1).Value = Y * Y Y = Y + 1 End Sub
Step 7: Add a closing statement for this loop with a keyword as “Loop”.
Code:
Sub Do_Until_Ex2() Dim Y As Long Y = 1 Do Sheets("Example 2").Cells(Y, 1).Value = Y * Y Y = Y + 1 Loop End Sub
Hold on! There is something more. You must have to add the until criteria/condition under this loop, this time after the Loop keyword. It will allow the compiler to check the condition at the end of the loop.
Step 8: Add until Y = 11 as a condition after Loop Keyword.
Code:
Sub Do_Until_Ex2() Dim Y As Long Y = 1 Do Sheets("Example 2").Cells(Y, 1).Value = Y * Y Y = Y + 1 Loop Until Y = 11 End Sub
Step 9: Run this code by hitting F5 or Run button and see the magic under sheet named “Example 2”.
How does this code work?
System squares up to the value of Y every time under the loop and stores it under the second column of sheet named “Example 2” row by row. After every iteration, the current value of Y is being increased by 1 unit and the updated value is being squared and stored up. As long as the value is less than the criterion value (Y = 11), the code will do the square and store it up. As soon As the value compiler reaches to value Y = 11, it stops executing the code and terminates the same.
Both of the codes give a similar output, but logically there is a difference in compilations for both. In the first type of code, the condition is being checked at the start of the loop and if it is False then only the loop starts to evaluate the next expression. However, in the second example, the code starts running loop and executing Do statements at the start (storing the values in a buffer memory for the execution statements). At the end of the loop, compiler comes up with a criterion and checks the same with the buffer value.
Things to Remember
- VBA Do Until is exactly the reverse case of Excel VBA Do While. VBA Do While loop runs as long as the condition is being TRUE. Once the condition is FALSE, Do While loop gets terminated. On the other hand, VBA Do Until runs as long as the condition is FALSE. As soon as the condition is TRUE, loop gets terminated.
- It has two ways of proceedings, one where the condition is checked at the start of the loop and other where the condition is checked at the end of the loop.
Recommended Articles
This is a guide to VBA Do Until Loop. Here we discuss how to use Excel VBA Do Until Loop along with some practical examples and downloadable excel template. You can also go through our other suggested articles –
