In this workout, we are required to create a function that can perform some average and median operations on an input range.

Let's go into the Visual Basic editor and create our function.

We're going to write our function in a module, so we should insert a module.

We have two things to do in our code.

The first is to set up the function and the second is to write the select case code.

A function procedure starts with the word function and the parameters required by the functions arguments.

In this case, we will call our function multistat.

Let's declare the first variable.

The RNG or range parameter will be a range type as the function will require a range to be input as its first argument.

The op parameter is of string type as the function will require the name of the desired operation.

In the second argument, we want the output of our function to be of double data type.

It can take any number, including integers and decimals.

Note now these variables have been stated within the function.

We do not need to write DIM to declare the variables.

Again, following the function format, we now need to write the instructions given each of the select cases of our variable op, the operator, so we write select case on our op parameter.

Select case can be used to assign different instructions to each possible option of our op parameter Where the case is sum.

We are writing in sum in our op parameter.

What do we want to do? We want Excel to perform the sum function where we want the sum function performed.

We need to write worksheet function, sum in Excel VBA, so that Excel will use the sum function.

And we want to sum our first argument, the range parameter, and that was Rng.

In the next case, should we have the word average inserted into our op argument.

We want Excel to go ahead and calculate the average of the chosen range.

So in the case of average, what we want, we want multi stat to calculate the average of our input range.

Our input range is the first parameter in the argument, RNG Our range parameter.

The final case will be where we input median into our op argument.

So in the case of median being entered, what do we want to do? Well, the instructions here should be that multiStat equals worksheet function.

Do median and it's the median of our range parameter here.

Remember that input parameter is the RNG range object, and it is the median of that first range object R range parameter.

That is all of our cases covered and we can end the select.

Let's go back into Excel and test.

This works back in the front end of Excel.

Let's use our multistat function equals multistat.

Here's our range.

The second argument is our operator.

This was of string type and we need to type the name of our desired operation this time average, and we get the average of all of those numbers.

Finally, let's try multistat with the median stated as our desired operator In that second argument, the first argument is our desired range, the RNG parameter.

The second argument, that's our desired operation.

We want to go with median and we get the median of all of those figures.

We are now required to write a procedure to display the function information for our new multistat procedure.

Let's go ahead and do that back into Excel Visual Basic, and we're going to go back to the Visual Basic editor.

We can make our function look like an inbuilt Excel function by providing specific function information and also including it within the appropriate function category.

The code that we're going to write only has to be executed once for Excel to store this new function.

Information. We're going to use the macro options method to display a user-defined function in a built-in category within the function dialogue box.

So let's write a new sub procedure and let's call our sub procedure multistat.

Describe, and we're going to use the macro options method, so application Macro options.

The macro, well that will be equal to our multita function.

The description argument.

How do we want our new function to be described? Well, our new function performs the chosen statistical operation on a chosen range.

Next, what category do we want our new function to feature in? We want to feature in category 4.

Category 4 is the statistical category.

Here you can see all of the different categories so that we can ensure our function appears in the appropriate category onto the next argument.

And that's the argument descriptions.

This is where we'll have a description of each of the required arguments to our function.

What cues do we want to give when our users use this function tool? Argument descriptions will equal the array.

Our first argument we want to describe as the chosen range of cells, and our second argument, the name of the operator.

That is all of our arguments completed.

We can run this macro once and then let's test out our new function in a spare cell.

I'm going to input the name of our decide operation here.

Let's choose some, and I'm going to start writing the name of our function multistat.

And we can have some help on multistat here we're told that multistat performs a chosen statistical operation on a chosen range, and our range argument is the chosen range of cells.

Well, let's choose those.

The next argument, well, the next argument is the name of our operator.

I'm going to choose some once I press.

Okay, we can see that familiar number of 122, the sum of all of those numbers in the range.