4.5 Using Complex Macros with Real-Life Problems Part 1
8 hours 33 minutes
at the end of this module in this. Listen, I'm going to show you some examples off complex Macron's with
idea to solve really life problems or something that looks like, really like problems. So we are not going to beat around the bush, and we're going to go directly.
the example. So here
um, worksheet, we have some kind of
Ah, sheet where they can answer to ABC questions on a quiz.
And at the end, when they click on submit results,
they get just information if they have passed or not.
So they don't get information about the percentage.
Ah, they had They will get that later. Why? Because if if they get this percentage, they can play with it until they pass. Because this is a B. C. They don't need to put any car, really answer.
So we're going to close this
and look at the answers. So here are the answers to that quiz. So they're 20 questions,
and we want first to make
Aah! Answers calculated.
And then we want this answer sheet hidden
Aah students cannot look at the answers.
Also, you notice here that there is ah,
word test in a cell. And one This is actually a password that I have typed in
That will be ah, needed to unhygienic sheet for, ah, teacher or professor if they want to,
uh, change the answers to the quiz for
next group or next semester or whatever.
So Ah, I'm keeping this Ah ah
password in this sheet because it's will be hidden so students won't be able to find it in no way because they wouldn't be ableto excessive
Now we can, of course, hide this sheet.
But then anybody can unhygienic it,
just clicking right. Click on any tab and clicking the answer sheet.
So this is not good. We want to hide it
So let's first look att. The submit results
and ah, this macro
is here. It's called Submit
no small under
and it's very simple.
So we are defining I as a counter is integer
and we are the defining. Some off answers total some off correct answers as interview as well.
So we're seeing some of some of correct answers. Zero and then we go from
2 to 21. Why?
Because in the
answer started row two
we ask if
So it can be a two. A three. A four up to a 21
Two sheets answers that range.
again, A and I.
And why? Because if we look at the answers,
I have also
but hear answers the quiz. So to correspond. Row to Crispin's in Answers correspond to roto in,
um but student gave us an answer and so on and so on.
if that is correct, then we're adding one,
some off correct answers,
or else we do nothing and then re close. And if technically else could have been omitted, because then we would have, if then
structure. And that's completely legal.
80% is needed to pass this test,
and that's 15 out of 20
Sorry, 75% is needed to pass this test so it's 15 out of 20.
And so if some off answers is equal
or greater than 15
then the message books is past
else. Message books. It's failed and if ends up. So this is it. Very simple
macro that will simply count the correct answers. So if we look in the current sheet
ah, look, it's for the correct answers in
and that's it. So we have calculated that
now we want to
look a TTE
how to hide this answers
And if you look here,
there is no
micro for hiding it. I have written it, but it's not visible.
I know it's name and it's called Hide
and I want to edit it
and it has just one line.
But look at this. This is very important. It has private
before sub, and then it's called high Cheat.
So what it does
looks at sheets.
Pyramids were visible
and the sheets we are looking to set to the pyramid, therefore, is answers, of course.
And we put the value off Excel very hidden.
So this value
well, tell us
it will be hidden in a way that it cannot be hidden
by clicking right clicking here
doing on Hyde. We don't have this because we don't have any human sheets at this moment.
So if you go back to visual basic,
it's very simple
And if we execute it,
So if we execute
this mackerel? Let me just close this.
So if we go to this one,
go macro and we type hide
and we see run.
This one is human
and great Click on height is not possible
we want to unhygienic it.
and we click on this module here. Now this one has a slightly different name because I didn't enter it from this dialogue where I typed the new name and clicked on create I went to directly to visual basic and open the new module.
So let's first look Att This,
uh, my crew
it goes to this user form
and it's it's it.
And the user form
And if you click here and say show, coat
So basically this one starts this user form
and let me just execute it first so you can see what it does.
So if I click on submit results
and if I execute the macro here,
they say ran.
I get this user form So again I have a dialogue box butts now interactive that log books
and I typed test
and I think okay,
it doesn't work. I have to click physically here and it gets on him.
I can now change this and then I can use the other
passwords. If I found out that somebody has found that the password this So let's go and look. Att The code
and it's very simple. So if text box one value So this is textbooks. One
equals this world book that Sheets answers the strange and one
well, you. So we're looking at the value off cell and one in sheet answers, although it's over the hidden so it's not visible.
we go to sheets
answers visible equals true. So remember, we
plays their excel very human, which is a special code to hide it completely.
Otherwise, we could just get the message books wrong
to end this,
and we do unload user form, so we remove it from screen so that dialogue box with the password will be gone as you have seen.
So this is very simple. I won't go at this point into how to create this user form. We'll address that in module eight.
So this is all
about how this
macro is work and how to shut hide the sheet, so it's not that complicated
and it can be done with couple lines of coat,
that concludes this lesson.