EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Logical Functions VBA Loops
Secondary Sidebar
VBA Logical Functions
  • VBA Logical
    • VBA Do While Loop
    • VBA IF Statements
    • VBA Loops
    • VBA Select Case
    • VBA Else If
    • VBA While Loop
    • VBA Select Cell
    • VBA Break for Loop
    • VBA IF Not
    • VBA Do Until Loop
    • VBA OR
    • VBA Boolean
    • VBA Like
    • VBA For Each Loop
    • VBA Operators
    • VBA Selection
    • VBA DoEvents
    • VBA Do Loop
    • VBA Not
    • VBA With
    • VBA AND

VBA Loops

By Madhuri ThakurMadhuri Thakur

Excel VBA Loops

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

vba window

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

debug print

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.

Debug.Print

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

for next loop

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.

 For Next Output

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

ForwardStep

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.

ForwardStep Output

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

Backward Step

After running the code we can see the output in Immediate Window. To open Immediate Window, Press Ctrl+G anywhere in Excel.

Backward Step Output

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

vba loops example 4-1

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.

Nested For

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

do while loop

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.

Run the code using the F5 key do while loop

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–

  1. VBA Function
  2. VBA VLOOKUP
  3. VBA XML
  4. VBA IsError
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more