VBA: User-Defined Functions - YouTube

This screencast is going to go over an example of a user-defined function in VBA.

We're going to create a piece wise function here, the function name is going to be interval,

it's going to output y according to the following constraints: So when x is greater than 9 we're

going to calculate y with one formula, if x is between 0 and 9 we're going to calculate

it as 10x +10, and then if x is less than 0 y is going to be 10.

One of the first steps to any sort of sub or function, we're creating a function, is

to set up a flow chart.

We have start, so we're starting this program, we're inputting x, and that's going to come

in the form of x is going to be input into the function name, because in Excel, the user

is just going to write in equals interval of x, so that's going to be the input, and

then we are going to do a couple of conditional statements.

We have a decision here, whenever we have a decision, that's a diamond, is x greater

than 9?

So if you recall, when x is greater than 9 the function value is going to be 15 times

root 4x+10, and then so on.

So the first thing we see if x is greater than 9, if so we calculate y to be equal to

that in terms of x, and then we move along.

If x is not greater than 9, so it's less than 9, or equal to 9, then we go to another decision

point, so we have another decision point here, is x greater than or equal to 0?

If that's true then we calculate y using the second formula 10x+10, if x is not greater

than or equal to 0, and in that case y is just equal to 10, and then you'll notice that

all of these sort of come back together to this central junction here, and then after

that we're going to display or output our y, and then we finish it up with an end, so

these are typical flowchart elements.

So in Excel we can go and create a new module, I open up the editor, and what I've done is I've

already created a new module, so you can insert a new module to get to here, start it with

function, the argument of my interval function, which is the name, is x.

This can really be anything, it's just used internally in this function.

The next thing I need to do is dim things other than the input.

If I leave it like this the editor is going to assume that it's a variant type, but if

I want to force it to be something like a double I can write it in there.

So I'll just leave it as double there, that's not really necessary.

If I am working with any other variables that are going to be in my code then I need to

declare them.

So y is going to be the output and that is going to be double.

Going back to the flowchart I've input x, I've declared things, now I have this diamond,

and a diamond is an If-Then.

So I know that I am going to have an If, and the first one is "If x > 9 Then" I am going

to define the output, which is y, is going to be equal to 15 times root 4 of x, plus

10.

Now what if x is not greater than 9, well we can put in an Else-If, so Else-If x is

greater than or equal to 0 than y is equal to 10 times x plus 10, that was the second

piece of our piece wise function, and then finally if it's not greater than 9, it's not

greater than 0, that means it must be less than 0, so we're working now with this lower

portion here, so we have the if here, and then we have the Else-If here, and then the

Else is really like what you do if nothing else, and that's y is equal to 10.

So "Else" "y =10".

All if statements need an "End If", so we have to "End If", and now we are back.

This is the "End If", this junction here, that's our "End If", and now the last thing

we need to do is display this before we terminate, so to display the output of the function is

always the name of the function, that's what's going to be placed into the cell in Excel,

So I am just going to say "Interval = y".I could write this a little bit easier, instead of defining a new variable y. I could just

write interval here equals that, and in that case I would also put interval equals this,

so I am replacing all the y's with interval, and I wouldn't have to have this line, and

this line, but sometimes it makes more sense just to sort of make intermediates

that then you an use as your output, so the output of the function is interval, and that going to be y.

So we're ready to go, I am going to go back over to Excel. I've put in a bunch of values for x

and now we're going to calculate y using our interval, so I start typing in interval,

it recognizes interval here, so I can double click on that , that's a user defined function

and now I am just going to do the interval of our x value. I press enter, and it returns a y,

so is that consistent with our original piece wise function? If x is less than 0

which it is, it's negative 10, it should be 10, so looks like our function interval is working properly

and now I can drag this down, that's one nice thing about user defined functions

you can just easily drag these things down like you would any other function that's built into Excel

And if I wanted to plot this I could just insert a chart, a scatter chart if I wanted to,

a line chart, and so on. This is how you can create and use user defined functions in VBA.