VBA: Coding Do Loops

VBA: Coding Do Loops


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

Leave a Reply

Your email address will not be published. Required fields are marked *