VBA For Loop - A Complete Guide

today i'm going to show you everything you need to know about creating far loops in vba by the end of this video you'll be able to create any for loop you need in vba whether it's for ranges collections arrays or something else make sure to download the 20 for loop examples from the description below the video so let's go ahead and get started if you like this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it so what is the for loop and why do we even need it imagine we want to print number one to the immediate window we use the debug print value one to do this when i click run you can see it printed one to the immediate window let's print out the numbers one to five by copying this line four more times now when we run the code you can see it printed out the numbers one to five to the medial window this is all very simple and straightforward so far however we have a major problem imagine we want to print out 1000 numbers or even 50 000 numbers obviously it's not feasible to write the depot print line 1000 times let's have a look at how the for loop solves this problem we type far i equals one to five and then we just need one debug print line we use i for the number and we declare it before the loop using dim so now the very same code we run it and it prints out one to five now if we decide we want to print out one to a hundred we only need to make one simple change now when we run it it prints out a hundred numbers to the immediate window no matter how many times we want to print a number we only need one debug print line a second problem the for loop solves is flexibility for example imagine we want to print out the range of numbers based on user input without the for loop we are stuck but with the for loop we can use a variable to decide how many times the loop will run this time we create a for loop using the number from the user to set how many times it will run when we run this code vba says please enter number and we enter the number 12. we click ok and you can see that it printed out the numbers 1 to 12. now if we run it again and this time we entered number 7. you can see it prints out the numbers one to seven so now you can see how flexible our code is by using a for loop the for loop does two things it saves us writing lines of code over and over and it makes our code flexible this is the format of a for loop the first thing we need is a variable it is standard practice that the for loop variable is always i the next thing is the starting value in most cases this number is 1 but in some scenarios you may use another number the end number is almost always a variable as it makes our code flexible as we have seen in this example we'll use the number 10. the final thing is step which you really need to use so step one is the default and this means use every number in the range step two means use every second number and step three means use every third number so for example if we run the code for i equals one to ten step 2 you will see that the result is 1 3 5 and so on the loop steps by 2 each time now in reality we rarely use step like this one way though that we might use step is to read through a loop in reverse for example if we wanted to read two rows or a collection in reverse order in this case we use step minus one and when we use a negative number we have to swap the start and the n numbers so the n number must be less than the start number now when we run the code you can see that it printed out the numbers in reverse now let's step through the for loop and see exactly how it works i'm going to use the debug toolbar to step through the code you can also use f8 as the shortcut when we enter the loop the value of i is 0. now as soon as we step past the far line i is set to 1. i will have the value 1 until we reach the next i line in the loop now when we step past the next line a couple of things happen first of all next gets us the next number in the range which in this case is the number two and if that number is still within the valid range then we go back to the first line in the loop but i is now set to two this time when we go to the end of the loop i is set to 3 and we go back to the start of the loop again and so on but now it gets interesting this time next will give us the value 4 but because 4 is greater than 3 which is the maximum value of our loop we exit the loop automatically so if you look in the locals window you can see that i is now four if you ever feel confused about how the for loop works then structure it line by line just like this and it will really help you understand it much better now we're going to look at nested loops a nested loop is when we've got a loop within another loop so why would we even need to do that well let's have a look at the numbers here so with these numbers here we want to read through all the rows but for each row then we want to read all the columns so we want to read a value in each column so if we go back to our code you'll see at the moment what it does is it just simply reads through all the rows and it prints out the first item so we'll run this code you can see we get one five nine which is basically column one of each row now if we wanted to read everything in the column what we would do is we create a second for loop so we say j as long let's make that j and then we say for j equals one to range dot columns dot count now it's important that default and the next of of j must be inside the loop so loops can't overlap the loop must be fully contained inside the loop so now if we do an i and a j here and let's run this code to see what happens and then we'll step through it so we can understand it completely so we run the code and you can see i is one in the first line j is one and the value is one so then the next line i is still one j is two and then the next one i is one j is three and so on so then you'll see that i becomes 2 and you see j goes back and does 1 2 3 4 and then you see i becomes 3 and j does 1 2 3 4. so once you get into how it works it becomes quite clear let's step through this code and we'll have a look at our locals window so we can see the values as we step through it so let's bring up the magnifier so that we can see exactly what's what our values are so as we step through the code i now becomes one we're in the far eye loop so i becomes one we go into the for j loop and now j becomes one so we print out the line and then we're looking for the next j so the next j is going to be two and that's still within our range and so this means we go back in and now we've got i and j equals two and then we say next j j equals three and then j equals four so when j equals five it it goes out of that loop and we're on to next i so that works the same way it simply says give us the next i which is two and if it's valid continue in the loop so we're back at for j equals one and what this will simply do is just reset this loop again and run true one to four again with j being one two three and four and so this happens again and then we go to for i and of course this becomes three and then j resets and one two three four and so on so this is how a nested loop works in excel vba and very useful for reading rows and columns but if your nested loop is more than too deep then it's worth kind of looking at your code and seeing should you design it a bit better sometimes we don't want to complete the for loop if a condition is met we may want to leave the for loop early so how do we leave the forward loop early well what we do is we use exit for so why would we use it so if you look at the data that we have here you can see what we're trying to do is find 90 as the id now as soon as we find 90 and do what we want to do we no longer want to keep reading through the data so we put an exit far afterwards so let's put a breakpoint here run the code so you can see it stops here when we find 90 which is when i equals four and then we step past that and we just simply exit the for loop because as i said we don't waste time reading through data when we've already found what we're looking for so you can see here i is four but we've exited the for loop now we're going to look at reading through a collection so as normal we declare our variable de may is long and then we say for i equals one two and it's the collection name and we use the count property so this gives us the number of items in a collection and then when we use debug print we say the collection and then we have parentheses and i which is the current item in the collection now when we run this code what you'll see is that it printed out all the items in our collection now if we wanted to print out all the items in reverse in the collection then we use step as i showed earlier we do step minus one and then we do from call count to one so this is important that we start with the bigger number now when we run the code you see that it roll mango pair apple writing out the items in reverse now one thing about using far eye with a collection is that it's quite slow so if you're dealing with a lot of data in a collection you should always use for each so the way we use varh is we declare the item as a variant and then we say for each fruit in our collection and then we just use the fruit variable to access the item so now when we run this code it will print out all the items in the normal order so for each very important if you're dealing with a lot of data because it's much faster than far eye now we're going to look at reading through a one-dimensional array so we use for i equals one two and we use u-bound so what u-bound does is it gives us back the position of the last item in the array so we say for i equals one to u bound and one is basically the first dimension if we have a two dimensional array two would mean the second dimension so now when we do debug print we simply use i on fruit and when we run this chord you'll see that it printed out everything as we expected now the one problem here is that rays don't always start at one or zero they can start at different numbers so instead of saying for one two what we say is for l bound and l bound will always give us the first position of the array now if we run the array here as you can see we've set it to zero if we run the array here it actually misses out apple because we have one in the position so what we do instead is we say l bound fruit and that gives us the first position and no matter what way the array is set when we run the code it will print out all the items the first two lines of code here they read a range of data into a two-dimensional array and so what we're going to do is read through the two-dimensional array you can see the data on the screen that we're reading and then we're going to view the locals window so we can see exactly what our two-dimensional array looks like if we open the first one we click on the plus you can see id and data which is the header and the second one is 75 and apple 54 in orange and so on so let's put all this data into a two-dimensional array and we're going to read you the two-dimensional array so how we do that is very similar to how we used the nested loop earlier we declare j as the variable and then what we'll do is we'll just simply copy this line and instead of having through one in l bound and new bound we put through two and what this means is that instead of giving us the lowest and the highest index of the rows it gives us the low and highest index of the columns of our array and then we put i and j in fruit then when we run the code you can see all the data from the two-dimensional array in our immediate window to learn more about for loops make sure to check out my videos on for loops and collections and remember you can also download the source code for this video that has 20 for loop examples if you liked this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it

Channel: Excel Macro Mastery

Views: 13,436

Rating: 4.9869494 out of 5

Keywords:

Id: SgzcZp-jEq4

Length: 12min 36sec (756 seconds)

Published: Thu Jun 10 2021