In this screencast I am going to go over an example of Do Loops in VBA.
I've got a flow chart already made, and what we're doing is we're starting with a value
for i of 1, we're going into this Do Loop, and then each time we're in this loop, or
each time this loop progresses we are multiplying the previous value of i by 1.1, so we're increasing
it by 10%, and then we have inside of each loop we have a conditional, is i greater than
20?
If it is greater than 20 we;re going to exit.
So we're basically taking 1, multiplying it by 10% over and over and over again until
it exceeds 20.
So while this is not true we just repeat and we keep going and going, and then after, when
i does exceed 20, we exit this loop and we display a message box for what i is, and then
we end.
So we're going to create VBA code for this, and we're also going to implement in here,
we are going to count how many times this loop progresses, and actually if we want to
in here we would put another flow chart element, another box where we're keeping track of the
number of times we've iterated, so we're going to add in a count=count+1, so every time we
go past this point we're going to increase that count by 1, and that will enable us to
determine how many iterations of the loop have occurred.
Just a few things here, there are three types of Do Loops in VBA.
There's just the generic Do Loop, so generic Do Loop has this structure, where we have
Do and then Loop.
You never want to set up a Do Loop, a general Do Loop unless you have some way to exit,
and I forgot to put it here, but you need some sort of condition that can be true, if
condition then we exit do.
We exit when something is true.
The other way that we can do this is a loop until, so we're going to do something until
a condition is satisfied, so either way we're exiting when something is true, and we're
going to implement these now in VBA.
So now I am over in Excel, I do Alt+F11 to bring up the editor, I am going to insert
a new module, and we are going to call this "Sub Looper".
First thing we need to do is dim our variables.
Going back to our flow chart we see that the variables that we need to dim are going to
be i, and count, so that's it, i and count.
i is going to be a double because it can take on any real value, and count is going to be
an integer, that's only 1, 2, 3, 4, and so on, because that's the number of times that the
loop has proceeded.
The next step we need to declare that i is going to be equal to 1, and then we enter
this Do Loop, and inside the Do Loop we say the beginning i is equal to 1.1 times the
previous i, and then we're going to have an if then, this is a one way if then.
We exit the loop, and if we don't exit the loop then we just increment the count by 1.
So I put in i=1, I like to set up my Do Loop with some space in there so I know that I
end my Do with a Loop. i is going to be 1.1 times the old i, and now we have an if in
there, so this if now is corresponding to this diamond.
So if i is greater than 20 then we exit do.
This is known as a one way, one liner, you don't need an "end if", and then the next,
if we don't exit the loop, then we increment count by 1.
So count=count+1.
And that should be it for our loop, so we loop back after we're through, and after we
exit the do, then we need to display a message box, and I've just said "MsgBox ("After" & count
& "iterations, i has a value of " & i)".
So this is just going to say, for example, after 20 iterations i has a value of whatever.
I think we're all ready to go, I am going to press F8 now to step through this, we can
look down here in the locals window, i is equal to 1, and now we're going to do, so
now i is equal to 1.1.
If i greater than 20 then we exit do, so at this point it's not greater than 20, count
starts out at 0, but then when I proceed through that we increment count by 1, and we keep
going, and what's happening, if you look down here, is that i is increasing by 10% each
time, and we keep going and going and going, and after a bunch of iterations, exponential
growth, 19.19, I go back up here, now we've incremented that to less than 20 to over than
20, so now this should be true we exit the do, and we have a message box: "After 31 iterations
i has a value of 21.11377," and so on.
Let me just show you one more thing, I am going to put a format number here, we're going
to format i to two places after the decimal, so I am going to run this now, and you see
that that 21.11 is now rounded now to two decimal places.
So that's how we can use a Do Loop, we can also, I am going to remove this, I am going
to show you how we can do this with a Loop Until, so Loop Until i greater than 20, same
thing run this and it does the exact same thing.
After 32 iterations, i has a value of 21.1.
So now I want to just very quickly show you, let's say instead of having false on the left
here, as we did before, and true on the right, we switch it so now we have true in the inside.
So now we have i less than or equal to 20, if that's true then we stay in the loop, so
this looks a little bit different, it is called a "Do While," so Do While means you're in
the loop when something is true.
So let's implement this in our code instead of "Loop Until".
So I am going to put it up here, "Do While i <= 20" and we're doing the same thing.
So when I run through this using F8 i is less than 20, i is increasing as it did before,
keep going and going and going, and all these cases we're in the loop when something is
true, so right now i is 15.8, which is less than 20, so that's a true statement, and I
keep going, and now this is not true, because i is 21.1, so we exit the loop and we have
a message box, so these are all equivalent ways of doing things, we have the do, the
general do loop, we have the do and loop until, and then we have the do while.
You get the same thing, the only difference is are you in the loop when something is true,
or when something is false.