Excel VBA For Loop Example 1

in this video we'll see an example and type of a looping system that is a for loop there are multiple loops in the loops part let's say for example for loop do loop do-while loop do until loop and so on so these are different different types of looping part in this video we'll focus on the for loop I'll show you some example in this place now what exactly is for loop used for if you want to iterate any statement if you want to repeat any statement multiple times you will be going for for loop now for loop is totally based on the number condition it will keep on executing number of times okay so the condition based on the number part now for example in this video what will show is I want to display a message box ten times okay message box some number ten times so do I need to write ten times message box and so on no I can just write a message box only one time and I will add a for loop for it so I can say I want to execute message box ten times so let us see with help of example now I am in the same file that is Excel VBA building codes and open some sheet nothing related to the sheet now okay I'll go to the Developer tab and the Visual Basic now in this place I have created a module named as for loop okay in this place there is some macro which is written here now in this macro it's very very simple just three new lines I have added three new lines nothing more than that now what exactly are three lines everything is for the for loop itself so please here now if I want to display a message box mail multiple times so you can see what I've done I have created a procedure for loop that is you already know I have declared a variable dim excess integer even if you don't declare a variable still it's brought it is not a problem dim excess in integer next is for X equal to 1 to 10 okay for X equal to 1 to 10 minutes X variable will have number 1 2 3 4 5 till number 10 it will keep on repeating again and now the last thing is I have written as message box 25 I want to display a number that is 25 okay and finally that is the next to end the for loop I have to write as next and finally end sub procedure so if you observe here 1 2 & 3 lines are already new this line you already know how to display a message box this one and this one that is 4 X equal to 1 to 10 this number can change this numb can change it can be as per your choice now if I execute at one time message box will be displayed ten times if you observe if I want to execute the entire code at one go I can just click on this play button okay I'll close I will keep my cursor at this place click on run button now if you see message box 1 2 3 4 5 6 7 8 9 and then 10 so there is a 10 part which is repeated 10 number of times it has repeated so for loop let me understand how it repeat it for 10 times so what I'll do is I'll execute step by step so if you focus here if I keep my cursor at this place and if I say step into step into step into okay now if you want to learn the value of X what you can do is you can add to watch okay so you can see here you can go to debug and I will show you add to watch and here I will type expression as X and click on okay so observe here what is the value of X now it is 1 ok if I execute again step into now it executed message box 25 is so message box 25 it will go to this next okay and observe here what will happen X value if I click on step into now the x value is 2 so what will happen is it will work for the second time message box 25 okay second time and let's see the third time again it came to this place x value chemistry so till the value of X keeps on repeating till 10 it will keep on executing now if you observe I will just click on this place step into step into I'll execute a bit faster so at the end when the number 10 comes I can show you the value here okay execute step into again step into again okay now X value is 7 till 10 I will keep on repeating step into ok step into step into ok so it is going back again to this place step into step into okay now the x value is 10 what will happen if I execute it again finally after this place when the value of X becomes 11 it will come out of the loop itself when the value is already 10 it will keep on repeating 11 the value if you see it came out so what is this watch it is used to keep a track on variable what is the value of variable at particular time so value of this X variable X expression is 11 at this place okay and 5 it came out and I will just click on this step in to write that is pardon hearing so I hope able to understand how to use a for-loop it's very very simple now if you want I will give one more example instead of this 25 every time it is displaying 25 value I do not want value as 25 I want value of x and that particular iteration so I can just remove this 25 okay I will put the value as X now what will happen is first time value was value of x is 1 so here it will become as one message box one next time of X will be 2 message box 2 3 4 5 so if I click on this Run button and if you observe now early it was message box 25 now the value will keep on changing by 1 okay 2 3 4 5 6 7 8 9 observe after 10 it should stop after 10 it stopped right so it's working fine so this message box X it will give the value of x as particular iteration or at particular value of X itself right now if you observe the value of x was incremented by 1 if you want to increment a value of x by 2 put a space step 2 that's it for X equal to 1 to 10 step 2 now what will happen is you not get value as 1 2 3 4 5 but you'll get something else if you observe if I if I keep my cursor click on Run button now observe now it is 1 now it became 3 that means interval of 2 1 plus 2 3 3 plus 2 5 5 plus 2 9 5 + 2 7 7 plus 2 9 and finally it will come out of the coding itself right so this step 2 is used to increment a value by 2 itself if you want you can change this step 2 - let's say step 3 and if I click on Run button so after 1 it should show as 4 after that 7 and finally 7 and 3 total 10 and it came out of so so it is incremented by 3 value okay and it is working fine here itself so step is a thing which is used to increment value okay by some number you can see the number which is written it will keep on incrementing now if I don't write anything here what will be the default if you don't write anything by default it will be step one okay so the number will be incremented by one itself right so I hope you will able to understand how do we for loop it's a very basic simple example in the next video I'll show you some more complicated and better example that's all for this video

Channel: Tutorials Point (India) Ltd.

Views: 212,759

Rating: 4.8472223 out of 5

Keywords: Excel VBA For Loop Example 1, VBA, Marcos, Microsoft, VBA Excel

Id: kKonIb8SDoM

Length: 6min 43sec (403 seconds)

Published: Tue Dec 06 2016