In this screencast we are going to continue talking about program flow elements in Visual
Basic for Applications (VBA). In this screencast we are going to be going over repetition.
Repetition is basically doing things over and over until something is met or iterating
through something and looking through arrays for certain values. A repetition structure
in VBA include the general "Do...Loop". That is doing something and looping through until
something is met or while something is satisfied. Then we have a count-controlled loop and that
is basically you have a fixed number of iterations and you keep going for a fixed number of iterations.
The general "Do...Loop" flow diagram is shown here. We come into some code here before you
make a decision and then we have some block of code after. The general structure of this
in VBA looks like Do and then you have the pre-test code block that allows you to do
something here such as user input or a calculation and then we have if condition (which is this
diamond in the middle) is true then we exit and if it is false we loop back to the beginning.
You can set up this loop and the "Do...Loop" will keep going until this condition is met.
An example of this is let's say we define "x" is going to be the square root of "x"
so we are defining the new "x" to be the square root of the old "x". If "x" is less than 1.1
we exit the Do and if not we redefine "x" as old "x" to the power of 1.5. We keep looping
until "x" becomes less than 1.1. When "x" becomes less than 1.1 then we exit this loop
and we can move on. This is often times used for input validation. You might ask the user
for a certain value. You get input so this is our pre-test block and then we might ask
"is the input valid". If we are expecting a positive number then if it is negative that
means the input is not valid and we go over here and say for it to display a correction
message. Then we go back to the beginning of the loop and get input and you keep going
through this loop until the input is valid. For example it might look something like this
where we have input box. You ask the user for a pressure. If pressure is positive then
you exit Do otherwise if it is false then we have this correction message saying "Pressure
must be positive" and then you keep looping. Let's say the user puts -20. That is going
to give the correction message and then it brings up that box again. Then it keeps going
until the pressure is positive. Some other special cases of the "Do...Loop" is we have
the Do-While. This is also known as a pre-test loop. We have the loop and the test is before
the loop code. That is known as a pre-test. When we say Do While this means
while this condition is met we are going to go through code. You can put in loop code
and keep looping while it is true. An example is let's we define "ilim" as 25. We set "i"
equal to 4 and we Do While "i" is less than the limit and each loop we add one to "i".
We start with "i" equal to 4 and we say "i" is equal to 5 then 6 and so on up to 25. When
"i" is 25 then "i" is no longer less than "ilim" so in that case this would be false
and we go out of that loop. You will notice here there is no pre-test code and the loop
is continued on true and exited on false. This is different from some other types of
loops. What would happen if we set up this look where we go into the loop with "i" equals
to 35? It won't even run once because "i" will come into this loop as being not less
than "ilim" so we won't even run through this loop once. There is also a Do-Until and this
is also known as a post-test loop where we have the test that is after code. We set it
up as Do and then loop code followed by loop until a condition is met. This continued on
true and the Do While is continued on false. We set up "ilim" as 25 and we set up this
Do Loop of "i" is equal to "i" plus 1. We loop until "i: is greater than the limit.
If "i" came in as 1 we keep adding 1 to it until "i" is equal to 26 it will then exit
out of this loop. This loop always executes at least once. You will notice here that there
is no post-test code but if we wanted to you could actually include some post-test code
and that code would go some where over here. The final thing is count controlled iteration.
Let's say you want to do something a certain number of times for example ten times. The
flow diagram looks like this . We have this box with a start value and then an index plus
increment. Then this is a built in logical which is when the index passes the limit then
we exit out of this loop. If the index does not pass the limit then we keep going. We
do something and we loop through this code. The general structure looks like "For index
= start to limit Step increment". We do loop code and then we do next index. Just so you
know if you leave out this step increment it just assumes a step value of 1 which is
quite common but sometimes you might want to count downwards and use a negative one
here. An example is you start with sum = 0. We say "For i = 1 to n". A side note is you
would have to declare that "i" has to be an integer and "n" you have to declare as an
integer and then set the value of "n" to something. Let's say we wanted "n" to be 10 we would
be going from "i" equals to 1 up to 10. We would be saying the new sum is equal to the
old sum plus x(i) with x(i) just being an array with "n" elements. Let's say we have
a vector of 10 elements each time we go through this "For...Next" loop we are adding the next
element to the sum of the previous elements.