WEBVTT

00:02.200 --> 00:03.900
Okay guys and welcome back.

00:03.970 --> 00:08.650
Now have a look at another function but this time one with a few more moving parts.

00:08.670 --> 00:13.540
Now we're going to do for this one as we're going say function and we're going to call it.

00:13.550 --> 00:20.650
B they count down and we're gonna say Well we'll stick with my values.

00:20.860 --> 00:24.980
I value as very very moment.

00:25.210 --> 00:26.270
Where are we going.

00:26.350 --> 00:29.020
My value as variant bump bump.

00:29.110 --> 00:29.690
Okay.

00:29.830 --> 00:34.780
So this is going to give us a count down time to our birthday or any date in the future.

00:34.840 --> 00:38.800
So just get back to exhale say 25th of 20th and 16.

00:39.190 --> 00:42.060
That is WIPs let me make it so he can actually see.

00:42.200 --> 00:44.220
So 25th the 12th 2016.

00:44.230 --> 00:46.180
Let's imagine that's our birthday.

00:46.210 --> 00:54.130
Now here what we could say is now we can see that currently it's the second of October 2016 at eight

00:54.130 --> 01:02.160
thirty eight in the morning and we can also say equals WIPs equals that minus that.

01:02.310 --> 01:03.760
But it's no it's the other way around.

01:03.760 --> 01:05.820
When is equals that minus.

01:05.890 --> 01:12.010
Now so we can see that we are currently as I recall this eighty three point six three nine.

01:12.010 --> 01:12.820
Blah blah blah.

01:13.000 --> 01:15.150
Days away from Christmas.

01:15.190 --> 01:15.700
Okay.

01:16.060 --> 01:18.300
So we have a few elements in there.

01:18.490 --> 01:24.640
If we wanted to return he says that number we could literally just write this calculation here inside

01:24.700 --> 01:25.650
this function.

01:25.650 --> 01:30.910
But what if we actually wanted it to say it's x amount of days and this many hours and this many minutes

01:30.910 --> 01:32.320
and this many seconds.

01:32.370 --> 01:34.610
Well that's what we're going to do in this lecture.

01:34.960 --> 01:38.400
So what we're going to do is we're going to add a few variables.

01:38.500 --> 01:43.530
So here we're going to say let's set up the variables that very well.

01:43.780 --> 01:50.520
And let's say dim let's call Count day as capitalize.

01:50.560 --> 01:53.470
So it's easy to read count day.

01:53.950 --> 01:59.920
And I'm going to make them all those variances variants because we're not actually execute as variant.

01:59.950 --> 02:04.260
We're not going to we're not going to be calculating or we're not going to be right in a piece akko

02:04.390 --> 02:07.030
that's going to run over 20000 lines of data.

02:07.210 --> 02:10.560
We're actually going to be writing a piece of code that works on one.

02:10.680 --> 02:19.360
So so let's have a count day when else do we have we can always say let's count our let's have a count

02:19.540 --> 02:25.480
minute when you count second.

02:26.120 --> 02:28.090
Okay so there are four elements.

02:28.100 --> 02:31.930
But when we return the day the number of days the number of hours the number of minutes and the number

02:31.930 --> 02:32.710
of seconds.

02:32.860 --> 02:34.150
Okay fantastic.

02:34.220 --> 02:38.660
Then we need to do a calculation to work out each of those four elements.

02:38.710 --> 02:43.030
So what do we say Count day equals.

02:43.140 --> 02:46.720
And what I want to do is I'm going to build this in states in the States.

02:46.720 --> 02:52.960
My value minus now open and close brackets.

02:52.960 --> 02:59.360
And that's going to give us essentially this that's this formula just here written in plain old excel

02:59.500 --> 03:01.420
in written in VBA code.

03:01.630 --> 03:07.090
Now what we do need to do is actually return the integer So if we become to if you were to come to the

03:07.090 --> 03:08.290
formula here.

03:08.920 --> 03:16.330
What we would actually say here is int open brackets come to the end close brackets and that would tell

03:16.330 --> 03:23.500
us are integer at bally's everything before the decimal place so we have 83 days so we're going to do

03:24.280 --> 03:27.600
is I'm going to shrink that down is going to come in front of this here.

03:27.730 --> 03:32.460
And we're also going to say in it and this will be no.

03:32.500 --> 03:38.380
So now we're going to return 83 just like we did in Excel just there so so far so good.

03:38.410 --> 03:41.870
We've managed to return the integer the number of days.

03:41.920 --> 03:45.430
But what we actually want this to do is be a string.

03:45.940 --> 03:50.560
So what we're going to do is we're going to can cut an eight base and tag on the word day so I'm going

03:50.560 --> 03:56.590
to add and then adding quotes out and then add the space because someone a gap between the number and

03:56.590 --> 03:58.410
the word days.

03:58.420 --> 04:02.470
Now I'm probably going to run across maths because we're going to come up to do was minutes and seconds

04:02.770 --> 04:04.430
and then I'm going to close the quotes.

04:04.450 --> 04:07.200
So now that will return 83.

04:07.270 --> 04:10.090
At this particular moment in time space days.

04:10.090 --> 04:11.260
Fantastic.

04:11.260 --> 04:12.800
Now we also have.

04:12.800 --> 04:15.840
Can't ours count ours.

04:15.940 --> 04:18.280
And that is going to equal them.

04:18.280 --> 04:19.540
I know I haven't finished.

04:19.540 --> 04:20.010
Yes.

04:20.290 --> 04:22.320
Let's just stick zero in there.

04:22.330 --> 04:27.090
One of the do is copy all of that from Bob pace over there.

04:27.250 --> 04:35.310
But instead of saying it one I'm going to say is a is it's our is and yet it's our guy.

04:35.440 --> 04:39.890
So our my value and ours.

04:40.630 --> 04:43.960
And then I'm going to copy that name again.

04:44.410 --> 04:46.380
And now I'm going to be old too.

04:46.420 --> 04:48.570
Let me just tidy them up a little bit there again.

04:48.600 --> 04:51.990
So we're going to be counting minute in you.

04:52.300 --> 04:59.230
And this time we're going to say instead of our only note or I can't type as anyone who's watched only

04:59.230 --> 05:00.860
my michaels's will know me.

05:00.950 --> 05:01.550
It's.

05:02.150 --> 05:10.570
So we should say No minute it's minute there and we will say minutes here so minutes.

05:10.610 --> 05:12.870
And finally we want the seconds.

05:12.950 --> 05:20.740
So copy and paste underneath this is count second a second.

05:20.970 --> 05:32.830
One second and we can name title of this ambits appear in say seconds.

05:33.220 --> 05:34.980
Exclamation points as they go.

05:35.020 --> 05:36.150
We have no bells.

05:36.230 --> 05:38.320
We are using my value the input.

05:38.330 --> 05:42.040
We're going to work out what the day is and add the word days and a comma.

05:42.080 --> 05:45.440
Then we're going to work out the hours then were going to work at the minutes and we're going to build

05:45.440 --> 05:54.950
this string in to the value be day count down so we can say is when we just copy axilla say tell it

05:54.950 --> 05:55.750
in the entire thing.

05:55.760 --> 05:58.150
And so being day count down copy.

05:58.190 --> 06:00.830
Stick that in here and that is gone equal.

06:00.890 --> 06:09.700
We begin to build it all together in the same count day count day and looks at times and count hour

06:12.170 --> 06:14.210
and time again.

06:14.570 --> 06:16.320
So we have missed the enough of that.

06:16.340 --> 06:27.770
So we have Count day count our count min counts second and then when I click off if they all capitalize

06:27.840 --> 06:28.760
I've got it right.

06:28.980 --> 06:29.330
Okay.

06:29.340 --> 06:31.760
So now let's get rid of all that.

06:31.850 --> 06:35.630
In fact let's put that back in 24 of the 12 2016.

06:35.900 --> 06:40.820
And here I am going to say equals b day and you can see here we go.

06:40.820 --> 06:46.720
B they count down open brackets 0 1 point to the date that we're looking for close brackets here sent

06:46.740 --> 06:53.710
and that may go 83 days 15 hours 14 minutes and 59 seconds to Christmas.

06:53.720 --> 06:55.360
Now here's the thing.

06:55.460 --> 07:02.900
What we actually have there is a formula that if we if if we hit EF 2 and come back into it you can

07:02.900 --> 07:04.240
see that it changes.

07:04.460 --> 07:10.640
But if we hit F 9 and um hit an F 9 they can't see that it does not recalculate.

07:10.640 --> 07:15.920
So there is something that we can add into this code that's going to make it recalculate.

07:15.920 --> 07:16.940
So here.

07:17.180 --> 07:21.980
Well I'm going to say is application got volatile.

07:22.760 --> 07:29.000
And when you say application up volatile particularly if you spell application correctly application

07:29.050 --> 07:30.170
are volatile.

07:30.170 --> 07:40.170
This forces a re calc on 9 or calkin m.

07:40.360 --> 07:46.200
So now every time I hit F 9 it's going to recalculate because it is volatile.

07:46.220 --> 07:51.290
Now obviously we've gone through a long and complicated process to actually build that string what we

07:51.290 --> 07:57.350
could do instead of doing all these steps is actually put in something that looks just like this so.

07:57.410 --> 07:59.340
Here's one I've prepared earlier.

07:59.930 --> 08:03.400
So here we have exactly the same thing.

08:03.560 --> 08:09.740
But we have used these individual calculations and built them into a string in just one line.

08:09.740 --> 08:16.220
So if you want to do it that way that's perfectly fine and then you can either just delete here and

08:16.220 --> 08:21.180
you could ram out all of this or you could delete it and literally just have that as your function.

08:21.410 --> 08:21.790
OK.

08:21.830 --> 08:26.060
I'll see him in the next lecture where we're going to have a look at a function where we have more than

08:26.060 --> 08:27.090
one arguments.

08:27.120 --> 08:27.490
OK.

08:27.560 --> 08:29.040
I'll see you in the next lecture.
