Beginners guide for how to use FOR Loop in Excel VBA
Keeping the beginners in mind, I am going to write this article about For Next loop in Excel Macro. Also one of my friend wanted such post to be published. First of all i would like to thank for such input and feedback. Even if you want any help or any article to be published, kindly mail me.
If you want to learn Excel Macros then read articles from Excel Macro Tutorial
So coming to the Article, mainly there are following Types of Loops which are used.
In this Article, I am going to Explain you about For Next Loop. For Other Two loops While and Do While Loop, you can refer the next article.
How to use FOR Loop in Excel VBA
In this Article you will learn about For Next loop. This is the most frequently used loop. It enables you to execute a set of VBA statements multiple number of iterations.
Since it is a loop, it requires
1. An Index or Incremental variable which counts the total number of iterations after completion of each iteration..
2. Such a condition where this loop should end.
Note: Be careful with this condition. If it is such a condition which may never end, then it is infinite loop and your program will crash.
FOR LOOP Syntax in Excel VBA
For index=start to end [statements] …………………. …………………. …………………. Next |
Example:Now let’s take an example to understand how exactly this for loop works. Suppose you need to calculate the Sum of first 10 Numbers from 1 to 10.
Sum = 0 For iCount = 1 To 10 Sum = Sum + iCount Next MsgBox Sum
Explanation: In the above example Variable “i” is the index variable which is set to 1 from Start. So in the first iteration the Value of i is 1. Control will go inside the loop and execute the statement like “Sum=Sum + iCount”. When it reaches to “Next” then by default it will increase the value if i by 1. So for the next iteration the value of “iCount” will be 1+1=2.
Every time before getting in to the loop, one condition (i<=10) will be checked. This condition is not specifically mentioned in the above code, but this is the For Loop property. Loop will keep going until the value of “i” reaches to 10.
When the value of i reached to 11 then the Condition (iCount<=10) is failed and then control will come out of the For Loop and loop will be ended.
How to Exit from For Loop in Excel VBA
Now as you can see, in the above example, For..Loop will run for 10 times and then only it will exit from the loop. Sometimes when you want to exit from the loop even before the FOR condition is failed. Using Exit For one can terminate the for loop any given point of time.
Sum = 0 For iCount = 1 To 10 Sum = Sum + iCount If Sum > 30 Then Exit For Next MsgBox Sum
In the above example as soon as the Value of Sum is greater than 30 then Loop will be terminated though the value of “iCount” is still less than 10.
What is For Loop with Step
So far you see that every time For Loop index is incremented by 1. So what if you want to increase the index by 2, 3, etc. each time?
It’s simple, here is the syntax to use STEP keyword in FOR NEXT Loop in excel VBA.
Syntax
For [index=start] to [end] Step [interval] [statements] …………………. …………………. …………………. Next |
Example:In the below example in each iteration the Value of i will be incremented by 2, instead of 1. If we don’t mention Step [interval] then by default it increases by 1.
Sum = 0 For iCount = 1 To 10 Step 2 Sum = Sum + iCount Next MsgBox Sum
For Loop in Descending Order
Sometimes you need to Run your For..Next Loop from a bigger value to a smaller value. It means in every iteration, you want to decrease the Value of “iCount” by a specific interval.
Now as you know how to set interval for each iteration in your For Next Loop using STEP keyword, all you need to set to interval to a negative value. This way after every iteration your For Next Loop iteration value will be decreased by specified interval.
Example:Now let’s take an example to understand how exactly this for loop works. Suppose you need to calculate the Sum of first 10 Numbers from 1 to 10.
Sum = 0 For iCount = 10 To 1 Step -1 Sum = Sum + iCount Next MsgBox Sum
In the above example, “iCount” value will start from 10 and it will be decreased by 1 every time because the interval value is set to -1.
In the Next Article, you learn about While and Do-While Loop. |
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial |
[…] previous Article, we had seen about For Next Loop. In this article we are going to see about While and Do While […]