We're going to delve deeper into the second type of repetition structure commonly found in programs, and that is the count-controlled loop and in VBA, that's For...Next. The flowchart for a count-controlled For...Next loop is shown here, and you notice here that we have this counting index, i=1. So we come into this with the start value, in this case i = 1, and while i is not greater than a limit value, for example 10, then we do the loop code and we increase i by 1. And if you notice here, then we're not using i in any of the loop code but we're doing this loop exactly 10 times. At the very end I becomes 10, 10 is not greater than 10, so we go through the loop once more. i becomes 11, and since i exceeds 10 then we will exit the loop. If we want to do something exactly 10 times, we could implement this Loop Until. Now the common flow chart element these days for the For...Next looks like this. We come into this, it's got this rectangle, we have an index = start value, and there is kind of some implicit arrows, you go from this upper-right box into the left box. So we come in the index = start value, typically this is 1 then we go into this left box, does the index pass some limit? For example 10, if you want to do this loop 10 times, if that true then you're done and you just move along. While we are not exceeding that limit, we perform the loop code and we go back and the index then is incremented by 1. This is used frequently with vectors and arrays. So the syntax for this in VBA is we have For index = start To limit Step increment, then you have your loop code, and you always have Next index. If the step increment is left out, a lot of times the step increment is just 1, but if you wanted to step by anything other than 1, you can put that after step. You can also count down, so you could start at 20 and count down by negative 2. An example that would sum the first n integers is shown here. We start out with a summing variable equal to 0, For i = 1 to n, the new sum is equal to the old sum plus i. So if i is equal to 1, then at the end of the first loop, the sum will be 1, during the second loop, i will be 2. So we're adding 2 to the previous sum, we'll have 3 and then when i is 3, we're going to add that to the previous sum, so we get 6. And we keep going, and that's how you can sum the first n integers. So I just wanted to reiterate that the flowchart element on the right is exactly equivalent to what we typically use these days and what I'm going to use in this course, which is the flow chart element for the For...Next loop shown on the left. Let's go through an example. Create a VBA function, that will count the number of the first n integers that are divisible by either 3 or 5. So let's make a flowchart for this, we start then we get n, n is the argument of the function, so we can set up a For loop. We're going to have to Dim some things, we're going to have to Dim n, that's going to be an integer, i is going to be an integer. If i does not exceed n then we're going to go into a if then statement, is i divisible by 3 or 5? If that's true then I'm going to increment my counting variable, which we also have to Dim by 1, so that's the number of items that are divisible by 3 or 5. If that's false then we don't do anything, we loop back to increment i by 1. And then once we have exceeded that index n, the limit, then we output C and we END. The way that we're going to determine if i is divisible by 3 or 5 is using the mod function. So let's go ahead and put this into VBA code. So I've created my module and my function, well I'm going to call this divisible. The argument is n as an integer and the output is going to, divisible is going to output an integer. We have to dim I, which is the counting index, the iteration index and C which is the count. We put in our For loop, and whenever I write For i equals something, I put in Next i so I don't forget. Inside the For loop, we have a one-way If then, the condition is if i is divisible by 3 or 5, and we can use the mod function for that. So if i Mod 3 = 0 or i Mod 5 = 0 then we're going to add one to our count. Then outside the For loop, the only thing that we have to do once i has exceeded n is to output C. The output of a function is always the name of the function, so divisible equals C. So lets see if this works, I'm going to go ahead and put a break point here on the first line of executable code just so we can see that this is working. In excel, I'm going to type in the function, divisible of 30, so this will count the number of integers between 1 and 30 that are divisible by 3 or 5. I press enter, and now I'm into debug mode, and we can look down here in the locals window to see what's going on. And I'm just going to step through it. So i right now is equal to 1, 1 is not divisible by 3 or 5, so we just bypass that. Neither is 2, but then when we get into 3, 3 is divisible by 3 or 5, so we add 1 to our count, so now our count is equal to 1. Then we can keep going and going. Now that I know this is working I'm just going to put my cursor there, and I'm going to go Run To Cursor. So we went through all the remaining i's and now we can just finish this function, and there are 14 integers between 1 and 30 that are divisible by either 3 or 5. Let's go through another quick example. Let's create a VBA sub that will count the number of 5's in a selection in a column vector and it is going to output the number of 5's. For example, here we have 3 fives, so it's going to let us know that there are 3 fives in our selection. Let's make a flowchart. First thing is we start and we immediately count the number of rows in our selection, then we enter into a For loop, where we're going to iterate through all of the rows, we're then going to ask ourselves if each cell is equal to 5. Now here, when I refer to cell in my flowchart that really is corresponding to Selection.Cells (i,1). If that's true, then we're going to increase our count by 1. If it's false, we're not going to do anything. And then we loop back and increase i by 1. When i exceeds the number of rows, we're done iterating through all the cells in our selection and then we output the number of items that are equal to 5 and we end. And because this is a sub routine we're going to output C in the message box. So let's put this into VBA code, I'm going to call this Sub CountFives. We need to Dim number of rows As Integer, i As Integer, and C As Integer. We're then going to use Selection.Rows.Count to count the number of rows in our selection. Then we have a For Next fixed iteration loop, so that's what shown here. Inside the For Next, we just have a one-way If then, similar to the previous example. And I have written this, because it's a one-way If then, I can write it as a one-liner. If Selection.Cell is (i,1), so row i column 1, our selection is only going to be a single column, if that equals 5, then we're going to increment our count by 1. And finally, outside of the for loop, we have our output, so we're going to output c in the MsgBox. Let's go ahead and see if this one works. I start with my selection and I'm going to press F8. We're going to count the number of rows and we get 11 which makes sense. Now we're going to go iterate from 1 to 11, if Selection.Cells= 5, then we're going to add to our count. The first one right now, i is equal to 1, so we are looking at Selection.Cells(i,1) which is row 1 column 1 of the selection. That's 4, 4 should not equal 5, so we bump up to the next. Now we're looking at row 2, i equals 2. That is a 6 which is not equal to 5. We bump up, now the third row, i is equal to 3. That is equal to 5 so we should count, and in fact that's what we do, we increase our count by 1 and then we keep going. I'm quite certain it's working now so I'm just going to press the continue button here and it just goes through the rest of the code and it says there are 3 fives in your selection. So hopefully with those two examples, you have a better understanding of what For...Next loops are in VBA.
