How to Master VBA loops FAST (with real coding examples)

in VBA we have many different ways of writing loops and as you can imagine this leads to a lot of confusion so in this video I'm going to simplify the whole process of using Loops I'm going to tell you when to use each one and which ones you can avoid so let's go ahead and get started before we look at the different Loops we should understand why we need Loops in the first place imagine we wanted to print a numbers 1 to 5 to the IM media window we could use this code now this code is Impractical for two reasons firstly we need a line of code for each number and if we had a th000 numbers then we would need 1,000 lines of code and secondly this code is not flexible it always prints out exactly five numbers and in the real world we want our code to handle any number of items that we give it we can replace this code with a for Loop and now we can change the number of times we print by simply changing the five to any number we want and we can change this value to be variable which means our Loop is now flexible so now that you know how powerful Loops are let's look at the far Loop in more detail the far Loop is by far the most used Loop in Excel VBA we use the far Loop when we know in advance how many items we are dealing with this code here will Loop through all the rows in a range and obviously this can be different each time the code runs however when we reach the loop we know exactly how many times it will run for example here row counted is five so we know that the far Loop will run exactly five times and this is the major difference between far loops and while and dowh Loops as we shall see shortly we typically use the far Loop when we are reading through a range reading through an array reading through any type of collection such as the standard collection or worksheets workbooks and so on now this takes us to the far each Loop we've already seen that the far Loop can be used to ret true group of items such as a collection or an array so we can also use the for each Loop in the same scenarios as the standard for Loop for example we can read through a collection using either Loop the main difference between them is that for each is Neer to write and much faster when used with collections for example compare these two Loops here for each is Neer because it doesn't require the index and it also makes it less prone to errors the limitation of the for each Loop is that you cannot always dictate the order for example if you read worksheets using the forage Loop it will always read them left to right but if you want to read them in reverse then you must use the standard for Loop the second limitation is that if you need the index then you will have to use the standard for Loop for example here we want to return the row number so it makes much more sense to use the standard for Loop in this case or otherwise we have to create our own count in terms of speed the for each Loop is significantly faster than the standard for Loop when reading through a large collection however interestingly it's worth noting that it's not the actual Loop that is low but the retrieval of an item using an index conversely the standard far Loop is faster reading to arrays than the far each Loop however it is not significantly faster so in a nutshell use the for each Loop if you're dealing with some kind of collection and if you need the index or want the items retrieved in a different order then use the standard far Loop there are two remaining Loop Loops in VBA the while loop and the do Loop the do Loop can be written in four different ways which leads to confusion but I will show you a simple way of understanding this Loop in a moment now in the example on the screen we have a while W Loop and we have a do while loop the loops in this example work in exactly the same way so why do we have both of these Loops in VBA well the while when Loop was the original Loop but it was eventually replaced by the more flexible do while loop and the while when Loop is still included in VBA for backwards compatibility but there is no need to use it we can use the do while loop instead if we ignore the while when Loop we can say that we have three Loops in VBA the standard far Loop the for each Loop and the do Loop the difference between the far loops and the do Loop is that with the far Loops we know the number of times we will run the loop in advance so this could be the number of items in a collection an array a range and so on with the do Loop we don't know how many times it will run we're essentially saying run while a given condition is true for example imagine we were to ask the user to keep entering fruit names and to type the word end when they were finished so this Loop can run once or it could run a thousand times we have no way of knowing in advance we are simply saying perform an action until a given condition changes now let's look at some practical examples of where we would use the do Loop a quick pause to tell you about the Excel VBA handbook course are you struggling to build VBA applications do you find it difficult to get good information on how to create real world VBA code is it a struggle every time you try to create a VBA application no matter how simple It is Well the Excel VBA handbook course teaches how to build real world Excel VBA applications from scratch unlike most courses you won't be overwhelmed with information and left to figure out how to put it all together instead you'll be taken stepbystep to 10 Excel VBA applications with every concept explained once you start working to VBA applications you'll be amazed how quickly your VBA skills increase so check out the VBA handbook course at the xlvba handbook. comom and the link can also be found in the description below the video one common use of Doh Loops is when we are reading through a text file line by line we don't know how many lines the file has until we reach the end of the file therefore the Doh Loop is very suitable for this task ask another example is reading two files in a directory using the deer function we keep repeating the loop until deer returns an empty string and this means there's no files left the do Loop can be written in four ways let's go back to our fruit example so we can see what this means in Practical terms the loop keeps going until the user types end but imagine we want to keep going until the user enters a blank string we change the code to do this but now when we step through the code you can see that it doesn't enter the loop and this is because the fruit variable is initially an empty string if we only had the while wind Loop we would have to set the fruit variable to some default value and we can see that this isn't convenient but with a do Loop we can simply move the while condition to the end of the loop and now when we run the code you can see that we entered a loop so the main difference between do while and Loop while is simply the do Loop will run at least once the do while loop may not run at all now apart from this after the first time both loops work in exactly the same way when using the do Loop we can replace the while with the until keyword and the only difference between while and until is that the condition is reversed so for example instead of saying do while fruit does not equal an empty string we can say do until fruit does equal an empty string similarly when we say do while not end a file we can say do until end a file you can ignore until unless you really need to have the condition reversed you may be overwhelmed with all this information so I'm going to simplify it right now use the for each Loop when dealing with any type of collection including ranges workbooks worksheets and so on use the standard for Loop if you were using an array the for Loop is also useful if you want to read a collection in a different order for example if you wanted to read the list of worksheets in reverse the for Loop is also important if you want to access the position of a particular item use the Doh Loop when you don't know in advance when the loop will end this could be reading through a file reading through a folder using D or accepting user input move the while condition from the do line to the loop line if you want to make sure the loop runs at least once in most cases there's no real need to use the onal keyword and avoid using while wind as it's obsolete if you'd like to take a more in-depth look at the far Loop then check out this video on the screen

Channel: Excel Macro Mastery

Views: 20,689

Keywords:

Id: kuerxWa_fkc

Length: 8min 37sec (517 seconds)

Published: Tue Oct 24 2023