VBA Do Loop

What Is Excel VBA Do loop?

VBA Do loop in Excel allows us to repeat tasks multiple times until we obtain the desired output or the condition is True. The best part of using the VBA Do loop is that we don’t have to rewrite the code for each task. Instead, the loop analyses each task/values step by step. The Excel VBA Do loop works until the condition is True.

Let us use an example to understand VBA Do loop. Suppose we want to multiply the cells from A1 to A5 by 5. Here we will show how the Do While loop works. First, open Excel and write random numbers from A1 to A5. Then, write and run the VBA code below in a module in the VBA editor. The result shows cells B1 to B5 with the multiplied numbers. The loop is executed till a blank cell is encountered. If we delete one of the cells (A3), the Do…While loop will exit the loop and show the result up to B2.

VBA Do Loop Intro

Code:

Sub VBA_Do_While_Ex()
Dim X As Integer
X = 1
Do While Cells(X, 1).Value <> “”
Cells(X, 2).Value = Cells(X, 1).Value * 5
X = X + 1
Loop
End Sub

Key Takeaways
  • VBA Do loop is a code where a statement or set of instructions is executed repeatedly based on a given condition.
  • The condition used in VBA Do loop gives a Boolean type result.
  • VBA Do loop allows us to run the code multiple times till the condition is true or while a condition is true.
  • The VBA Do…While loop works multiple times till the condition is True, and the VBA Do…Until loop works multiple times till the condition is False.
  • VBA Do Exit statement is used to exit the loop before the condition changes.
  • If…Then…Else can be used as a condition statement along with Exit Do.

Syntax

Do [{ While | Until } condition ] [ statements ] [ Exit Do ] [ statements ] Loop

  • Condition: It may be a numeric or string expression of either True or False. If the condition is Null, it is treated as False.
  • Statements: These are statements repeated when the While or Until loop is used until the condition is True.

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.

Types of VBA Loops

VBA Do loops are of two types: 1) Do-While loop and 2) Do-Until loop.

#1 – VBA Do…While loopDo…While loop is used when we want to repeat a statement multiple times till the condition is true. In this case, the condition is checked either at the start of the loop or at the end after the loop is executed at least once.

#2 – VBA Do…Until loop – Do…Until loop is used when we want to repeat a statement multiple times till the condition is false. Like the Do…While loop, the condition will be checked at the start or end of the loop.

How to use the VBA Do Loop?

Let us take an example of the Do…Until loop in Excel VBA. Here, as we know, the loop will be executed until the condition is false. So let us understand each step of using VBA Do…Until for executing a simple task of filling cells with a given value.

Step 1: Open a module in a VBA editor, as shown below:

VBA Do Loop -Use - Step 1

Step 2: Start writing the VBA code using the sub-procedure and define a VBA variable type as an Integer to enter a numeric value.

VBA Do Loop -Use - Step 2

Step 3: Assign the variable X with the value 1. It suggests that the value will be printed from the first cell.

VBA Do Loop -Use - Step 3

Step 4: Now add the Do…Until loop and write its condition. For this, specify the value till where we want to fill the cell. For example, let’s say cell 5. So, we write X>5.

VBA Do Loop -Use - Step 4

Step 5: Write the statement for assigning the value which we want to fill as shown below:

VBA Do Loop -Use - Step 5

Step 6: Increment the variable X by one so that the loop iteration can be performed and close the loop.

VBA Do Loop -Use - Step 6

Step 7: Run the code using F5, and we will see the result as shown below:

VBA Do Loop -Use - Step 7

Examples

Let us look at some scenarios where we use the Do…loop and how the control works.

Example #1 – Condition at the end of Loop

In this example, we will use the Do…while loop when the condition is placed at the end. The loop is executed at least once, even when the condition is false.

Step 1: Write the sub-procedure and define a variable as an Integer data type.

VBA Do Loop - Example 1 -  Step 1

Step 2: Assign the variable value X=4. It means that the value will be printed from the fourth row.

VBA Do Loop - Example 1 -  Step 2

Step 3: Assign the value which we want to see in the cell A4 as shown below:

VBA Do Loop - Example 1 -  Step 3

Step 4: Increment X by one for the loop iteration, X=X+1. It means the row is incremented by one, and the 5th cell is filled with the assigned value.

VBA Do Loop - Example 1 -  Step 4

Step 5: Now. in the end, add the Do…While loop and write the required condition. Here we give X < 3.

VBA Do Loop - Example 1 -  Step 5

Step 6: Run the code. The value is written only once in the 5th cell and exits the code.

VBA Do Loop - Example 1 -  Step 6

Explanation: The loop is executed once even though the condition is false as the Do…While loop is used at the end.

Step 7: Let us use another condition to understand the loop. If X<10, then the loop will be executed from A4-A9 till the value of X becomes 10.

VBA Do Loop - Example 1 -  Step 7
VBA Do Loop - Example 1 -  Step 7 - Output

Code:

Sub DoWhile_Ex()
Dim X As Integer
X = 4
Do
Cells(X, 1).Value = 1999
X = X + 1
Loop While X < 10
End Sub

Example #2 – Summation Using Do….While Loop

In this example, we will calculate the sum of each row in two columns using the Do…While loop.

Step 1: Write the values in the Excel cells as shown below.

VBA Do Loop - Example 2 - Step 1

Step 2: Write the sub-procedure in the VBA editor and define a variable X as an integer.

Example 2 - Step 2

Step 3: Assign the value X =1 and use the Do…While statement with a condition as shown below:

X = 1

Do While Cells(X, 1).Value <> “”

In the above line, we are executing the loop until the first column has no blank cell as the loop iterates through each row.

Example 2 - Step 3

Step 4: Provide the condition for the sum of cells in rows of two columns. Here, (X,1) means the cells in column A, and (X,2) represents the cells in column B.

Code:

Sub Summation_Do_Loop()
Dim X As Integer
X = 1
Do While Cells(X, 1).Value <> “”
Cells(X, 3).Value = Cells(X, 1).Value + Cells(X, 2).Value
X = X + 1
Loop
End Sub

VBA Do Loop - Example 2 - Step 4

Step 5: To see the result, run the code, and you can see the sum of the values in columns A and B in the corresponding cell in column C, as shown in the image:

VBA Do Loop - Example 2 - Step 5

This loop is executed till the code encounters a blank cell in Column A.

Example #3 – Exit Statement in Do…While Loop

VBA Exit statement is used to exit a loop even if the condition still holds true. It is used to leave an endless loop using the If..Then..Else statement, which analyzes the given condition and exits the loop.

Let us see an example to understand this better. Here we will use Do…While for printing the squares of numbers 1 to 10 in Excel. Then, we will use the Exit statement to exit the loop based on the condition provided.

Step 1: Write the code starting with the sub-procedure in the VBA editor and define the variable as an Integer.

Example 3 - Step 1

Step 2: Assign a value to the variable X and add the Do…While loop to execute the code when X<=10. It means the code should be executed ten times.

Example 3 - Step 2

Step 3: Use the If statement for the condition, X>6. Then, use Exit Do to exit the loop after the 6th iteration.

Example 3 - Step 3

Step 4: Write the code to calculate the square of the numbers in a cell as shown below:

Example 3 - Step 4

Step 5: Run the code and see the result up to 62, as shown in the image below. Here, the control exits the loop when the X value exceeds 6.

Example 3 - Step 5

Code:

Sub Do_Exit_Ex()
Dim X As Integer
X = 1
Do While X <= 10 If X > 6 Then Exit Do
Cells(X, 1).Value = X * X
X = X + 1
Loop
End Sub

Important Things to Note

  • The VBA Do loop allows us to analyze a set of values one by one.
  • VBA Do loop is of two types, Do…While loop and Do… Until loop.
  • In both types of Do loop, the condition can be checked at the start or end.
  • The VBA Do loop is generally used to loop in each cell, worksheet, and workbook.
  • The VBA Do loop counter counts the number of cells. These are used in VBA to declare a counter variable, store an initial value, and increase the counter variable by an integer (say 1).

Frequently Asked Questions (FAQs)

1. How do you end a Do Until loop in VBA?

We can end a VBA Do…Until loop using an Exit Do statement or turning the condition from False to True. We can consider any number of Exit Do statements at any place in a Do loop.
It can be applied for both Do While and Do…Until loops. When we use the Exit Do loop, the control shifts directly to the following statement after the Do loop.

2. Can you use Else in a Do-While loop?

Yes, we can use Else in the Do…While loop to check the condition for Do Exit in VBA Excel and prevent endless looping.

3. Can for loop be replaced by Do-While loop?

Yes, the For loop can always be replaced by a Do-While loop. The For loop is also known as a count-controlled loop, which means we know how many times we will run the loop. Do…While loop is also known as a conditional loop, as we do not know how many times the loop will run.

This has been a guide to VBA Do Loop. Here we explain the steps of using VBA Do…Until for executing a simple task with examples. You may learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *