VBA Do Until Loop

What is Do Until Loop in VBA Excel?

The VBA Do Until Loop is a fundamental programming construct for Excel. It is used to execute a code block repeatedly until a particular condition is met. This loop is handy when repeating actions until a specific condition becomes true. The Do Until Loop provides an efficient way to automate tasks and manipulate data within Excel spreadsheets.

Let’s look at an example where we’ll use VBA Do Until Loop to count from 1 to 10. The counter variable starts at 1, and the loop continues to execute as long as the condition counter > 10 is not met. During each iteration, the counter’s value is printed using the Debug.Print statement, revealing each number in the sequence.

The counter then increments by 1, and this process continues until the condition is satisfied and the output is seen on the immediate window.

Syntax

The Do Until Loop in VBA Excel provides two distinct syntax variations to achieve the same outcome—repeatedly executing a VBA code block until a specified condition becomes true:

Syntax Variation 1 – Do Until

Do Until condition

    ‘ Code to be executed

Loop

In this syntax, the loop starts immediately after the “Do Until” statement and continues executing the code block as long as the condition remains false. The loop ends with the “Loop” statement. The condition is evaluated before each iteration, ensuring that the code block executes only while the condition is false.

Syntax Variation 2 – Do … Loop Until

Do

    ‘ Code to be executed

Loop Until condition

In this syntax, the loop executes the code block without checking the condition initially. The loop continues executing as long as the condition remains false. The loop ends with the “Loop Until” statement, and the condition is evaluated after each iteration. This syntax delays the condition check until the code block has been executed at least once.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Difference Between the Syntax Variations

The primary distinction lies in the timing of the condition evaluation. The “Do Until” syntax checks the condition before each iteration, ensuring that the code block is executed only while the condition is false. On the other hand, the “Do … Loop Until” syntax executes the code block at least once before evaluating the condition, potentially leading to the code block executing even when the condition is initially true.

How to Use Do Until Loop in VBA Excel?

Here’s how you can use the Do Until Loop in VBA Excel:

Step 1: Open the Visual Basic for Applications (VBA) editor in Excel. Press Alt + F11 to open the editor.

Step 2: In the VBA editor, insert a new module by right-clicking on any existing module or the workbook name on the left pane – Insert – Module.

Step 3: Declare and initialize any necessary variables before the loop.

Step 4: Set up the VBA Do Until Loop structure by specifying the condition that needs to be met for the loop to continue.

Step 5: Place the code you want to repeat inside the loop.

Step 6: The loop will repeatedly execute the code within it until the specified condition evaluates to True.

Step 7: Once the condition becomes True, the loop will exit, and the program will continue executing the subsequent code.

Examples

Let us look at some examples of implementing the VBA Do Until loop.

Example #1

In this example, we will utilize the VBA Do Until Loop to systematically compute and showcase the factorial of 5.

Step 1: In the new module, start by creating a subroutine named, “CalculateFactorial.”

Step 2: Here, we declare two variables: num to hold the number for which we’ll calculate the factorial and factorial to store the calculated factorial value.

Step 3: We then assign a value of 5 to the num variable, indicating that we want to calculate the factorial of 5. The factorial variable is set to 1, which will be used as a starting point for our calculations.

Step 4: The Do Until Loop begins here. It will continue executing the code within the loop if the condition num = 0 remains false. In other words, the loop will run until ‘num’ becomes 0.

Step 5: Within the loop, the factorial variable is updated by multiplying its current value by the current value of num. It effectively accumulates the factorial product as we iterate through the loop.

Step 6: The value of num is decremented by 1 with each iteration. It ensures we progress through the loop while counting from 5 to 1.

Step 7: The loop will continue executing the above steps if the condition num = 0 remains false. Once num becomes 0, the loop terminates, and the program proceeds to the next line of code.

Step 8: The calculated factorial of 5 is stored in the factorial variable. This line of code uses the Debug.Print statement in VBA to display the calculated factorial value in the Immediate window and a descriptive message.

Step 9: Save the module and click on Run. Once you execute the code, you will see the factorial of 5 in the Immediate window.

Here is the complete code:

Sub CalculateFactorial()
Dim num As Integer
Dim factorial As Long
num = 5 ‘ Calculate factorial for the number 5
factorial = 1 ‘ Initialize factorial to 1
Do Until num = 0 ‘ Loop continues until num becomes 0
factorial = factorial * num ‘ Multiply factorial by num
num = num – 1 ‘ Decrement num by 1
Loop
Debug.Print “Factorial of 5: ” & factorial
End Sub

Example #2

In this example, we will look at how the VBA Do Until Loop calculates the sum of even numbers. The provided code demonstrates how the loop efficiently adds even numbers from 2 to 20 and prints the cumulative sum.

Step 1: In the new module, create a subroutine named “SumEvenNumbers.”

Step 2: We begin by declaring two variables: sum to accumulate the sum of even numbers and num to represent the numbers we’ll be working with. Both variables are initialized, with the sum set to 0 and ‘num’ set to 2 as the starting point for our calculations.

Step 3: The Do Until Loop initiates here. It will continue executing the code within the loop as long as the condition num > 20 remains false. The loop ensures that we keep processing even numbers until num crosses the value of 20.

Step 4: Inside the loop, the sum variable is updated by adding the current value of num to it. This cumulative addition ensures that even numbers are continuously added to the running sum.

Step 5: With each iteration of the loop, the value of num is increased by 2. This step is crucial to move to the next even number for processing.

Step 6: The loop will persistently execute the above steps if the condition num > 20 remains false. Once num surpasses the value of 20, the loop concludes, and the program progresses to the next line of code.

Step 7: After the loop finishes, the calculated sum of even numbers is stored in the sum variable. This line of code uses the Debug.Print the statement to display the sum in the Immediate window and an informative message.

Step 8: Save the macro and click on Run. Once you execute the code, you will see the sum of even numbers calculated and displayed in the Immediate window.

Here is the complete code:

Sub SumEvenNumbers()
Dim sum As Integer
Dim num As Integer
sum = 0
num = 2
Do Until num > 20
sum = sum + num
num = num + 2
Loop
Debug.Print “Sum of even numbers: ” & sum
End Sub

Important Things to Note

1. Ensure the condition specified in the Do Until loop is eventually met to avoid infinite loops.
2. The VBA Do Until Loop’s strength lies in controlled repetition. It enables you to efficiently automate tasks that must be repeated until a specific condition is met, reducing manual effort.
3. The “Exit Do” statement within an If statement in VBA allows you to control when the VBA Do Until Loop breaks based on specific conditions.
4. You can achieve a “VBA Do Until Loop continue” effect by using an “If” statement to skip the rest of the loop’s current iteration and proceed to the next one.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA Do Until Loop. Here we learn how to use Do Until Loop in Excel VBA, with examples & downloadable excel template. You can learn more from the following articles –

• VBA DIR Function
• VBA Loops
• VBA Break For Loop