In this screencast we're going to go through a couple of examples of iterating through
vectors and arrays using For-Next Loops and this is going to be done in VBA.
The first example we're going to use a count controlled iteration, so that's a For-Next
Loop to develop a VBA sub that's going to tally the number of 5's in a vector.
So for example we have a vector here, it should spit out maybe a message box saying "There's
1 five in your vector."
The first step is to draw a flowchart, so we start, I like to add a list here of things
that we're going to need to dim.
We're going to have an index i.
We are going to have a count, so those are going to be both integers.
The next step is to count the number of rows in our selection.
So we're going to have another variable, nRows, that's also going to be an integer.
So we count the number of rows, and then we enter into the For-Next Loop.
If i exceeds the number of rows then we are done, so when that's true.
If not then we go into a fixed iteration and we're going to do something in this For-Next
Loop.
So what are we trying to do?
We're trying to see if when we iterate through the cells of this vector if the value of a
certain cell is equal to 5 then we're going to increment this count, so count is kind
of a running tally of all the fives.
So if the cell value is equal to 5 then we increment count by 1, otherwise we don't do
anything, we just move along.
So back to our flowchart, this will take the form of an If-Then, a one way If-Then.
So we ask ourselves is cell i equal to 5?
If that's true then count equals count+1, we just increment the count by 1.
If it's false we don't do anything, we just move a long to the next row.
So now we increment that back up, we go through this when i, the index, exceeds the number
of rows we are done, and the last thing we need to do is display count, so we're going
to display to the user what the number of fives in our vector is, and then we end.
So let's code this in VBA, Alt+F11 to bring up the editor, insert module.
So i am going to call this "Sub CountFives()".
The things that we need to dim are i, n rows, and count, and these are all integers.
The first thing we need to do, going back to our flowchart, we need to count the number
of rows.
So "nRows=Selection.Rows.count".
Next, we go into this For-Next Loop, we're iterating over all rows, so for i=1,2,nRows,and
what we're doing inside of that iteration is we're checking to see if the current cell
value is equal to 5.
So if, and I am going to do "Selection.Cells(i,1)" so that means of our selection cells, for
example 1,1, will correspond to first row first column of our selection, so that's going
to be our first cell.
If that value equals 5 then count=count+1.
I don't need an end if because this is just a one liner, one way if then, so I can just
say that, otherwise we're not doing anything so I can just leave it as that.
Next i, so that completes this loop.
So now I am back up here and when we're done we just need to display the count.
So I've done something like "MsgBox "There are " & count & " fives in your selection""
So this should say there are one, you know, for example one fives in your selection
or maybe I want to put a parenthesis around this in case it's one, and we are ready to
go, so I need to make sure that I have a selection highlighted, that's our selection, I am going
to step through this in the editor using F8, so the nRows we picked up as 8, there's 8
rows I keep going.
If the selection is equal to 5 then count=count+1, and as we iterate through this you can see
that i is increasing, but count is not increasing because only the second to the last value
had a 5 in it in our selection, so right now i is equal to 7, that's our seventh row.
We found a 5 so count is incremented by 1, and then we keep going, count is 1 so it displays
"There are 1 5 in your selection", and if I go back and put in another 5, for example
I can run this Alt+F8 count fives, and there are two fives in your selection, so this is
how we can iterate through a vector.
So now the goal is to develop a VBA function that's going to tally the number of fives,
so we're just going to take our sub that we just created and we're going to convert it
into a function.
A couple of things you need to keep in mind when changing a sub to a function, you need
to change sub to function, and that'll automatically change n sub to n function, I'll show you
this in a moment.
You need to add an argument to the function, and I'll show you that, and then you never ever
want to have input boxes and output boxes or message boxes in functions.
So let's go over to our code, I am going to change this from sub to function, now we have
an argument, and this is going to be my range, so I am just going to put in anything because
that can be anything, it's just a dummy argument, that's what they're inputting into the calc
fives function in the spreadsheet.
Instead of nRows being selection, our object now is the dummy argument, that's the range
they're putting in, so that is now our argument.
So we are dimming same thing, i and nRows, count, and again, instead of our selection
being the object, our range, which I am just using the dummy argument here, anything,
if that is equal to five then we increment count by one, and then we never ever want
to have a message box.
Instead, the output of a function is always the name of the function, so I am going to
say "CountFives=count", so that's the output of our function.
So let's going into our spreadsheet, I am going to type this in "=CountFives" it recognizes
that, so I put in my range, and when I press enter it's going to go through exactly what
we did in the sub, but then the output is just into a single cell, and again you wouldn't
want to have a message box here because functions are usually just a single cell with an output
in that cell.
Moving a long we're going to develop a VBA sub that's going to count the number of fives
in an array.
So now, instead of just having a column vector, we're going to count the number of fives in
an array.
The difference here now is that we're doing a double iteration, so we're not only iterating
through the rows of a single column, but also we have multiple columns.
So our flowchart is going to look a little different, we have to include now another
iteration, so now we're not only iterating over the rows, but we're also iterating over
the columns.
So I am going to go back to my original sub for doing a vector, and we're going to modify
it so now we can do that over an array.
I've changed the message box a little, what we need to do now is instead of having just
i we now have another integer, j, that we're iterating over all rows.
We also have nColumns as an integer.
We also have to count nCols, using "Selection.Columns.Count" and instead of just doing a single iteration
we're now iterating over the columns.
During each of those we're doing the same thing, where we're checking to see if that
particular cell is equal to 5.
If it is we increment the count by 1, we have to do the next j to complete the j loop, and
we do next i, and this should be all set.
So I am going to go over and I am going to move these up here, going to highlight this, we
remove the function that we had from earlier, this is now going to be our selection, and I am
going to press F8 to start going through this, it's picked up the number of rows, the number
of columns, and I am just iterating through.
When we come to a 5 then count is incremented by 1, so now we have count is equal to 1, and
we keep going, we just picked up another one.
We go through all the different combinations of row and column, and then we spit out "There
are 2 fives in your array.
And, if you wanted to we could change this to a function, just change that to function,
this can be anything, instead of selection we have anything, anything, anything, and
instead of the message box we're going to say "CountFives()" that's the name of the
function, is equal to count.
And let's make that this runs, equals count fives of our array, and in fact it does work,
we have two fives in our array.
So that's how you can work with vectors and arrays using For-Next fixed iterations in
VBA.