This screencast is going to go over an example of input validation, specifically using a
Do Loop in VBA.
So we want to create a sub in VBA that will use a Do Loop to ensure that the user inputs
a positive number y using an input box, and then the sub is going to display y^2 in a
message box.
The first thing we want to do is draw a flowchart and then we're going to implement that flowchart
directly into VBA code.
Every flowchart begins with start.
What I like to do is I like to have a list of the variables that we're going to dim,
and if we're working with input validation one of the first things we need to do is to set
up a loop.
So that's just a small circle, and we know that something is going to come back around
to intersect with that circle.
So the first thing we need to do is obtain input from the user.
So the user is going to input y, we know that we're going to have to dim y, so I add that to my
list of things that we need to dim.
So the user is going to input y.
The next step is to make sure that y is positive, so is y positive?
So that is a decision, a logical, which is a diamond.
If that's true we're going to exit the loop.If it's false we might have a correction message.
So this is a message box that's just saying that y must be positive, and then we go back
here, we keep going, so now we're going to input y again, and we loop until y is positive
and then the last step is just to message box y^2.
So that's an output, and then we end.
There are a couple of ways to do these loops in VBA, the most popular is Do Loop.
In this , whenever you set up a Do Loop there has to be some sort of condition that will
become true so you can exit the do.
If there is not this line then you will be in an infinite loop forever, so we would have
the input here , I am just going to put kind of generic input y up here, down here we would
have a message box.
So we obtain the input from the user.
If y is greater than zero, so we're going to put the greater than zero, and then we're
going to exit do, so that's how you can get out of here, and then if this condition is
not satisfied then we're going to display the message box that says you're value for
y must be positive, and then we go back, ask the user for another y, and we keep going
until y is greater than zero.
Another way we can set this up as written in the flow chart is we do and we loop until
y is greater than zero, so here we would have our input y, and we would have our message
box, and we're going to keep doing this until y is greater than zero.
The third type is a do while.
We would have to rearrange our flow diagram such that instead of being y greater than
zero we would have to change this to y less than or equal to zero because a Do While you
always stay in the loop while a condition is true.
So if we switch this conditional to be y less than or equal to zero that would make this
left decision true, and the right decision false.
We'd say Do While input y and message box.
We're going to implement this top one here, just a generic Do Loop.
So I am going over to excel, Alt+F11 to insert a new module.
I am going to call this "Sub Validate()".
The first step is to dim our variables, and the only thing we have to dim is y, so I am
going to dim y as double.
The next step is to enter that loop, so I am going to have a Do, and I just like to
put in the end so I don't forget it.
We're going to do y is equal to "InputBox("Please enter a positive number")".
So whatever they put in to that it's going to input into y, and the next step is to do
our condition "If y>0 then exit do" this is know as a one way one liner, so it's a one
way if then, you don't need to put the end, as long as it's placed on the same line.
Alternatively we could write it as "Exit do" but then we'd have to "End if" so that's another
way to write it, but it's really easy if we just put into one line, you can do that for
one way If Thens.
If y is greater than 0 then exit do, if we don't exit then we need a message box that
says "Please enter a positive number" and then we keep going and once we get out of
the loop, so this part was the loop, this whole thing is the loop, once we get out of
that message box y^2.
So I am just going to do message box "The square of your number is " and y^2., and what
I am going to do is format this, I like to put in "formatnumber(y^2,2)" so it'll be two
places after the decmal, and that should be it.
So the square of your number is_ and then it's going to format the y^2 to two decimal
places, and output it in a message box.
So let's step through this, I am going to do F8.
Positive number ,I am going to put in -3 just to show what happens, if -3 or y is greater
than 0, so that's false, I am not going to exit do so it should just go to the next line,
please enter a positive number, and then it loops back up to the first line, "Please enter
a positive number."
So again if I put in -7 it's going to keep going, "Please enter a positive number."
And finally when I put in a positive number, since 7 is greater than 0 we exit do, and
then the last line is "The square of your number is, and we have 49, and if we're just
entering an integer that format number it kind of makes things more complex because
we're adding those two zeros, but in general if I run this and I have something like 4.5
then it formats it to two decimal places, so if you're using really complicated numbers
like 3.16725 it's not going to have, like, 10 places after the decimal.
So that is it for input validation.