What is Do Until Loop in VBA Excel?
In VBA Do Until Loop, we need to define criteria after the until statement which means when we want the loop to stop and the end statement is the loop itself. So if the condition is FALSE it will keep executing the statement inside the loop but if the condition is TRUE straight away it will exit the Do Until statement.
As the words itself says that to do some task until a criterion is reached, Do until the loop is used in almost all of the programming languages, in VBA also we sometimes use Do until loop. Do Until Loop means to do something until the condition becomes TRUE. It is like a logical function works based on TRUE or FALSE.
This is the opposite of Do While loop where Do while runs the loops as long as the condition is TRUE.
Do Until loop has two kinds of syntax.
Do Until [condition] [Perform Some Task] Loop
Do [Perform Some Task] Loop Until [condition]
Both look very similar and there is one simple differentiation is there.
In the first syntax “Do Until” loop checks the condition first and gets the condition result is TRUE or FALSE. If the condition is FALSE it will execute the code and perform a specified task and if the condition is TRUE then it will exit the loop.
In the second syntax “Do” loop firstly it will execute the cod task then it tests whether the condition is TRUE or FALSE. If the condition is FALSE it will again go back and perform the same task. If the condition is TRUE then it will straight away exit the loop.
I know it is not that easy to understand anything in the theory part, but nothing to worry. We will give you easy examples to understand the loop. Read on. To start the learning, let’s perform the task of inserting the first 10 serial numbers from cell A1 to A10.
Follow the below steps to apply the “Do Until” loop.
Step 1: Create a macro name first to start the subprocedure.
Sub Do_Until_Example1() End Sub
Step 2: Define a variable as “Long”. I have defined “x” as a long data type.
Dim x As Long
Step 3: Now enter the word “Do Until”.
Step 4: After starting the loop name enter the condition as “x =11”.
Do Until x = 11
x = 11 is the logical test we have applied. So this line says to run the loop until x is equal to 11.
Step 5: Apply CELLS property and let’s insert serial numbers from 1 to 10.
Cells(x, 1).Value = x
Note: Here we have mentioned “x” starts from 1, so at first x value is equal to 1. Wherever “x” is there is equal to 1.
Step 6: Now close the loop by entering the word “LOOP”.
Sub Do_Until_Example1() Dim x As Long Do Until x = 11 Cells(x, 1).Value = x Loop
Ok, we are done with the coding part, now we will test the codes line by line to understand the loop better.
To run the line by line code first press the F8 key.
It will first highlight the macro name by yellow color.
When you can see the yellow line it says that that is not executed by about to execute if you press the F8 key one more time.
Now press F8 key one more time, the yellow line will go to Do Until Loop.
Now to understand the loop place a cursor on the variable “x” and see the value of variable “x”.
So, x=0. Since the highlighted line is the first line in the loop so the value of “x” is zero, so press the F8 key once again and see the value of “x”. Before that exit, the code running and assign the value to “x” as 1.
Now again start the running of a loop by pressing the F8 key. See the value of “x”.
Now the value of “x” is showing as 1. To have incremental value to the variable “x” we need to reassign the value of variable “x” as x = x + 1 inside the loop.
Now press F8 key one more time and we should get the value of 1 in cell A1.
Now press the F8 key once again and see what the value of “x” is.
The value of variable “x” is 2 now. So our condition says to run the loop until the condition becomes TRUE, so our loop keeps running until the value of “x” becomes 11.
Press F8 one more time it will jump back to the “Do Until” loop line.
Press F8 key two more times, we will get the value of 2 in cell A2.
Press F8 key again and the value of “x” becomes 3 now.
Press the F8 key again it will jump back to the loop once again.
Like this, this loop will again keep executing the task until the value of “x” becomes 11. Now I have executed the loop until the “x” value becomes 11.
Now if I press F8 it still will go back to the loop.
But if I press the F8 key now it will exit the loop because the applied condition becomes “TRUE” i.e. x = 11.
So we have serial numbers from 1 to 10 in excel sheet now.
So, this is the basic idea of the “Do Until” loop. To understand any loops you need to run the code line by line until you get the full knowledge about loops.
This has been a step by step guide to VBA Do Until Loop. Here we discuss how to use Do Until Loop in Excel VBA along with a practical example. Below are some useful excel articles related to VBA –