Excel VBA Do Loop
There are 2-3 types of loops in VBA. Some loops work when the condition is TRUE, and some works when the condition becomes FALSE. Excel VBA Do Loop is such a kind that works till the condition is TRUE, and it exits as the condition becomes FALSE. Do Loop is of 2 types, which is Do-Until loop and Do-While Loop. Do Loop starts working only when the condition satisfies the requirement, and then we need to define the exit condition as well when the loop is stopped. If we do not define this, then the loop will run until the defined data types limit.
Below is the syntax of Do Loop:
Do While/ Until [Condition]
[Statement]
Exit Do
[Statement]
Loop
How to Use Do Loop in Excel VBA?
We will learn how to use Do Loop in Excel by using the VBA Code.
VBA Do Loop – Example #1
In this example, we will see how Do Until Loop works. We will run the condition where the Do loop will print the number until the condition is TRUE and get excited once the condition becomes FALSE.
Step 1: Now, open a Module from the Insert menu tab firstly, as shown below.
Step 2: In that, write the subprocedure of VBA Do Loop as shown below, or you can choose any name to define the name of the code.
Code:
Sub VBA_DoLoop() End Sub
Step 3: Define a variable as an Integer. It is because we will be using numbers with Do Loop.
Code:
Sub VBA_DoLoop() Dim A As Integer End Sub
Step 4: Assign a value to variable A. Let’s say the value is 1. This value is the cell position from where we will be seeing the values getting printed. Here, the values will be seen from cell A1. If we choose 2, then the value will be printed from cell 2.
Code:
Sub VBA_DoLoop() Dim A As Integer A = 1 End Sub
Step 5: Now open Do Until Loop, as shown below. This is where we will be writing the code.
Code:
Sub VBA_DoLoop() Dim A As Integer A = 1 Do Until Loop End Sub
Step 6: Now write the condition for Do Until Loop. Let’ say we want to see the numbers until cell 5.
Code:
Sub VBA_DoLoop() Dim A As Integer A = 1 Do Until A > 5 Loop End Sub
Step 7: Write the statement where we will see the number from the first cell of column A (Column 1). And also, choose the value which we want to see. Here we have chosen number 20.
Code:
Sub VBA_DoLoop() Dim A As Integer A = 1 Do Until A > 5 Cells(A, 1).Value = 20 Loop End Sub
Step 8: Now give the exit statement at last. Here we want to exit from Do Until loop when it reaches the +1 cell of starting cell.
Code:
Sub VBA_DoLoop() Dim A As Integer A = 1 Do Until A > 5 Cells(A, 1).Value = 20 A = A + 1 Loop End Sub
Step 9: Compile the code and run it by clicking on the Play button located below the menu bar, as shown below.
Step 10: We will see the number is been printed from cell 1 to 5 with the number 20.
Step 11: Now if change the reference starting point from 1 to 2 in variable A and see what we will get.
Code:
Sub VBA_DoLoop() Dim A As Integer A = 2 Do Until A > 5 Cells(A, 1).Value = 20 A = A + 1 Loop End Sub
Step 12: Again, run the code. We will now see the values printed from cell 2 (which is the reference cell) to cell 5 (the last cell).
VBA Do Loop – Example #2
In this example, we will see how Do While Loop works. Do While also works when the condition becomes TRUE. As per the syntax, Do run the defined condition While the selected statement is TRUE and Do exit from the loop when the exit condition is TRUE. We will use Do While loop and add a number that is already created numbers and get the output in a different column.
For this, follow the below steps:
Step 1: Write the subprocedure for Do While loop as shown below.
Code:
Sub VBA_DoLoop2() End Sub
Step 2: Now, for this example also, we would need a variable and reference point from where we will be seeing the numbers. So, consider the first 2 steps from example-1, as shown below.
Code:
Sub VBA_DoLoop2() Dim A As Integer A = 1 End Sub
Step 3: Now open Do While Loop syntax as shown below.
Code:
Sub VBA_DoLoop2() Dim A As Integer A = 1 Do While Loop End Sub
Step 4: Now write the condition where Do While will run the loop while cell value is not equal (<>) to Blank. Also, consider the input of the value to the cells from the first column of the sheet.
Code:
Sub VBA_DoLoop2() Dim A As Integer A = 1 Do While Cells(A, 1).Value <> "" Loop End Sub
Step 5: Now, from the second position of the sheet, get the output by adding a number to each cell that is not blank. And let say that number be 5.
Code:
Sub VBA_DoLoop2() Dim A As Integer A = 1 Do While Cells(A, 1).Value <> "" Cells(A, 2).Value = Cells(A, 1).Value + 5 Loop End Sub
Step 6: And in the exit condition, write the condition to exit from the loop if the next cell is Blank.
Code:
Sub VBA_DoLoop2() Dim A As Integer A = 1 Do While Cells(A, 1).Value <> "" Cells(A, 2).Value = Cells(A, 1).Value + 5 A = A + 1 Loop End Sub
Step 7: Again, run the code by pressing the F5 key or by clicking on the play button. We will see, in the next column to A, which is B, the new number list has been added with an addition +5 to each cell having values.
Step 8: Now, suppose if we delete a cell value from in between the list.
Step 9: After running the code again, we will see there is a break at cell A8 so that Do While got exited from there. And added +5 each cell number till A7, which has the output from B1 to B8.
Pros of Excel VBA Do Loop
- It is so easy to implement and quick to run that it doesn’t take time to finish.
- We will write as a typical condition we want if that is TRUE.
- We can use both Do While and Do Until Loop in combination.
Things to Remember
- The best way to exit from the loop is the give the FALSE condition nearer to the TRUE condition.
- We can use more than 1 condition in Do Loop if those conditions are TRUE.
- It is necessary to give Exit conditions. If we don’t, then the loop will continuously run until it has the limit or will never stop running.
- Save the excel file into Macro enable format to retain the code.
Recommended Articles
This is a guide to VBA Do Loop. Here we discuss how to use Do Loop in Excel VBA along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
