Excel VBA Loops
Loops can be considered as the best functioning component of VBA and can also be considered as the heart of Macros. They usually help you reduce the number of lines in your code and do the work in minutes which can take hours to a human being.
In day to day life, a programmer comes up with such cases where he needs to run the same set of code again and again. It is called looping. Obviously, every time running the same code will give around the same result with some fractional change in the variable. Looping has a large set of advantages, but the most important of all is automation. It helps to automate the stuff.
If you have not used VBA Script before, you can just open an excel workbook and hit Alt + F11. It will open a VBA Project.
Let’s get deeper inside.
Suppose we wanted to print 1 to 10 numbers at a single shot. We can do it with the following code.
Code:
Sub loop1() Debug.Print 1 Debug.Print 2 Debug.Print 3 Debug.Print 4 Debug.Print 5 Debug.Print 6 Debug.Print 7 Debug.Print 8 Debug.Print 9 Debug.Print 10 End Sub
If you can get through the code quickly, I have added Debug.Print one by one to print numbers from 1 to 10. After running the code we can see the output in Immediate Window. To open Immediate Window, Press Ctrl+G anywhere in Excel.
Run the code using the F5 key or manually as shown in the below screenshot. So let’s check the output for this code.
Types of VBA Loops in Excel
Being a programmer, you will never be keen on writing repetitive lines which are about to give the same result. You would like to automate it. Hence we will see various Excel VBA Loops and how to use them using some examples.
Type #1 – VBA For Next Loop
For Next VBA loop allows us to loop through the cell range and perform the same task for each cell specified in the loop. The starting number & end number must be indicated here.
code:
Sub loop1() For i = 1 To 10 Cells(i, 1).Value = i Next i End Sub
This code gives exactly the same output as the previous one. However, the most noteworthy thing in this code is the reduction of the number of lines. We have reduced the 12-line code to merely a code with 5-lines.
In this code, loop1 is the name of the macro. The for loop starts from i = 1, it prints the value of i (i.e. 1) under excel cell and at Next, it goes again to the start of the for loop and makes i = 2 prints the value of i (i.e. 2) in next cell and so on till the code reaches its last value i.e. 10. As soon as the code reaches the last value of i (10), the code gets terminated itself due to the non-availability of any integer value above 10.
Run the code using the F5 key or manually. So you can see the output as below.
Type #2 – VBA For Loop with Forward Step
As you have seen in the previous loop example, I was having an increase in 1 step. By default, the step value is considered as 1 in VBA. However, is it possible to set a step of our own? Suppose you only want to see the even numbers between 1 to 20. How it can be done?
As general mathematics suggests, the numbers which are at the distance of 2 from each other (Starting from a first even number 2) are considered as even numbers, right?
We will apply the same logic. See the below code for your reference.
Code:
Sub ForwardStep() For i = 2 To 20 Step 2 Cells(i, 1).Value = i Next i End Sub
For the values starting from 2, this loop enables to print 2, 4, 6, 8…20 in subsequent excel cells. Step 2 explicitly has given so that starting from number 2, the code will jump to the next number with step 2 (i.e. 4 will be printed) and so on. This process will continue until 20 occurs in a cell. As soon as 20 occurs in a cell, the loop will be terminated automatically.
Run the code using the F5 key or manually. So you can see the output as below.
Type #3 – VBA For Loop with Backward Step
In the same way, as for loop with forward step, we can use for loop with a backward step. For example, what if we set our backward step as -2 and run a for loop from the values 20 to 2?
We will see how it works.
code:
Sub BackwardStep() For i = 20 To 2 Step -2 Debug.Print i Next i End Sub
After running the code we can see the output in Immediate Window. To open Immediate Window, Press Ctrl+G anywhere in Excel.
As we have given a starting point of the loop as 20 and ending point as 2 with step -2, the loop starts from 20 and in the step of -2 it goes on to the next number which is -18 and so on. As soon as the loop reaches value 2, it gets terminated automatically and the last value will be printed as 2.
Type #4 – VBA Nested For Loop
We can also Nest theFor loop. However, it is not suggested to do nesting of the same loop more than thrice in a code (It can be considered as an error in logic).
Please see the below code for nested for loop.
Code:
Sub NestedFor() For i = 1 To 10 For j = 1 To 2 Cells(i, j).Value = i * j Next j Next i End Sub
In this example, at i = 1 and j = 1, macro puts the value of i * j (1 * 1) into intersection of row i = 1 and column j = 1 (i.e. cell A1) and goes for the next value of j (2) keeping i = 1. Into the intersection of row i = 1 and column j = 2 (i.e. cell B1), macro inputs the value of I * j (1 * 2). Loop ignores the next value of j as j varies only from 1 to 2 and goes to the next value of i.
For i = 2 and j = 1, macro enters the value of i * j (2 * 1) into the intersection of 2nd row and 1st column (i.e. cell A2) and goes on. Until the range of i and j.
Run the code using the F5 key or manually as shown in the below screenshot. So you can see the output as below.
Type #5 – VBA Do While Loop
Do While loop is one of the most interesting loops in VBA. It allows, you to check the condition and write the output (DO), when a certain condition or set of conditions met (or the condition/s is/are TRUE).
See the code below:
Code:
Sub do_whileloop() Dim i As Integer i = 1 Do While i <= 10 Cells(i, 1).Value = i * i i = i + 1 Loop End Sub
This loop works as below:
For the values of i from 1 (i = 1 initially) to 10, it calculates, i * i (i.e. squared value for each integer value of i) and inputs it under cell 1 to 10 respectively. The output is nothing but the squared values of integers 1 to 10. Please take a closer look at Loop under this code. It is mandatory to add it, otherwise, the system will shoot an error.
Run the code using the F5 key or manually. So you can see the output as below.
These are some of the VBA loop examples which I have tried taking care in this article. Hope it will be precise enough to clear some concepts.
Things to Remember
- While using for loop with a backward step, make sure your starting number must be greater than your ending number and vice a versa.
- Under Do while loop, make sure you are using Loop to close the do while before ending the entire VBA Code. Otherwise, the system throws a compiler error with the error message “Do Without Loop”.
You can download this VBA Loops Excel Template here – VBA Loops Excel Template
Recommended Articles
This has been a guide to Excel VBA Loops. Here we discussed Types of Excel VBA Loops and how to use them along with some practical examples and a downloadable excel templates. You can also go through our other suggested articles–
