Guide: Do Until Loops in VBA
Loops in VBA
Just like any other programming language, VBA also provides several loops that help with the repeated execution of a specific piece of code for a certain number of iterations based on defined conditions.
The list of loops provided by VBA are:
- For loop
- Do while loop
- Do until loop
- For each loop
- And a combination of nested loops of the above.
In this article, we will take a deeper dive into the “do until” loop.
The Do Until…. Loop
The do until loop is a little different from the “do while” loop. A do while loop executes the loop content when the condition is met. But Do until loop executes the loop content for every iteration until the condition is met. Once the condition is met, the iteration stops.
[Code to execute]
<condition(s)> is the one or more conditions that should be satisfied for each iteration of the loop to execute and
[Code to execute] is the piece of code that should be executed in every iteration.
Simple Example of a Do Until Loop
Here is a program with an integer. Its value is initialized as “1” and then a do until loop is used.
The loop is designed to print the value of the integer and increment it by 1 as long as the integer’s value is less than or equal to 10. So, all numbers starting from 1 until 9 are printed. Once the incremental integer value reaches 10, the condition is met, so the iteration stops, and the loop ends.
Sub do_while_demo() ' declare variables Dim i As Integer ' initialize the variable i = 1 ' loop to print 10 numbers starting from 1 Do Until i = 10 ' print the value of i Debug.Print i ' increment the value of i i = i + 1 Loop End Sub
Loop to Print The Values in An Open Worksheet
This program iterates through every row of an open worksheet and prints all the values of col 2 and col 3 of that row as long as the value in the first column is not empty.
Sub do_until_loop_demo() ' declare variable & initialize Dim i As Integer i = 2 ' loop through every row of the active worksheet and stop once the cell in the first column is empty Do Until Cells(i, 1).Value = "" ' print the wonder and its location in subsequent columns in each row Debug.Print Cells(i, 2).Value & " located in " & Cells(i, 3).Value ' increment this counter / row number to iterate through the next row. i = i + 1 Loop End Sub
Taj Mahal located in India – Agra
Chichen Itza located in Mexico – Yucatán
Christ the Redeemer located in Brazil – Rio de Janeiro
Colosseum located in Italy – Rome
Great Wall of China located in China
Machu Picchu located in Cuzco Region
Petra located in Jordan -Ma’an Governorate
Great Pyramid of Giza located in Egypt.
Condition And Content Outside The Loop
Here is a different program that loops through the country and year columns of each row. It stops when the country is India and prints the year associated with it. Here you need to understand that this do until loop acts as a dummy to simply move the execution control until the row where “India” appears as the country first.
The table is used in the open sheet.
|ICC Men’s Cricket World Cup Winners – 50 Overs|
|Year||World Cup Winner||Runners Up||Host||Finals Venue|
|1975||West Indies||Australia||England||Lord’s Cricket Ground, London|
|1979||West Indies||England||England||Lord’s Cricket Ground, London|
|1983||India||West Indies||England||Lord’s Cricket Ground, London|
|1987||Australia||England||India & Pakistan||Eden Gardens, Kolkata|
|1992||Pakistan||England||Australia & New Zealand||Melbourne Cricket Ground, Melbourne|
|1996||Sri Lanka||Australia||India, Pakistan & Sri Lanka||Gaddafi Stadium, Lahore|
|1999||Australia||Pakistan||England||Lord’s Cricket Ground, London|
|2007||Australia||Sri Lanka||West Indies||Kensington Oval, Bridgetown|
|2011||India||Sri Lanka||India||Wankhede Stadium, Mumbai|
|2015||Australia||New Zealand||Australia||Melbourne Cricket Ground|
|2019||England||New Zealand||England||Lord’s, London|
Sub do_india_world_cup() ' declare variable & initialize Dim i As Integer i = 3 ' loop through every row of the active worksheet and stop once the cell in the second column is "India" Do Until Cells(i, 2).Value = "India" ' simply keep iterating. ' increment this counter / row number to iterate through the next row. i = i + 1 Loop If Trim(Cells(i, 2).Value) = "India" Then ' print the year when India was declared as the Winnner for the first time Debug.Print "India was declared as the winner in " & Cells(i, 1).Value & " for the first time. " End If End Sub
India was declared the winner in 1983 for the first time.
Loop for Waiting for Something to Happen
A do until loop can be used to wait for some actions like
- Loading of a page,
- A button to appear or get enabled,
- A submission of a form to happen and validate the same
The below code snippet is a loop that keeps iterating as long CommandButton1 is disabled.
Sub do_wait_demo() Do Until CommandButton1.Enabled = True ' keep waiting Loop End Sub
Exiting The Do Loop
The loop can be exiting, skipping further iterations, using the “Exit Do” statement. This statement can be used within conditional blocks/statements like the If statement. If the condition is met, the loop is exited, skipping further iterations.
Example Of Using The Exit Do Statement
In this program, the “exit do” statement is used within a conditional block. The condition is to check if any of the locations printed has the word “India” in them. If the condition is met during the execution, the loop is exited, skipping further iterations.
Sub do_until_loop_demo() ' declare variable & initialize Dim i As Integer i = 2 ' loop through every row of the active worksheet and stop once the cell in the first column is empty Do Until Cells(i, 1).Value = "" ' print the wonder and its location in subsequent columns in each row Debug.Print Cells(i, 2).Value & " located in " & Cells(i, 3).Value If InStr(Cells(i, 3).Value, "India") &gt; 0 Then ‘ the loop is exited when the location has the word “India” in it. Exit Do End If ' increment this counter / row number to iterate through the next row. i = i + 1 Loop End Sub
Taj Mahal is located in India – Agra
The do until loop is a code snippet that keeps executing continuously until an action happens or until the stated condition is met. It is clearly different from all other loops offered in programming languages that keep iterating as long as the condition results in “True” value (or) as long as a particular condition is met. So it is very important to use this loop wisely and in the right context.