### Intermediate Excel

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

### Video Transcription

00:00
Okay, let's look now at what happens when we click the command button.
00:06
So
00:08
we're looking at
00:10
this
00:12
sub this macro
00:14
and what first thing we do. And you see that I haven't done the dim statement here. It doesn't have to be because thes air just
00:22
plane integer print plane, the string or in number Constance.
00:28
So I'm just doing this so I could ah,
00:32
simplify this line here. So I'm saying row number is sheets, answers cells 11 and look at the here.
00:40
So this is this is number here that that tells which is the active
00:46
Ah, ro. And you see here the answers I have given So
00:51
So the routine that starts the whole thing, that one. When we click, start the tests puts here number three,
00:58
and there they put a different number, but you'll see how this thing develops. It doesn't matter. So
01:04
the call number six you see here has left from the last question.
01:11
And, um
01:14
so Cole number is this cell,
01:18
and then we have three simple questions. So if option button value one is equal true, so it wants quick, then answer is one.
01:25
If it's too, then answers to if it's treated and it's straight. So if which we determine which one of these trees through and then we decide what is the number of the answer. And then we just put in these sheets answers
01:42
in cells, roll number three and
01:46
call him number. The last one was six,
01:49
01:53
this is it. It can be 12 and three. And here it was three.
01:57
And then I'm just typing this me that high. So when you click the command baton and it does all these calculations, it will hide itself so it will remove itself from the screen.
02:10
And, uh, that's it about this form. So this is a ll the cold you need in the form to do this.
02:17
Let's just close this because we're no longer looking at the form.
02:23
And now let's look at first. The module one
02:27
and module one is, uh,
02:30
start test module, which is the most important one.
02:32
It's also where a simple
02:35
So what we're doing is we're saying, for I equals 1 to 5 because we have five questions. So if it would have 20 questions, it would be from my was 1 to 20
02:46
and then we're just telling column is Ah,
02:49
I plus one.
02:53
Ah,
02:53
equals I
02:57
I plus one. Yeah, because it's called Miss my Policeman. But basically, what we do
03:02
is we're saying sheets answers cells
03:07
equals one
03:09
cells 12 So row one cell too.
03:15
Is
03:17
I plus one?
03:19
It could be calm. Yeah,
03:23
but, uh,
03:24
it's okay. So this is
03:27
this one,
03:28
and we're saying that it is I plus one. So we start with column to counter goes from 1 to 5. So it goes like this.
03:38
Uh, we could have set it to be a different one, but it could have been from 2 to 6. But
03:46
it's just much clear because you have 20 questions. You don't have to remember to say 2 to 21. You just put here the numbers so we could have also automated this. So we count the number of cells with the do loop. But
04:01
this is this is just the example.
04:05
And in the questions in cell 11 we are putting too. Why? Because here, if we put two ah, then ah,
04:16
the routine knows to start from two and is goingto be questioning line two then three, then Ford and five and then six because I's going from 1 to 5. This is going from 2 to 6.
04:31
And now if you look a tw what we're doing,
04:35
we're just doing user forms show. And this this unload user form is actually not needed. So I'm going to delete it.
04:44
And I have written the code also so that this con number is also needed. So I'm going to delete it. I'm just showing you what happened when I was developing it
04:53
on. It's important for you to see that you can't, you know, almost never right this cold from the scratch. Correct. So there is always something in the process that
05:06
makes you
05:08
put some lines there that they're not really needed. They're not influencing the functionality off micro, but they make problems. So basically,
05:19
this is it so and then we
05:23
tell next I envy insects. So what is? But this is doing it is calling five times this form and then in the form we have code, which, when we click on submit, will find what the answer is and put it in the answers line here.
05:43
And let's go back to this so that you see this as well. So we can we can save this one for later so you can review it.
05:54
And let's look a module three, which is basically a sub that collects student name.
06:01
So we have dimension. Answer this string and we're saying answer equals input box. Enter your name. So you have seen that input box in which he should be. I answered my name.
06:15
Um,
06:15
so in this box,
06:17
um,
06:18
thes books can also be modified
06:24
because if I start typing here,
06:28
I get the sums of, like, exposition y position, help file contacts, whatever. We don't need that.
06:35
We just need the plane input box. If you want to do that, you can go toe, excel, help, or look the answer online. How to make your input box look different.
06:48
But this is it. So we're setting the counter to zero after that. So we got the name in the in the variable answer,
06:57
and we do a do loop which says, first, we increase the country to one
07:01
and we simply go loop until sheets
07:05
answers. So, in the answer sheet in cell I one which is this one or this one or this one, depending on the
07:15
counters
07:17
value,
07:19
we find the empty cell. So we ran out off filled lines with students answers because this can be done with four by multiple students as well.
07:30
And what we do now is that we put
07:34
ah,
07:36
answer which his name in the cell. So when I was typing this, this line was empty.
07:43
So it counted 12 and then three, and the 3rd 1 was empty. There I was, three at the moment. Eso in cells I
07:51
and remember, when you're using the cells
07:55
command, then it's row first and call next while where you using ranger using the standard X L C five or a three. So it means Cone first and then wrote
08:09
and also after replaced the name here. The second thing we did is that we told the functions later, which is the row in which to put the answer. So basically, when you click on the submit button in the form, it knows that the current row is three.
08:28
So it's, uh
08:31
look at this
08:33
and that's it.
08:33
And ah,
08:35
we did the loop. We found We replace these numbers and sell and we ended sub. So this one just places the name and tells the later functions what would be done. So if student clicks several times on the enter your name,
08:54
uh, button,
08:56
It would put several instances off that student's name here, but this would remain empty because then we would tell the the other Mac Rose that
09:07
this, for example, if you put it twice, it would be distant. The number here would before this line would be empty. So the teacher would know that this student has just clicked on input name and input their name several times more than once.
09:24
So this is, uh this is how this macro works
09:28
and this is pretty much it. So you see, Okay, you can admit crows to hide and on high dances and questions what you can also do. Let's just close this and close this, um, you can also hide these
09:46
so that they are not visible when manipulating the
09:52
answers and questions and so on and so on.
09:56
And you, when you hide everything, get just this one sheet, you can protect everything with passwords. So this stone just clicks the name so you can put some instructions here. Like, for example, one
10:11
click on
10:16
and
10:18
and
10:18
your
10:20
name.
10:22
But, um,
10:24
And
10:24
Hunter,
10:26
you're
10:28
full name in
10:33
the, uh,
10:35
books that
10:39
will. Beer
10:43
is you make
10:46
mist ake
10:48
when and to ring
10:50
your
10:52
name.
10:54
Just repeat
10:58
this
11:01
procedure
11:03
so we'll have the empty line
11:05
and
11:07
yeah,
11:07
click
11:09
on and so on and some And then to the number two is
11:18
when
11:20
you
11:22
Sorry.
11:22
11:26
Click on
11:28
start.
11:31
Yeah.
11:33
First button and
11:37
And serve
11:39
two
11:41
schoen's.
11:41
Bye. So
11:45
thing one
11:46
off, three
11:48
off.
11:56
Um,
11:58
sures.
12:01
Yeah. So
12:07
correct spelling.
12:11
And what also we can do here is that
12:16
we can large this cell.
12:22
We can select the cells to weaken forward them so that we wrap text
12:28
and, uh,
12:31
auras until is left,
12:37
and vertical is center
12:39
on. We did this
12:43
on. We can increase the text size
12:48
so that, uh,
12:50
this will do. We can then
12:54
reduce
12:56
the height of these gums.
13:01
No little so that it looks nicer.
13:05
Yeah, And, uh,
13:09
or whatever you think it's ah is appropriate.
13:13
So we have here a typo,
13:18
but this is it. So this is finished.
13:22
Product will save it.
13:24
And that is end of this example

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