### Intermediate Excel

Course
Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

### Video Transcription

00:01
Okay, so this exercise are this example We're building form that will allow user to type.
00:09
They will have three fields for a B and H, and you will feel just two of them. You really leave the third blank.
00:17
You put the numbers which represent the lens offsides, whichever you want. They'd be Abie on or age
00:24
and ah, Then it will calculate the 3rd 1
00:30
So, um,
00:33
it should avoid any errors or mistakes. And
00:38
ah, that's ah pretty much it.
00:42
So, um
00:43
ah, let's go to the form creation. So we have this.
00:50
There is a tool box and we have pretty much finished this form. It doesn't have to be any prettier. It can be
00:57
modified. You can you can add some some background. You can add pictures you can at whatever you want.
01:06
But
01:07
let's, uh, let's leave it like this because this is what we need
01:12
so far. And now we're going to create for codes that runs this form.
01:19
So it's ah,
01:22
save you code.
01:23
And now we see the cold for
01:27
this.
01:30
He was a form,
01:33
and, uh,
01:34
it's nothing there because what we actually want
01:38
is to create cold
01:41
for this
01:42
button
01:44
because whatever happens in this form will happen after you click on calculate button.
01:52
So we're going to click here your code, and we get this and he says, Private sub command button, one click
02:00
and, uh, here, we're going to create code for this.
02:06
So, um,
02:07
with a look at the logic
02:09
behind this,
02:12
first we have to check
02:14
if the numbers entered our numbers.
02:19
Second, we have to check if there are actually two fields
02:23
filled and not one or zero or three.
02:28
So we have to see if
02:30
these are
02:30
empty or not empty.
02:34
And we also have to check give these are numbers and not something else.
02:39
And, uh
02:42
then
02:43
based on rich fields, have bean
02:47
entered. So which wheels have numbers?
02:52
We're going to decide what is the third field field that we need to our textbooks that we need to fill with the result
03:00
and then to displayed so that it's clear that the result is there now,
03:06
um,
03:07
uh,
03:08
my idea for this form is that result will be displayed within this form so we could use a message box and just put the result there.
03:17
But it would be nicer if the results would just pop up in the empty
03:23
textbooks or field.
03:25
So
03:25
let's, uh, let's start for from very beginning.
03:31
And, uh,
03:35
I'm not going to put any
03:37
Ah, numbers here in dimensions are dim. Statements.
03:44
Uh, I'm going to do that at the end, So I'll see what I will need off
03:49
are these numbers and then I'm just going toe
03:52
put the things at the end.
03:54
So
03:55
first of all, I would need some kind of counter
03:59
because I'm going to
04:00
check how many fields
04:03
to have numbers in it.
04:06
So I'm going to call it, see? And I say, OK, the beginning of this, it zero.
04:14
Then
04:15
I'm going to check each field
04:19
if it's no Merrick or not. So I'm going to say
04:25
if
04:26
and then I'm going to use the function off. If you remember it from infra function is numeric
04:33
off
04:35
and then
04:38
the value off the first textbooks is text
04:43
box
04:44
one
04:46
dot
04:47
well, you
04:49
we're going to close the brackets
04:53
and if that is the case, then C equals C plus one.
05:01
So in that case we have,
05:04
we have added the count increased discounter by one.
05:10
Now let's ah, just copy Paste this
05:15
and
05:17
let's do it for textbooks to
05:21
and then let's do it for textbooks. Three.
05:28
So at this point,
05:30
we have created
05:33
the
05:36
called calculation
05:39
that will tell us how many fields have been filled with numbers.
05:44
And, of course, now wait have done it. We have to
05:47
do dim see
05:49
as
05:50
integer,
05:53
so because it's not going to be any kind off,
05:57
Ah, decimal number is going to be just 123
06:01
So
06:02
this is enough. And so far we have
06:06
successfully started to do this. And we can test this by pressing by adding command line.
06:16
And this is what I suggest to you to do it in the beginning. So if you do something, just test your
06:26
message books
06:28
and then see
06:30
and then this sub.
06:31
So let's save it
06:35
and let's close it.
06:40
And in next video,
06:43
you're going to see how we test it.
06:46
So we're going to create a link from this
06:49
window here.
06:50
Two.
06:51
The
06:54
form that we have just created

### 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

Milan Cetic
IT Security Consultant
Instructor