Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:00
Okay, We're back in
00:03
the XL
00:04
window
00:06
and let's use this shape. And, uh, let's put some text here
00:11
and then let's see
00:13
start
00:15
accumulation.
00:18
Let's make it slightly bigger.
00:23
So it's
00:26
like, this
00:29
changes assistant old
00:33
and, uh,
00:37
sorry about that.
00:40
And yeah, we have.
00:42
Okay, so it's ah, little bit big. So it's ah, reduce it to todo
00:49
and yeah, we have this Don't still visible
00:54
s so it's, uh,
00:57
because slightly smaller.
01:00
Yeah,
01:00
like this. So start situation. We have this shape.
01:06
So it's click on this shape.
01:07
I'll get this
01:11
crust
01:12
and then let's assign macro
01:17
and it's going to be just called right Triangle click. Let's do you
01:23
And then
01:26
you're just going to hole this form
01:30
by clicking by typing user form one that show,
01:37
that's all.
01:38
We just want to invoke this user form
01:41
in this moment.
01:42
So
01:44
that's it. We're going to save it.
01:47
We're going to close
01:49
this,
01:51
and now
01:57
when we click, we get our user form.
02:00
So let's type here some numbers.
02:05
45.
02:07
Let's go. Thank calculate and we got the correct number. So we got to. That means that two fields have numbers
02:15
and was changed this
02:17
somehow. So it's good to hear
02:21
and maybe five here
02:23
and we still get to.
02:27
And if we clicked three here,
02:29
you'll get three. So
02:30
all three of field, this is not a
02:34
should be there. So we have to,
02:37
um,
02:38
check s So so then the macro will have to act upon it and this place a message.
02:46
So at this point, we have finished
02:50
the testing off this phase of our A macro.
02:53
So let's close this
02:57
and let's go to a developer. Let's go to visual Basic.
03:02
Let's go. You can use your form
03:06
and let's look at the coat
03:07
so we don't need this message books here anymore. So let's, uh,
03:13
just put it
03:15
as a comment
03:16
and let's continue with the coat.
03:21
So,
03:23
um,
03:24
so at this point,
03:27
if C is not too, then we have to act upon it,
03:32
and, uh,
03:35
we have already, Detective, this is an americ, and ah,
03:38
we have checked that the third field
03:43
has no number in it
03:46
now. It can contain some texts.
03:50
Ah, but, uh,
03:52
So if one field contains text and the other two number,
03:55
uh, this macro will automatically assume that we have to put that in this dirt field, regardless of this, there is a text there or not.
04:04
So let's listen to complicate this and let's not complicate the logic. It would be,
04:12
ah, interesting to look at, uh,
04:15
if these fields are empty.
04:18
But
04:20
let's let's not do that at the moment. So let's say like this if
04:25
and then C is different than two. So what do we do then?
04:31
Then
04:32
we
04:33
display message books,
04:40
Books
04:41
says, for example,
04:43
you needs
04:45
Okay,
04:46
Hypo.
04:47
So you
04:51
then
04:55
you needs
04:58
two.
04:59
And there
05:00
to Mary Coe
05:04
values
05:11
you close this. So if c is different than do so if you have
05:15
on
05:15
other than two textbooks is filled with numbers release, play this message.
05:23
And, uh,
05:26
because this is going to be a structured if we're going to press, enter here
05:30
and do this
05:34
and, uh, yeah,
05:36
I hope raised message books
05:42
and then here
05:45
going type else.
05:47
And then let's see what happens.
05:50
If not
05:53
so we're going to type if
05:56
and no,
05:58
we're going to see which to have numbers. So we know that to have numbers, but you know, to know which two.
06:03
So we're going to type if is America. So this is again the same function
06:11
off
06:13
text
06:15
box one the
06:17
Well, you.
06:28
And
06:30
is Merrick
06:32
off?
06:33
Sorry.
06:36
Text box to you
06:43
then. So why, this
06:46
is America
06:47
is, uh,
06:49
logical function and it turns through a false.
06:54
So if textbooks one and textbooks to our America,
07:00
we're going to get through and through, and we should say true and true, it's true.
07:05
So in that case, we know that we haven't. The user of this form has entered A and B
07:14
and we don't have in age, we don't have any number.
07:19
So we have to calculate the age
07:23
and then what we do with H.
07:27
We have to check
07:29
if textbooks one or a
07:32
is equal or greater zero because the side of the triangle cannot be
07:40
negative.
07:41
So just the check up. So we're going to say, Why didn't we do that before?
07:46
Ah, because, um,
07:48
we could have done it here. Here, where then is
07:54
and then we could make the structured if and then we could say then and then we had sequences C plus one. So we add one to see.
08:05
And then we could have added
08:09
another if
08:11
check
08:11
if x books. One value is
08:15
greater than zero.
08:16
So if it isn't,
08:18
then ah ah, We have to
08:22
display a message that you have to enter Positive,
08:26
Billy. But let's do it here as well. Let's make
08:31
our life little bit interesting.
08:33
So if
08:37
next
08:39
hurry
08:41
box,
08:45
I want that value
08:50
is
08:52
lower or equal zero
08:56
and
08:58
text books.
09:01
Well, you
09:01
Sorry. Textbooks to
09:05
ducks.
09:07
Well, you
09:07
it's also
09:11
was equal zero.
09:13
Then
09:16
this is 00 Is my mistaking typing
09:22
then
09:24
message books
09:30
and
09:31
you can
09:33
and sir,
09:33
only
09:35
was too
09:37
numbers.
09:39
And let's close this.
09:43
So
09:45
syntax is correct.
09:46
Then we'll just copy it
09:50
and, uh,
09:58
sorry.
10:01
Here, we should have leased
10:03
or
10:05
and we don't need this anymore.
10:09
You could have vanity to or we couldn't done it like this.
10:13
So we also have to check what is
10:20
Field
10:20
H or textbooks? Three. So we're going to do if
10:26
and then text books.
10:28
Three. That value
10:31
is different than empty.
10:37
Then
10:39
message books,
10:43
and it should be
10:45
field
10:46
age has to be
10:50
empty.
10:52
So this'd is what we
10:56
did, you know.
10:58
And now we are sure that we have correct. Sign off numbers in boxes. Woman, too, and that textbooks three is empty so that they haven't entered anything in it.
11:11
And now we can proceed to calculation
11:16
and we're going to call. Results are
11:18
variable here, and it's going to be equal. Sq R or square root off
11:28
text
11:30
box.
11:31
Well, you
11:33
story textbooks one, don't you
11:39
times text books,
11:41
Wrong value.
11:46
Plus
11:48
this box
11:50
to that, will you
11:54
moves?
11:54
Yeah,
11:56
test box to the
12:00
well. You
12:01
We close it,
12:03
We have some typos
12:05
correct in
12:07
and
12:11
presenter
12:13
corrects it puts the space is the editor put spaces in the right places
12:18
and we have done that.
12:20
So now we have to put this value in the form
12:26
and, uh, step user
12:31
form
12:31
one
12:33
that
12:35
textbooks
12:37
three
12:37
dot
12:39
value
12:41
equals
12:41
result.
12:45
And also what we want to do is to
12:48
indicates somehow that we have calculated that field and that the result is there.
12:54
So user form
12:58
one that
13:00
next
13:01
marks three that
13:05
Dex
13:07
holer some background color
13:09
equals. And let's put three be read. So plain red
13:16
Aunt, Here it is. And we have finished everything that we have to do.
13:20
Or this one. What we have to do back here is to
13:28
as
13:30
single
13:31
so if you remember, a single is single precision decimal number because this is going to be the civil number.
13:37
And so far we have finished
13:41
this just to remind you one thing. Look at these. If statements they don't have and if, why? Because we have just one thing after then and there is no else. So this is not block. If this is just plain if then structure
13:56
and we don't need to close it because it's in one line.
14:01
Okay, so
14:05
way have done the solution. If we are calculating h
14:09
no,
14:11
let's say these two are not
14:13
numeric. So a and B are not the ones that Eric So let's go to A and h.
14:20
So do this. If we're going to create
14:26
else
14:28
and then
14:31
copy this
14:33
this
14:39
and
14:39
click here
14:43
so else.
14:45
And now if the America Off textbooks one value and in America textbooks, three value. So we're looking now toe a age.
14:58
So if these air numbers, then
15:03
again,
15:03
if textbooks one really or textbooks three value
15:07
aren't smaller than or equal zero, then you can enter. Only positive number is
15:13
if textbooks three will you
15:16
is
15:18
ah different. Now it's we're looking at
15:22
sell two textbooks do. It has to be empty.
15:26
Village has to be empty. And here we have to do one more. Think
15:31
we're going to put another if statement. And we're going to say, if text
15:37
books,
15:41
um
15:43
three
15:45
that will
15:46
you
15:50
is
15:50
smaller or equal
15:54
to text books?
15:56
One.
15:58
No, Well, you
16:02
then we have to display message
16:10
says
16:11
H
16:12
has to
16:15
be
16:15
great there,
16:18
then
16:22
okay,
16:26
because
16:27
hypothesis has to be greater than both sides of a triangle.
16:32
So it's a
16:33
it's
16:34
thing that we also have to check.
16:37
Why?
16:37
Because now
16:41
result is going Toby
16:45
textbooks. Will you three
16:48
squared
16:49
minus
16:52
textbooks, will you? One square.
16:56
So
16:57
if this is smaller than this, we're going to get negative value and error message resulting
17:04
because we're going to try to,
17:07
uh, calculate square root from the negative value.
17:12
And then we're going to put result in box, too, because B is now what we're calculating
17:22
and
17:25
that's it.
17:27
And now
17:30
the third possible result is that we have numbers in
17:33
B and age, so textbooks to in textbooks. Three. We don't have to ask that because we know it's there
17:44
and we're just going to
17:45
else here. Sorry
17:49
else.
17:52
And then we're going to copy these lines.
17:56
Just changed the cell or boxes numbers.
18:02
So
18:07
let's make it
18:11
presentable,
18:15
Shane.
18:17
So
18:18
if
18:19
isn't America So we're looking now a text boxes two and three
18:25
and here
18:26
also have two and three,
18:30
and then we're looking at textbooks one, is it empty or not?
18:36
And if textbooks three value is smaller than very textbooks to,
18:41
then we get here message H has to be greater than be.
18:48
And result is equal off
18:52
square root off
18:56
Xbox three, squared, minus textbooks to squared.
19:02
Envy of putting the result in the books one.
19:07
And now what we have to do is just close this if statements.
19:14
So we have to close this one. Sorry.
19:22
And if
19:26
then we have to close
19:29
this one
19:32
if
19:33
and we have to close the 1st 1
19:37
If
19:40
yeah,
19:41
happens when you don't like well,
19:45
and
19:49
yeah,
19:55
and, uh, that's it. This is our
19:59
coat.
20:00
We're just going to save it.
20:03
Close it,
20:04
and that's it.

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor