There are lots of built-in functions in Excel.

For example, I can write in the square root,

there's a square root built-in function,

there's the average function, there's lots of these.

So in this screencast,

I'm going to show you how we can make a user-defined function.

So, a custom function in Excel using VBA.

So, we have a cone here and we want to compute the volume of this cone.

We want to be able to type in something like cone volume and

type in the radius of maybe four and the height of maybe six,

press enter, and it'll compute the volume using this formula.

So, right now there is no built-in function in Excel to do that.

So, let me show you how to do that.

I go ahead and insert a module.

Just like subs, functions are placed in new modules,

and they don't have to be new modules,

they're just placed in modules.

You can have multiple procedures within the same module.

So, I'm going to call this function cone volume.

It's going to only have two arguments.

So, it's going to have R and H. Now,

you don't dim your arguments inside the function as you do in subroutines.

Instead, in the parentheses here,

in the cone volume of function,

this is where you dim things.

So, this is where we would declare the radius as a double.

We could also do the same thing for the height as a double

and then the output of the cone volume function we want that to be a double.

So, we can put double at the end.

So, the output is going to be double and you don't redim.

Some people are tempted to write Dim R are as double inside the function,

but that actually won't work and it'll give you an error.

At the end of this screencast,

I'll show you what happens when you do that.

But if you need to define new variables inside the function,

for example I have pi in my cone volume function,

I can do that here.

So, just like we would in a subroutine.

So, Dim pi as double.

For now, I'm going to define pi is equal to four times the arc tangent of one.

There is no built in function for pi like there is in Excel.

You can borrow the pi function as I'll show in a subsequent screencast.

You can borrow it from Excel.

But for now, four times the arc tangent of one is a good way to get pi.

The output of a function is always the name of the function.

So, whatever you want to be output in the cell that we type the formula in Excel,

that has to be named,

that has to be a variable named cone volume inside of our ConeVol function.

So, I can do ConeVol equals,

now this is where I'm going to put the right hand side of the volume equation.

This is just equal to R pi that we've defined times radius

squared times R height that's input by the user,

divided by three, and there is our cone volume function.

Now, I can go over here and I can type in ConeVol of

let's say radius of four and a height of six and when I press enter,

it'll calculate the volume using this formula.

So, this works just like other functions in Excel.

I could do a case study using it,

I can drag it just like other formulas.

I can name this cell Rad for radius.

Then I can go up here and I can type in

my cone volume function with the radius that I've defined down here.

I want to keep a constant radius of four,

but I want to do a case study on the height changing from 0.5 to 0.4.

The second argument I can just use as my height there

and I can press Enter and then I can drag this down,

just like you would other formulas in Excel.

So, let me show you what happens if we dim R inside of the function.

Some people think that they to redim the arguments as I alluded to earlier,

dim R as double.

But when we try to do that then,

let me go back over here and press Enter,

it gives us an error and it says,

"Duplicate declaration and current scope."

So, you can't redim the arguments that you put into the function.

So, we need to go ahead and delete that.

So, this is how you make just a basic user-defined function in VBA.