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