Do Until Loop in VBA – Usage with Examples

In the previous article, we learned about the usage of Do While Loop. The Do Until loop in VBA also works on the basis of condition check but it is very different from the working of Do While Loop. Let us start learning the Do Until Loop.

What is Do Until Loop in VBA?

The Do Until loop executes a block of statements multiple times. The condition is checked before each iteration of the statement block. In a Do Until loop, the loop body is executed only if the condition is FALSE. If the logical test in the condition returns a TRUE then the loop will stop iterating the statement block.

There are two types of Do Until Loop.

• Do Until Loop – In this loop, the condition is checked before each iteration of the loop body. This is an entry control loop since the entry condition is applied to enter the loop.
• Do Loop Until – In this loop, the loop is executed before checking the condition, and the condition is checked at the end of each iteration. This is an exit control loop as the condition is checked after the iteration completes.

Syntax of Do Until Loop

Do Until(<condition_test>)
'statement 1
'statement 2
'statement 3
'
'
statement n
Loop

The condition test must return a logical TRUE or FALSE in this syntax.

Working of Do While Loop – The condition result would be checked at the beginning of each iteration. The statements will be executed only if the condition result is FALSE. Once the last statement of each iteration completes, the condition is rechecked before the start of the next iteration. The loop keeps iterating the block of statements until the condition test returns a logical TRUE.

Examples to Implement Do Until Loop

In this section of the blog, we will implement some examples to learn the working of Do Until Loop in VBA. So let us start.

Example 1 – Print Counter Variable Value using Loop

Let us suppose we wish to print the value of the counter variable.

• We wish to start from 1 and increase the value of the counter variable each time by 1.
• The counter variable value should be printed in the contiguous range of cells ( starting from cell A1 ) until it is greater than 7.
• Once the counter value becomes greater than 7, the loop must stop printing the value of the counter variable.

We can use the following Sub Procedure to implement the counter variable loop.

Sub exPrintCounterVariable()
'print the value of counter variable using Do Until Loop
Dim counter As Integer
'set the value of counter variable to 1
counter = 1
'iterate the loop until counter > 7
Do Until counter > 7
    'Cells function to assign value of counter variable to cells in spreadsheet
    Cells(counter, 1).Value = counter
    'change the color of cell containing value of counter variable
    Cells(counter, 1).Interior.ColorIndex = 35
    'increment the value of counter variable to print its value in next iteration
    counter = counter + 1
Loop
End Sub

When you run this Sub Procedure, it prints the value of the counter variable in the range A1:A7.

Example 2 – Adding New Worksheets to Workbook

We can use the Do Until loop to add new worksheets to the workbook until the total number of worksheets in the workbook reaches the specified limit. Let us say we have one worksheet in the workbook and we wish to add new worksheets to the workbook until there are 5 worksheets in the workbook. We can use the Do Until loop in this case.

Sub exAddWorksheets()
'adding new worksheets until there are five worksheets in the workbook
Do Until Worksheets.Count = 5
    'add new worksheet to workbook in every iteration
    Worksheets.Add
Loop
End Sub

As a result, you can see that there are five worksheets in the workbook. The four new worksheets are added by the Do Until loop in Macro.

This brings us to an end.

Thank you for reading. ❤