In this screencast, I'm going to explain the difference between the two procedures
that are inherent in Visual Basic, Subroutines and Functions.
Subs can be executed by running it from Excel.
So, you can do that straight from Excel in the Developer tab,
or you can associate a button with the different subs.
You can also run it from the Visual Basic Editor,
or it can be called by another VBA subprogram.
Subs can be created with the macro recorder or you can type it in from scratch.
Subs can have zero arguments.
Ninety nine percent of stubs do have
zero arguments that provide for values to be input or output from the sub.
The syntax for subs in VBA starts with just sub and you have your name,
and you have your optional arguments.
Again, 99 percent of the time are optional arguments.
This is just going to be empty left and right parentheses.
Then we end the sub with End Sub.
So, let's go through an example.
We're going to just create a simple VBA sub that's going to ask the user for a number,
and then it's going to square the number in a message box.
So, I'm over here in Excel now and to bring up the editor,
you got to make sure you have the Developer tab.
So, we click on the Developer tab and you go Visual Basic,
or alternatively you can do Alt F11.
I've already been working with some things here
that I'm going to go over later in this screencast.
But for now, let's go ahead and insert a module.
You always want to be putting your code in a module,
and this is where we're going to put the code.
So, I'm just going to name it Sub SquareNumber.
You notice when I press enter,
it automatically puts the empty brackets there because all subs need that.
We're going to do something called dimming,
which you'll learn more about in subsequent screencast.
But dimming is essentially declaring that there are variables in our program.
So, I've dimmed two variables.
I've dimmed x as a double.
Y is also a double.
So, it's double-precision.
Double essentially means that it's a real number.
The next line I've typed in x equals Input Box, Enter a number.
So, the user can provide the number x in something called an input box.
Next, we're going to calculate a new variable y,
which is x squared.
Finally, we're going to output the square in a message box.
So, we've got an input box to obtain information,
we've got a message box to output information.
Now very important, you never want to use input boxes and message boxes in functions,
they're only reserved for subs.
So, we're going to square the number.
We're going to output that.
You noticed the formatting here.
So, I have the square of your number is with a space here.
This is a string in quotations.
You can concatenate strings with numbers using the ampersand.
So, let's go ahead and run this.
I'm going to press F8 to step through this.
You can press F8 to run line by line.
Alternatively, I could just run it all using this Run button or F5.
But, I like to have the locals window up.
If you don't have the locals window,
you can go to View local's window.
This shows you exactly what's going on inside of your program.
So, I'm going to press F8, it brings up Enter a number.
So, I can put in five, I press okay.
And now you see that it's taken in that five.
We now run the next line using F8.
We squared that to calculate a new variable, y which is 25.
Then finally, we output that in a message box,
and then we can end the sub.
So, this is an example of just a very basic sub.
So, functions are the second type of procedure in VBA.
It's a VBA subprogram that can be executed by using
its name in a VBA expression or in an Excel formula.
So, in a cell you could type in equals
with your user-defined function name with your arguments.
So, functions return values in place of their names.
Functions cannot be recorded,
but must be typed into the editor.
Functions can have zero to several input arguments.
In contrast to subs,
99 percent of functions do have arguments,
and it's very rare to have functions without arguments.
But some of you might be familiar with the Pi function,
which doesn't have any arguments,
or the Now function in Excel which also does not have any arguments.
Syntax looks like function instead of sub,
and we have End function,
and you have the name of your function,
and your optional arguments.
Let's go through an example. Let's create a function called
savings that will calculate the total savings given the principle,
interest rate, and time in years.
Well, what we want to be able to do in Excel is to type in a cell equals savings,
and we have the three arguments.
We have the principal, the interest,
and the number of years and it ends,
it gives us the amount,
the savings at the end of n years.
So, I've typed this into the editor.
We have Function Savings,
and the way that you dim or declare variables,
you don't have Dim statements inside functions.
Instead, the arguments, and you don't use the Dim statement.
You say P As Double, I As Double.
So, those are real numbers,
P and I. N is only going to be integers,
so you can dim it as an integer,
takes up a little less space.
Then the output savings,
the output that's going to be provided by the function is a double,
so we put it at the end.
You put the equation here.
So, the output of a function is always the name of a function,
and that's equal to the equation,
P times one plus i to the n. And I can put this into a cell on the spreadsheet,
just like you would another function,
a built-in function to excel,
and it ends up, we get our result.
So, just a little bit of a debrief here on functions, user-defined functions.
All functions begin with function.
The function above in Excel you call it in Excel by typing in the function.
P, i, and n are internal space holder variables.
When I write this in the editor,
they're only used in the VBA code.
I could have just as easily written A,
B and C as long as I replaced that.
Then I have A, B and C here.
They're just used internally in that function.
The value assigned to savings is output in
the spreadsheet cell when the function is called.
Functions don't have to be simple.
They can be super complex with loops,
if then statements and so on.
Every open Macro Enabled Workbook can use functions in
any other Mac Open Macro Enabled Workbook.
Real quick, I wanted to show you an example of a function with no arguments.
This would be similar to Pi.
The golden ratio is something is equal to
the square root of five minus one divided by two, is a number.
I could define a function GR,
which will just give us the number.
So, 0.618.
I've also got a more complex example here of a sub that has arguments.
So, this is a sub that has arguments.
What this is going to do is it's going to delete rows on a spreadsheet.
So, I could say I want to delete rows four through seven,
the sub needs to know where to start.
So, it's going to start at the fourth row,
it's going to finish at the seventh row.
We have a sub here,
RunDeleteRows that's going to call the Deleterows sub up
here starting with row three and let's go through seven.
So, if I run this sub using F8,
you see that it jumps into the DeleteRows.
We've passed arguments three and seven into the sub on the spreadsheet.
We're selecting rows three to seven,
and then we can delete those rows.
So, that's an example of a sub that might have arguments.
All right. I hope you enjoyed this screencast and thank you for watching.
