WEBVTT

00:02.120 --> 00:02.780
Okay guys.

00:02.780 --> 00:04.010
And welcome back.

00:04.010 --> 00:10.760
Now in this section what we're going to have all look at are the apps the user defined functions.

00:10.760 --> 00:17.420
Now as we know there are loads and loads literally hundreds of functions already built into Excel as

00:17.420 --> 00:22.760
a standard and a functionally something like the sum function which we can see just here.

00:22.760 --> 00:26.440
So when we type equals some sum is a function.

00:26.510 --> 00:32.120
And now years ago Excel didn't have anywhere near the number of functions that it has currently.

00:32.510 --> 00:36.770
So there was a need to create a function specifically for reuse as need.

00:36.770 --> 00:42.380
Now since then most things that may have been added as a standard but there still could be an instance

00:42.380 --> 00:48.590
where you need to perform a specific calculation that is unique to your need.

00:48.590 --> 00:55.470
Now just to give you an example years ago PI wasn't included as a standard function in Excel.

00:55.490 --> 01:01.520
So if you wanted to calculate Pi you needed to create a u d f user defined function.

01:01.670 --> 01:06.140
So Pi is going to be one of the first things that we have a look at but firstly a couple of things to

01:06.140 --> 01:10.760
remember functions live in standard modules.

01:10.760 --> 01:15.920
So here you can just say that I have added module one just the standard function just like we've done

01:15.920 --> 01:17.750
with the subs up to this point.

01:17.750 --> 01:22.860
So as a function is always coded in a standard module.

01:23.000 --> 01:31.760
Thing number one thing number two functions can be called by their name in the word book in which you

01:31.790 --> 01:33.060
created them.

01:33.080 --> 01:39.350
So if you created a function called my clever function you could only call that from the workbook in

01:39.350 --> 01:43.080
which you created it and where it saved all.

01:43.190 --> 01:49.490
If you have that workbook open you can use it in another open workbook but you will have to change the

01:49.490 --> 01:50.530
way you call it.

01:50.570 --> 01:52.120
But we'll come to that shortly.

01:52.460 --> 01:53.010
Okay.

01:53.150 --> 02:00.620
So they go they use defined functions live in a standard module and they differ slightly from the subroutines

02:00.620 --> 02:05.570
that we've looked at up to this point inasmuch as they begin with the word function.

02:05.570 --> 02:11.840
So instead of saying Serb we will say function so we will say function pi and yes I know that's not

02:11.840 --> 02:13.770
how you spell pi.

02:13.780 --> 02:18.800
Now what we're going to do here we're going to specify which arguments are going to be requires.

02:18.810 --> 02:25.610
I'm just going to say my value and I'm going to say that as a very and because I don't know whether

02:25.610 --> 02:30.980
it's going to be a long number or a number with a load of decimal places after it so var. is going to

02:30.980 --> 02:33.690
capture all of those when they hit enter.

02:33.710 --> 02:37.580
You can see that end function is already being had added.

02:37.700 --> 02:40.970
Just like when you type sub the end subs already at it.

02:40.970 --> 02:41.590
So here we go.

02:41.590 --> 02:43.920
We've now got this shell of our function.

02:43.910 --> 02:49.390
Now what we need to do is add some code into it now to get the return value.

02:49.460 --> 02:55.950
What you need to say is the return value is going to be the same as the function name.

02:55.970 --> 02:58.790
So here the function name is Pi.

02:59.030 --> 03:04.630
So I'm going to type pi equals and then I'm going to say this argument.

03:04.700 --> 03:05.720
My value

03:08.180 --> 03:12.840
multiplied by I think I can put what pietists that many.

03:12.950 --> 03:18.750
This will place a finger is three point 1 4 1 5 1 5 9 something like that.

03:18.770 --> 03:19.460
So they go.

03:19.490 --> 03:21.830
Now that is our function complete.

03:21.830 --> 03:23.500
So we have function pi.

03:23.660 --> 03:29.290
My value that is going to be the argument that is what we're going to perform our calculation on.

03:29.540 --> 03:33.590
And we're saying pie because that's the name of the function equals my value.

03:33.590 --> 03:36.670
Multiply by three point 1 4 1 5 9.

03:36.710 --> 03:42.870
So here when I come over to Excel I cannot type equals Pi.

03:42.890 --> 03:46.910
And you can see that that is now a function with Excel.

03:47.090 --> 03:53.120
And when I double click it or when he's open brackets we can know what the arguments and now number

03:53.120 --> 03:55.100
one drawback of custom functions.

03:55.100 --> 04:00.220
You do not get all tips so you're going to have to remember what the arguments are.

04:00.230 --> 04:08.540
So now if I said PI 10 close brackets hit enter you can see that the calculation of Pi for ten is three

04:08.540 --> 04:09.530
point.

04:09.530 --> 04:12.460
Why is it you want Point 4 1 5 9.

04:12.470 --> 04:13.780
I need new glasses.

04:14.000 --> 04:17.230
Okay so that's a very simple demonstration of how you do it.

04:17.360 --> 04:20.020
So let's have a look at another function.

04:20.060 --> 04:22.920
Let me just make that look a little bit pretty like that.

04:22.920 --> 04:28.630
So okay so an X function John is going to be square me.

04:28.640 --> 04:36.350
The almost is definitely a square function in there in Excel but when I say Square me so here's a new

04:36.350 --> 04:37.580
function.

04:37.580 --> 04:38.990
So this time what we're gonna say.

04:39.110 --> 04:40.610
I haven't said what we're going to use.

04:40.610 --> 04:46.730
We're going to use my value as a variant again so here a square me.

04:46.730 --> 04:51.210
My value as variant so a.a Square.

04:51.220 --> 04:54.130
Me because it has to have the same name as the function.

04:54.360 --> 04:55.480
Then say equals.

04:55.490 --> 05:01.030
And it's going to be my value multiplied by my value.

05:02.090 --> 05:03.910
And they go it's as simple as that.

05:03.910 --> 05:09.740
So here if I send equals squirt an hour and they go we can now see.

05:09.770 --> 05:18.030
Square me and if I said five and closed off the rack gets hit enter they go the square of 5 is 25.

05:18.260 --> 05:20.450
And similarly I can actually reference ourselves.

05:20.450 --> 05:24.450
I could say Square me open racquets.

05:24.460 --> 05:31.570
Point to that so close brackets and hit enter may go the square of 25 is 625.

05:31.700 --> 05:36.200
Okay I'll see him in the next lecture where we're going to have a look at another simple use for the

05:36.200 --> 05:37.470
user defined function.

05:37.480 --> 05:38.690
I'll see you there.
