Okay. I'm pretty excited,

now we're done with some of the tedious stuff for this module,

getting through all that objects stuff,

and properties, and methods.

So now, we get to move into some examples,

a lot of what I'm going to be covering in this screencast,

we're just going to be working through examples.

I've got examples in this screencast and then also in a subsequent one.

So, to start us off,

what I'm talking about here,

really only pertains to subroutines,

we're not going to be using the same type of stuff in functions, user-defined functions.

User-defined functions never use input boxes or message boxes,

and they don't use active cells nor selection,

instead they use range objects as the arguments to

the function or just simple numbers as arguments.

So, in subroutines, there's

a couple different ways that you can normally get information,

so this is importing information

into the VBA subroutines such that you can do calculations.

We can do this through the use of an input box,

the active cell, so this is chosen before the sub is run,

whatever the active cell is,

we can use that value.

We can use a selection or you can have

a fixed range as far as outputting things back to Excel,

what do you do with the result.

We can output things in a message box

into an active cell which is chosen before the sub is run,

and we can also output stuff to a selection,

and we can output to a fixed range.

So, I've got an example here.

I've got two examples we are going to work through,

how would you make a VBA sub that would take a value from an input box and A,

display the square root in a message box, B,

display the square root in the active cell,

and C, display the square root in cell C3.

So, I'm real big on flowcharts,

and I'm going to be drawing a lot of flow charts in this course.

This flowchart is going to be very simple,

but I wanted to just introduce you guys to flowcharts,

and I'll explain as we go along,

flowcharts are going to get more and more complicated,

but whenever I'm making any program,

I make a flowchart to begin with.

Flowcharts always start with an oval, that says start.

So, that's where we begin.

We then draw arrows into the different operations.

So, the first thing we're going to do here is we're going to

get a number that I'm going to define as x.

So, we get x.

Whenever we're interfacing with the user,

we write this as this parallelogram.

So, we get the x.

This can either be an input box or some other way.

I'd also like to keep track of the things that we need to Dim.

So, we're going to have to Dim x,

and that's going to be likely as a double.

Next, we're going to calculate y,

actually it's not this square,

is the square root.

The square root of x, we also need to Dim y then we output y,

that's we're interfacing with the user,

so that's typically a parallelogram,

and then we end.

So, this is a basic flowchart for this process.

So, let's go ahead and write the code for this.

Though in all three cases,

we're going to take a value from an input box that's going to be x,

we're going to calculate the square root of x to calculate y,

and then we're either going to display it in a message box,

the active cell, or in cell C3.

So, I've named this cell square root.

I write my Dim statements for x and y both as doubles.

We obtain x in an input box,

we calculate the square root of x and assign that to the variable y,

and finally we output the result y in a message box,

"The square root of your number is y."

So, let's run through this using F8,

we obtain a number in the input box.

I'll put in seven,

we then take the square root.

You can look down here in the locals window to make sure

that things are being calculated as expected which they are,

and then we message box that,

we output that in a message box.

Now, you notice all the sig figs we add is a double,

so we had 15 or 16 significant figures.

We can change that by using the format number function.

Now, instead of just outputting y,

I put FormatNumber y,

two which will truncate it to the hundredths place.

When we run it, I put in seven and it truncates it to the hundredths place.

The second part B, is display the square root in the active cell.

The only change we have to make here is instead of outputting in a message box,

we output to the active cell which here is B2.

So, everything else is the same except we have active cell equal FormatNumber y, two.

So, I can put my active cell wherever,

and I run this using F5,

enter a number eight,

and it outputs it to the active cell.

Finally, if we want to display the square root in cell C3,

regardless of where the active cell is when we start this subroutine,

it'll always be placed in cell C3,

we can just make a few modifications.

We'll replace active cell with just range C3.

So, regardless of where the active cell is when we begin this sub,

it'll place the answer into cell C3.

Okay. So, we just completed example one.

Now, example two is similar,

"How would you make a VBA sub that would take the value in cell A1?"

So, instead of us obtaining x from an input box,

we're just going to obtain it from the value in cell A1,

and we're going to do the same thing.

We're going to display the square root in the message box,

active cell or C3.

So, instead of x being acquired in an input box,

it's just going to be equal to the range A1.

So, now when I run this, it's going to take seven.

Regardless of where my active cell is,

it's going to always take range A1.

So, I run this and it outputs in a message box,

or for part B into the active cell.

So, I run that, my active cell is C5.

So, let's run that.

Okay. Finally, in cell C3,

which we ranged C3.

Regardless of where we start with the active cell,

it will place it always in cell C3.

So, we run that and we place it there. All right.

The next screencast is going to go through another couple of examples.