All right. Moving right along, we are now going to talk about repetition structures in VBA. There are two general types of repetition structures in VBA. There's the general do loop and there is a count-controlled for next looped. The general do loop is when you're looping not a predefined fixed number of iterations, you are iterating until some condition is met or while a condition is satisfied. The example I like to use is, if I'm thinking of a number between one and 10 and I ask you to pick a number and you choose four and I say no, then you guess again, and it's eight, and I say no, then you guess again, and you keep going. The number that I have in my head is seven. So, you keep going until you guess my number. You could guess the number on your first try or the 100th try. So, you're doing a loop until something is satisfied or while a condition is satisfied. A count-controlled loop on the other hand, is when you're doing something exactly a certain number of times. So, you iterate loop for a predefined or fixed number of iterations. So, you do something exactly 10 times or exactly 100 times. The most basic do loop in VBA is known as the general do loop. This is also known as a mid-test loop because the test is performed in the middle of the loop. You have some pre-test code and some post-test code but in between the pretest code and the post-test code, you have a condition. If that condition is true, then you exit the do loop. If that condition is false, you just keep going. I want to point this out it is pretty important, a lot of students mess this up. You don't have to have an "else" statement on here because if you don't exit the do, you just sort of move along and you would just keep going and you do the post-test code block. So, you don't need an "else" statement in here. You see that we have the one way, "if then" on a one liner, so if condition, then exit the do loop. Let's go through an example here. This is just a general do loop. I've created a sub, I've defined x as 20, then we enter into the do loop. The flowchart element for the do loop is just this circle at the beginning. So, we enter into the do loop, we have pretest code, x equals the square root of x, then we have the opportunity to exit the loop. We have if x less than 1.1, if that's true then we exit do. If you don't exit the do, we don't have an "else" statement. We just move along and x is equal to x to the 1.5, and then we enter it back into the loop. So, this loop statement here represents this arrow that's returning back up to the initial loop. Then finally, we might do something like message box x. Let's just step through this, x is at 20, x is now going to be the square root of the previous x. So, we just take the square root of 20. Is that less than 1.1? No. So, we say now x is x to the 1.5. We loop back and we keep going. So, we're going to keep going and we're doing this, there reaches a point when x becomes less than 1.1 right now. So, x is less than 1.1, then we exit the do and we might message box the answer. Now, there's two special cases of the general do loop. There's something known as the do-while, which is called a pre-test loop. You notice it's called pre-test because the test is before the code. Notice that there is no pre-test code. If you have a situation where you need pre-test code, you cannot use the do-while. The loop is continued on true. So, that's a little bit different from the general do loop. The general do loop you exit do when the condition is true. The syntax for this looks like do-while and conditions. So that's, you've got your condition built into the first line of this three line code block. Here is an example of a Do While. We're defining ilim as 25, that's just a limit, I currently set to four coming into this, and then we have the do. You notice there is no pre-test code block, so we can use the Do While. While i is less than ilim, so we're staying in the loop while that condition is true. While i is less than ilim, we set i equals to i plus one and then we put the loop command at the end. Ilim is equal to 25, i is equal to four, Do While, so this first one, four is less than 25, so we stay inside the loop. We loop while that condition is true then we bump i up to one and we keep going. We reach a point where i equals ilim and we exit the loop and we message box i. I want to show you what happens when we set i equal to 35 going into this loop and let me show you what happens. So, ilim is set to 25, i is 35. Because 35 is not less than ilim, we just skip right over and we don't even go through the loop once. The second more specific special case of the general do loop is the loop until. This is known as a post test loop because we do the test after the pretest code. You notice here that there is no post-test code. There's no opportunity to put code after doing the test. You notice that we exit on true just like the general do loop. The syntax for this is in VBA as shown here. We have the do with the loop code. That's our pretest code and we loop until a condition is true. An example of the loop until is shown here. We set ilim equal to 25, we then enter into the do which is denoted by the do statement. The pretest code is i equals i plus one and we loop until this is true, i is greater than ilim. When that is true, we exit and we message box i. So, let's run through this, ilim is equal to 25. If you don't define the value of a variable, it's automatically zero. I did not set i equal to anything here, so i is zero. Then we're going to loop. We're just adding one. There reaches a point where I approaches the limit, so it's 25 but we do one more because we have to loop until i is greater than ilim. So now, i is equal to 26 and we exit and we message box i. So, those are the three types of do loops in VBA. I wanted to just real quickly explain one of the hazards of setting up a general do loop. The other two loops are easier because they're designed to prevent you from getting trapped in an infinite loop. If you set this up wrong and you make it so that there's no way to exit the do loop, it'll just get caught in an infinite loop. Alright, thanks for watching. I hope you enjoyed this screen cast.
