2 videos 📅 2025-04-02 09:00:00 Canada/Central
21:02
2025-04-02 15:35:53
27:55
2025-04-02 17:04:20

Visit the Visual Basic for Applications (VBA) for Analysts course recordings page

United Arab Emirates - Visual Basic for Applications (VBA) in Excel - Advanced

                WEBVTT

00:00:03.880 --> 00:00:06.180
How can I interact with your machine?

00:00:10.300 --> 00:00:15.420
Well, okay, you're going to subtract yes,

00:00:15.640 --> 00:00:21.580
start now, but yeah,

00:00:22.900 --> 00:00:26.840
so you need to use parentheses.

00:00:31.740 --> 00:00:39.200
No, no, no, no, no, the column is number five, one, two, three, four,

00:00:39.200 --> 00:00:52.060
no, the column is number six, and you need to be two,

00:00:52.360 --> 00:00:55.060
so the start now is number five,

00:00:55.060 --> 00:01:00.420
so you need to start now with minus one, right?

00:01:00.520 --> 00:01:05.940
Yeah, like if the column plus one equals six and I need to get two,

00:01:06.260 --> 00:01:08.720
yeah, so it's five, three,

00:01:08.940 --> 00:01:13.360
minus one, look this, look this, I'm going to,

00:01:14.660 --> 00:01:17.900
we have DIC your machine,

00:01:18.240 --> 00:01:20.540
we have five here, five, six,

00:01:20.540 --> 00:01:24.480
seven, and in the six,

00:01:24.600 --> 00:01:27.740
I have five, six, seven, okay?

00:01:27.820 --> 00:01:29.500
Something like that. Okay.

00:01:30.020 --> 00:01:34.860
You wrote number five as a start number, right?

00:01:38.860 --> 00:01:46.080
We need that this number moves to this column, right?

00:01:46.900 --> 00:01:47.540
Yeah.

00:01:48.240 --> 00:01:52.240
So we need one, two, three, four.

00:01:53.580 --> 00:02:03.840
You have four here, and this four is just the number you wrote, minus one,

00:02:04.820 --> 00:02:06.220
so this is what we need,

00:02:07.060 --> 00:02:08.840
the number you wrote, minus one.

00:02:08.860 --> 00:02:10.660
But instead of minus one,

00:02:10.920 --> 00:02:12.580
but there's still the plus one here.

00:02:13.280 --> 00:02:15.180
No, the plus one is another thing.

00:02:16.060 --> 00:02:21.040
You need, you need, no my code, baby.

00:02:24.280 --> 00:02:25.520
No my code.

00:02:28.820 --> 00:02:30.520
Yeah, but I just don't know why,

00:02:30.620 --> 00:02:35.760
why I need to start with minus one, minus one, all my names.

00:02:36.200 --> 00:02:39.740
If you, if we need, if you need,

00:02:41.620 --> 00:02:45.620
I'm going to do this, okay?

00:02:47.140 --> 00:02:50.120
Do you want, do you want to do this and this?

00:02:52.400 --> 00:02:55.820
Remember, remember this is a,

00:02:58.840 --> 00:03:05.800
remember this, this is what we have before we use input, okay?

00:03:05.800 --> 00:03:09.560
Oh, you have a new folder.

00:03:11.880 --> 00:03:15.520
Look, look, look my, look my, my screen, please, look my screen.

00:03:16.140 --> 00:03:16.620
Yeah.

00:03:17.620 --> 00:03:23.420
This is what we had before we use input box, remember?

00:03:24.760 --> 00:03:25.060
Yeah.

00:03:25.280 --> 00:03:28.860
This is before, so if we will use,

00:03:29.000 --> 00:03:34.140
if we use this without input box, it works, works as, as we want.

00:03:34.140 --> 00:03:38.400
But, but when we start using input box,

00:03:39.020 --> 00:03:41.920
the start number move the columns.

00:03:43.200 --> 00:03:45.160
What numbers of columns move?

00:03:46.180 --> 00:03:47.460
Move this.

00:03:48.080 --> 00:03:53.420
If I write a number, number five,

00:03:56.100 --> 00:03:58.700
moves four columns.

00:04:01.300 --> 00:04:08.980
If I write six, it moves five columns and so on, right?

00:04:09.440 --> 00:04:11.600
Is it okay or not, or not?

00:04:12.640 --> 00:04:14.460
Let me do it again.

00:04:14.820 --> 00:04:16.460
Let me do it again.

00:04:18.300 --> 00:04:22.160
I'm going to give you all of this.

00:04:47.740 --> 00:04:50.700
This is the first example, right?

00:04:52.560 --> 00:04:53.700
It's not, they're going to do it again.

00:04:54.900 --> 00:04:56.700
This is the first example, okay?

00:04:56.700 --> 00:05:04.240
Now, if I write that goes from one to 12,

00:05:04.840 --> 00:05:10.700
we use this to start in the second place, okay?

00:05:12.740 --> 00:05:12.760
Okay.

00:05:13.840 --> 00:05:19.640
Look this, I'm going to change this number one for five, okay?

00:05:20.040 --> 00:05:21.460
Number one for five.

00:05:21.460 --> 00:05:28.900
Just in the row columns, sorry, in the row here, okay?

00:05:30.060 --> 00:05:33.100
Look this, okay?

00:05:34.000 --> 00:05:37.680
So, let's go into two cities.

00:05:38.740 --> 00:05:43.000
We have one, two, three, four.

00:05:45.880 --> 00:05:49.460
How many spaces do we have?

00:05:49.680 --> 00:05:50.380
Four, right?

00:05:50.380 --> 00:05:54.780
I change it by five and we have four spaces.

00:05:55.320 --> 00:05:57.400
I'm going to change it for several.

00:05:57.800 --> 00:06:02.580
We have one, two, three, four, five, six.

00:06:03.900 --> 00:06:12.080
Okay, so if I change this number, it moves minus one.

00:06:12.800 --> 00:06:19.120
If I write ten, the number of spaces is going to be nine.

00:06:19.120 --> 00:06:25.300
If I write eleven, the space will be ten.

00:06:25.580 --> 00:06:26.500
See it again?

00:06:28.180 --> 00:06:28.480
Okay.

00:06:29.400 --> 00:06:36.180
So, if I need to these values start here,

00:06:37.760 --> 00:06:41.440
I'm going to need to do this.

00:06:41.900 --> 00:06:45.960
This number, seven, minus, or less one.

00:06:45.960 --> 00:06:45.980
Okay.

00:06:47.840 --> 00:06:50.260
I need to subtract this number.

00:06:50.260 --> 00:06:51.260
I'm going to do it again.

00:06:51.740 --> 00:06:52.180
Okay.

00:06:52.580 --> 00:06:55.260
If this is eight, I need to change this for eight.

00:06:55.400 --> 00:06:58.420
And now we will start.

00:06:58.800 --> 00:07:00.280
Yes, that's what I don't understand.

00:07:00.520 --> 00:07:06.420
So, in the formula, zero row plus one, minus eight, minus one.

00:07:07.560 --> 00:07:07.920
No.

00:07:08.640 --> 00:07:10.780
Look, you're going to see my formula.

00:07:11.220 --> 00:07:12.320
You're going to see my formula.

00:07:13.180 --> 00:07:18.440
The difference in my formula is that this plus one is here.

00:07:18.540 --> 00:07:19.780
It's just a difference.

00:07:20.080 --> 00:07:21.720
Look my formula.

00:07:22.320 --> 00:07:25.600
I can change it here.

00:07:26.220 --> 00:07:27.520
And it's the same.

00:07:33.980 --> 00:07:34.700
Yeah.

00:07:34.700 --> 00:07:36.700
Minus one, right?

00:07:37.520 --> 00:07:38.500
We need to do this.

00:07:44.480 --> 00:07:45.040
Okay.

00:07:47.920 --> 00:07:53.360
But remember that call goes from here to here.

00:07:53.620 --> 00:07:54.820
It's a variable, this one.

00:07:55.860 --> 00:07:56.560
It's not a static.

00:07:58.040 --> 00:07:59.080
Static is this number.

00:08:00.400 --> 00:08:01.180
This is a static.

00:08:01.420 --> 00:08:02.680
This is a variable.

00:08:03.200 --> 00:08:03.720
It's changed.

00:08:04.580 --> 00:08:06.560
Remember we're in a loop.

00:08:07.240 --> 00:08:09.480
The first number of call is a star number.

00:08:09.580 --> 00:08:10.800
For example, five.

00:08:10.800 --> 00:08:11.660
Five.

00:08:12.120 --> 00:08:14.040
The next six, seven.

00:08:14.260 --> 00:08:15.540
So, it's going to change.

00:08:17.280 --> 00:08:17.960
This is not.

00:08:18.320 --> 00:08:20.220
This is fixable.

00:08:23.300 --> 00:08:24.380
You don't understand, right?

00:08:24.700 --> 00:08:25.560
No, no.

00:08:26.180 --> 00:08:27.440
You don't understand me very well.

00:08:29.640 --> 00:08:37.180
I don't understand very well, but I can't allow you to do things like that.

00:08:37.440 --> 00:08:37.960
Okay.

00:08:37.960 --> 00:08:43.680
Well, I will think in a better way to explain you.

00:08:44.300 --> 00:08:44.760
Not now.

00:08:44.820 --> 00:08:46.960
I think for tomorrow, the first time.

00:08:49.220 --> 00:08:54.400
That is, well, let me think to explain it in a better way.

00:08:54.820 --> 00:08:55.200
Okay.

00:08:56.720 --> 00:09:05.080
But now, in this moment, I need to know that you understand how to use loops.

00:09:05.080 --> 00:09:07.180
It's the first of all.

00:09:07.280 --> 00:09:13.820
Do you know how to use for, how to use do while, how to use do until, right?

00:09:14.840 --> 00:09:19.280
Yes, but I think with all of that, I need to practice, right?

00:09:19.800 --> 00:09:20.840
Yes, I know.

00:09:21.080 --> 00:09:22.360
We're going to practice tomorrow.

00:09:22.700 --> 00:09:25.960
We're going to take at least two hours to practice this.

00:09:27.000 --> 00:09:27.300
Okay.

00:09:27.300 --> 00:09:28.380
To improve our skills.

00:09:28.620 --> 00:09:28.840
Okay?

00:09:29.940 --> 00:09:30.320
Don't worry.

00:09:30.440 --> 00:09:30.580
Yeah.

00:09:30.580 --> 00:09:31.480
Don't worry.

00:09:31.480 --> 00:09:35.320
I will prepare very similar examples with this.

00:09:36.440 --> 00:09:36.700
Right?

00:09:37.660 --> 00:09:42.460
So, the other way that I need that you understand is how to use input loops.

00:09:43.600 --> 00:09:46.100
And I think you got it, right?

00:09:47.000 --> 00:09:49.160
Yeah, that's what I know.

00:09:49.560 --> 00:09:49.740
Yeah.

00:09:51.020 --> 00:09:51.400
Okay.

00:09:52.580 --> 00:09:58.180
So, let's going to make a very simple example.

00:09:58.840 --> 00:09:59.260
Okay?

00:09:59.260 --> 00:10:00.300
You're going to do this.

00:10:00.300 --> 00:10:13.160
I need that you use an input box to answer for the age of a person.

00:10:14.060 --> 00:10:14.940
Okay?

00:10:15.700 --> 00:10:25.800
And I need you to display a message with the mother age that has 20 more years.

00:10:25.800 --> 00:10:35.560
For example, if I write that I have 20 years, it's going to display a message that my

00:10:35.560 --> 00:10:38.340
mother has 40 years.

00:10:39.860 --> 00:10:40.220
Okay?

00:10:53.100 --> 00:10:54.500
Okay.

00:10:54.500 --> 00:10:54.800
Okay.

00:11:41.340 --> 00:11:42.740
Okay.

00:12:21.280 --> 00:12:22.680
Great.

00:12:24.360 --> 00:12:26.200
That is great.

00:12:27.780 --> 00:12:28.380
Okay.

00:12:29.800 --> 00:12:37.640
So, you can use a very well input box and message box and use very well the variables.

00:12:38.380 --> 00:12:40.000
That's very, very good.

00:12:40.500 --> 00:12:41.040
Okay?

00:12:43.020 --> 00:12:48.780
And we have to make now an example.

00:12:49.780 --> 00:12:54.940
What about if I wrote my birthday?

00:12:56.500 --> 00:12:57.300
Okay.

00:12:57.880 --> 00:13:02.140
And I need that display the message with my age.

00:13:06.780 --> 00:13:09.280
That's the exercise.

00:13:10.460 --> 00:13:12.140
We grow in an input box.

00:13:12.940 --> 00:13:14.080
My age.

00:13:14.420 --> 00:13:15.540
My birthday.

00:13:17.920 --> 00:13:18.400
Okay.

00:13:20.660 --> 00:13:23.260
And this place a message with my age.

00:13:23.260 --> 00:13:31.000
So, we need to calculate the years between today and my birthday.

00:13:32.460 --> 00:13:35.360
You know how to do it or we do it tomorrow that?

00:13:37.000 --> 00:13:38.200
Okay.

00:13:38.200 --> 00:13:40.300
I'm going to try.

00:13:40.700 --> 00:13:42.200
But I can't get it.

00:13:47.240 --> 00:13:48.440
Okay.

00:13:52.160 --> 00:13:53.720
And it.

00:13:54.060 --> 00:13:56.160
Yeah, that's very good.

00:14:15.900 --> 00:14:20.460
I think it's integer because it's an H, right?

00:14:22.480 --> 00:14:25.720
The answer I think is integer because it's going to be an H.

00:14:26.620 --> 00:14:29.540
But won't it have a decimal point to it?

00:14:30.500 --> 00:14:36.460
No, because we are going to subtract years and years are integers.

00:14:36.920 --> 00:14:40.200
But what is it, 32.5 years?

00:14:40.200 --> 00:14:44.460
Oh, come on, let's, let's, let's, let's do it.

00:14:44.560 --> 00:14:46.480
Don't worry, it's okay.

00:15:01.300 --> 00:15:07.980
We have a function called year that they start the year of our date.

00:15:08.840 --> 00:15:10.700
Oh, okay, then can you show me?

00:15:10.900 --> 00:15:12.300
Because I didn't know that.

00:15:12.420 --> 00:15:13.880
Yeah, just a function.

00:15:14.620 --> 00:15:16.500
The main function is year, yes.

00:15:17.260 --> 00:15:18.800
Like am I supposed to know that?

00:15:18.860 --> 00:15:20.120
I don't know what to do with this now.

00:15:20.300 --> 00:15:29.320
No, because now, like in the function, open parentheses and write the date.

00:15:29.820 --> 00:15:32.020
Oh, so you're just, you're not writing the full date.

00:15:32.060 --> 00:15:33.820
You're just writing the birthday year?

00:15:35.280 --> 00:15:36.220
I'm going the other way.

00:15:36.620 --> 00:15:37.200
Are you okay?

00:15:38.360 --> 00:15:38.760
Are you all right?

00:15:39.200 --> 00:15:39.240
Yes.

00:15:39.240 --> 00:15:40.740
Okay, I'm going to do it.

00:15:40.800 --> 00:15:42.220
I'm going to try to do it.

00:15:42.220 --> 00:15:48.420
This is some page from the new year.

00:15:49.000 --> 00:15:54.220
And I'm going to use current date.

00:15:55.240 --> 00:15:58.880
I'm going to use the age as integer.

00:16:00.680 --> 00:16:00.760
Okay.

00:16:03.100 --> 00:16:07.780
We're going to use another page since input date.

00:16:10.540 --> 00:16:11.540
Okay.

00:16:11.540 --> 00:16:14.640
Now, this is input path.

00:16:18.000 --> 00:16:19.300
Print date.

00:16:22.800 --> 00:16:31.700
We're going to, and this is going to see tomorrow, but I'm going to now just to see it.

00:16:32.340 --> 00:16:33.500
We have see date.

00:16:33.500 --> 00:16:37.620
See they transform a string to date.

00:16:41.500 --> 00:16:44.540
So input date is a string, right?

00:16:45.000 --> 00:16:48.920
I wrote in the input box a string looks like a date.

00:16:50.220 --> 00:16:50.500
Okay.

00:16:50.540 --> 00:16:53.120
But I did that the time is a date.

00:16:53.720 --> 00:16:58.520
So I transform this string using this function to a date.

00:16:58.940 --> 00:17:01.920
So birthday really is a date.

00:17:01.920 --> 00:17:04.380
Do you credit this or not?

00:17:04.380 --> 00:17:06.480
I've just never seen that see date before.

00:17:06.700 --> 00:17:07.780
I don't know what that is.

00:17:07.860 --> 00:17:08.880
I haven't looked it up.

00:17:09.100 --> 00:17:09.560
Yeah, okay.

00:17:10.300 --> 00:17:18.240
So I think that this function, another or like that, we're going to see tomorrow.

00:17:18.540 --> 00:17:19.100
It's better.

00:17:19.380 --> 00:17:19.780
Okay.

00:17:20.180 --> 00:17:20.480
Right?

00:17:20.560 --> 00:17:21.000
It's better.

00:17:21.080 --> 00:17:23.680
So I'm just going to do the example in this one.

00:17:23.840 --> 00:17:25.440
I'm going to do it tomorrow.

00:17:26.480 --> 00:17:27.020
Okay.

00:17:27.620 --> 00:17:27.920
Yeah.

00:17:27.920 --> 00:17:33.020
So tomorrow we'll see at the same time, nine o'clock.

00:17:33.720 --> 00:17:33.900
Okay.

00:17:34.040 --> 00:17:38.300
I'm going to and we're going to do more practice.

00:17:39.980 --> 00:17:40.380
Yeah.

00:17:40.540 --> 00:17:41.180
Okay.

00:17:42.720 --> 00:17:44.100
So see you tomorrow.

00:17:45.760 --> 00:17:46.860
It's time.

00:17:48.760 --> 00:17:48.980
Okay.

00:17:49.160 --> 00:17:49.580
See you tomorrow.

00:17:49.680 --> 00:17:50.240
See you tomorrow.

00:17:50.620 --> 00:17:50.700
Bye.

00:17:50.940 --> 00:17:51.240
Thanks.

00:17:57.920 --> 00:17:58.720
Okay.

00:18:43.660 --> 00:18:45.060
Okay.

00:19:14.160 --> 00:19:15.560
Okay.

00:19:41.320 --> 00:19:42.720
Okay.

00:20:11.580 --> 00:20:12.980
Okay.

00:20:41.300 --> 00:20:42.700
Okay.

00:21:01.120 --> 00:21:02.520
Okay.