Hello, guys, and welcome back to another class, of course, about the complete introduction to VBA
with Excel.
So in the best class, we talked about a few things that you guys can do with macros.
And in today's class, what we are going to learn is how to use macros in multitasking.
So basically, you will instead of just having one step instead of your micro, we are going to practice
the use of macros to be able to make more than one calculation.
So basically so basically what we are going to do to be able to do this is pretty simple.
So what I suggest you do is, first of all, just write down letters.
So as a root right here, then you can use the read function, the random function to be able to generate
a random number between one and thirty thousand, like with it in the past class.
And finally, we will have a random percentage that will be generated.
And once again, this percentage will be between zero and one.
So you can use the round function to be able to answer.
So let's say I have this database that I receive each and every day.
And what I want to calculate is what I want to have is, first of all, I want to have the percentage
like the real percentage.
So here I want to have seven, eight percent, for example, and I want to have the amount of this percentage.
Then I want to have the percentage that is left and I want to have the amount of that percentage.
So it sounds a little bit complicated, but we're going to do it together and you'll see it's going
to be pretty simple.
So all this will be stored inside of a macro.
So instead of having to do all those steps each and every time, we will be able to do it well by just
using one macro.
So it's going to be pretty interesting what we are going to do.
So take a few moments just to create your table so we can post the video.
And when you guys are done, we can start it at.
All right, so pretty simple, what we're going to do at first is go to the developer tab and want to
do at first is pretty simple.
We want to have this percentage right here.
So we are going to name our our cells at first.
So if you have first.
Percentage.
Then we will have right here the first number, then percentage left
and number with.
Great.
So first of all, how exactly do we find the person so we'll start by recording democracy.
As always, we're going to use a relative reference because we want to be able to work with our macro
everywhere.
So really important.
Start by clicking on the case right here, because this is where we are going to start.
Then we're going to record our macro will call it, let's say, I don't know.
So we'll call it practice here.
So if you guys want to add a shortcut, keep it's up to you.
Do it.
So just click on OK when it's already right.
So first step, pretty simple.
What we want to do at first is generate the percentage.
So the real percentage.
How exactly do we do this?
Pretty simple.
We're just going to click equal and we want to have this number right here.
So this is the first step.
So when we have this number, next thing to do is simply click on the percentage.
So here it was done automatically.
So if it's not done automatically, for example, you have it as a general number we want to do is simply
click on the percentage.
Then next step is calculating the first number right there.
So what exactly would be the first number?
So pretty simple.
Just click click on the equal button.
And basically the first number is simply this set percentage multiplied by this number right here.
So this would be our phone number.
So we are just going to multiply this number.
So this percentage by this number right there.
And here we go.
So right now it's in percentage.
We're just going to put it into general.
So basically 40 percent of this number will be this number right there.
Next step is calculating the percentage left, so pretty simple how exactly we calculate this, it's
simply going to be one minus this percentage.
And finally, the last step is simply calculating the number that is left so we can calculate it in
two ways, so we can simply multiply this percentage by this number or we can just subtract this number
by this number.
So you decide how you want to do it.
I'm going to go for a multiplication.
So pretty simple.
I'm going to multiply this number.
By this number right there.
All right, once again, really important to put it in general.
So I have what I want right now.
So basically, I have my first number.
That is twenty nine percent of this number right here and it's there.
My second number is seventy one percent of this number right here and it's right there.
So what I want to do right now is simply put, everything in to this case.
So those cases right there.
Right.
And when I'm all done, it's pretty simple.
I can just stop the recording process, so I'll stop recording.
All right, next step, what I want to do right now is simply generating it everywhere I need it.
So let's say, for example, I want to have this Mike.
I want to generate this Maku right there.
So I'm going to go to my macro's and just run my practice micro.
As you can see, it works perfectly fine.
And each time that I refresh the page, it will automatically refresh the answers.
It's pretty simple to do and to understand.
So same thing right here and we can run it.
And once again, if I try to run my macro, well, it works pretty fine.
All right.
So right now that you guys have this, as you can see, this is exactly how well it's possible to basically
make a multi-step macro.
It's possible to make a multi calculation's micro into just one macro.
All right.
What we can do right now is pretty simple.
So I want to do another practice with you guys.
And basically we are going to use that if function to do so.
So basically, we're going to do pretty much the same thing that we did in the first.
So what exactly we're going to do?
So pretty simple.
We're going to use those numbers right there.
And what exactly we are going to do, we are going to add a function to it.
So we're simply going to write down.
So if this number is greater than this number, we're going to return?
Yes.
If not, we're going to return, no.
So something that is pretty simple to do.
And so we'll try it out.
So we'll just write down.
Right.
So here we'll have our test.
This would be the test step.
And here we are going to record our other macro.
So just in.
So, first of all, we're going to use the relative reference on our test and then we're going to record
our macro.
So pretty simple.
We'll call it practice to.
All right, so let's do it.
So pretty simple, we'll start articulation by writing down the if function and when what exactly we
want.
Well, first thing inside of our functional logical test and here the logical test is pretty simple.
We want to know if this number is greater than this number.
So basically, if this number is greater than this number, we want Excel to return.
Yes.
Otherwise we want Excel to return.
No, no.
You can see it's pretty simple.
And if we run it, it works pretty fine.
So it's yes, because this number is greater than this number.
So we have our Macoute that is ready and waiting fine.
So we can try to run it pretty much anywhere we want so we can, for example, go here and run on my
crew right there.
And it's going to be no, if we run it in a place where there is no test that has been done, it's going
to run anyways.
So we can, for example, run it right here.
And as you can see, it's going to be no, because once again, it does the same calculation.
So basically this number is not greater than this number because zero is not greater than zero.
So it works pretty fine.
So finally, what exactly we can do is pretty simple.
We can try to put those two macros together.
So it's possible to use one.
Well, it's possible to have two macro's to do it, but we can also put those two macros together.
So this is exactly what we are going to do right now.
So pretty simple.
As I said, the first the first step will be simply calculating the percentage.
Then we're going to find out the first numbers simply by multiplying this number, by this number.
And then we're going to calculate the percentage left, the number, the number that is left, and finally
we'll do the logical test.
So if you guys are ready, let's do it.
So pretty simple.
Let's go in our micro recorder.
We are going to use a relative reference to do so.
And we're going to record our macro will call it final practice.
And we can run it, right?
So, as I said, the first step is pretty simple is simply finding out the first number right there.
So we want to be equal to this one right here.
This number right there.
So it's going to be twenty seven percent.
It makes sense then.
Same thing.
We want to multiply our twenty seven percent by this number right there.
So we are going to do so.
So it will be equal to twenty seven percent multiplied by this number right here.
Great.
It works pretty fine.
But we have one problem here.
It's in percentage that we are going to put it in general.
Then next step, calculating the percentage left.
So pretty simple how exactly we do this.
It's going to be one minus this percentage right here and we can run it.
And finally, the last step of the calculation will simply be calculating what basically calculating
the number that is left.
So we are going to multiply this 70 percent by this number right here.
So pretty simple.
It's going to be equal to the 70 percent.
Multiplied by this number right there, great, and what I want to do now is simply put everything into
next, we are going to add those cases so we have our cases right here.
And finally, the last step is writing down our logical test.
So we're going to hear and write down our logical test.
So it's pretty simple.
We're going to write down if in this case, this number is greater than this number we want to return.
Yes.
Otherwise we want to return.
No.
And then once again, we can run our logical test and we can even at this little thing right here.
So right now we should have our all our full my crew that is ready and should work pretty fine.
So we'll start with the recording process and we will test our macro.
So right now, our macro, what exactly do we have?
We have our calculations right here, this declaration right there, this calculation, this calculation,
and finally, the logical test.
So this is exactly what we are going to do.
We're going to run our micro.
So, as you can see, it works pretty fine.
We can even test it.
So you can see everything works perfectly.
So right now, what we have done is basically what we have in the built.
In both cases, what we have done is we have used multiple steps to be able to achieve a certain task.
But what I tried to show you in this class, pretty simple, is that you guys can basically put all
this together inside of one macro.
So it works pretty fine.
So basically, let's say you guys want to use this macro somewhere right here.
So I'm just going to take all this table copied and pasted, I don't know, test it.
Let's say I passed that right there.
So I have my all my full database right here and I want to generate my macros.
So basically, this is exactly what I will do.
I will generate my macros somewhere right here.
Well, it's possible for me to do this.
So pretty simple.
Just go right here.
I'll go inside of my macro and I can run my microphone, as you can see, works pretty fine.
And basically it's going to use the numbers that are right there.
So everything works perfectly.
So as you can see, macros are something that is really, really useful right now, we did an example
that, well, maybe doesn't make that much sense because we just did some calculations.
But you can apply this in your well, in your everyday life.
And if you were in management or any other business field, you'll see it's really, really important.
What the microbes are really, really important can be really, really useful.
So that's it for us.
Thanks, guys, and see you in our next class.