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: 

1. For loop
2. Do while loop
3. Do until loop
4. For each loop
5. 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.

Syntax: 

Do Until <condition(s)>

[Code to execute]

Loop

where 

<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

Output:

1 

 2 

 3 

 4 

 5 

 6 

 7 

 8 

 9 

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

Output

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
2003	Australia	India	Australia	Wanderers, Johannesburg
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
2023	—	—	India	—

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

Output:

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 

1. Loading of a page, 
2. A button to appear or get enabled,
3. A submission of a form to happen and validate the same

Etc…

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") > 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

Output 

Taj Mahal is located in India – Agra

Conclusion

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.