Back to another class, of course, about the complete introduction to Microsoft Excel, the use of
VBA or the V for Microsoft Excel.
So in this class, we are still going to talk about Microsoft and different types of macros that exist.
And basically what I want to do today is really putting everything that we have just learned into application
and create a few macros that can be really useful for you guys.
Well, basically, in your use of Excel and at the same time, you are going to learn what exactly you
can do with macros.
So as I said in the past few classes, what does what macros exactly does?
So basically it will automate tasks that normally takes you a few steps to make.
So let's say, for example, we have this table right here.
And I don't know, I want to, for example, take the letter A and simply add color to the letter A
and make it a little bit bigger.
So basically, I will be able to do it in a few steps, but I think also using macros.
So let's just use as an example the letter.
So let's say, for example, for my letter eight, I want to grow the well, the size of my letter A
put it at 14 change, put it in bold change.
The colors will basically make the whole cell yellow and the text.
So right here in the red.
So basically, as you can see, took me more than one step.
And also at this, let's say this one so easily took me way more than one step.
But I will what I can do as well is using a macro to be able to automate all those steps, because if
I want to do the same thing for other letters, all the letters right here, I won't be able because
once again, I can only do this with the letter.
I did it only with the letter A yes, I can select everything.
But let's say, for example, I want to do something different.
Will there be for the letter F, for the letter G, for example?
So it's easier sometimes to use the macros.
So basically what we'll do at first is pretty simple.
We're just going to practice this macro that I just showed you.
So if we try it out, that's pretty simple.
We are going to go here and use relative references because I want to be able to do this everywhere
and we're going to record the macro.
So we'll call it, I don't know, we'll call it a and we'll click on.
OK, so if our macro what we need to do is pretty simple, first of all, we want to change the size
of our letter.
So it was at 14, we're going to put it at 18.
We want to add some color.
We want to add this thing right here and we want to make it in bold.
So that's it.
That's all I wanted to do.
I wanted to add also red color to it.
So that's it then.
The next thing, we just come back in our developer and stop recording our macro.
So right now we have our macro.
That is the letter.
So basically, if I want to do the exact same thing to my other letters, so it's pretty simple, I
simply go here, select my microplane and click on Run.
And as you can see, the same thing will automatically be done with the letter C.
I want to do the same thing for the letter K.
Well, pretty simple.
I can go here and simply run it.
And once again, no, forget one thing you guys can basically.
What if you right now I use the macro right here.
I click and I run it this one.
But you guys can use a shortcut, a shortcut keys.
So basically you can use control and thus control A control B control.
C, you can decide your short shortcut key.
And basically by having a shortcut key if you need to do this in in many places, instead of having
to do all the five steps, we can just run the shortcut.
So that's for all the basics.
So just put in bringing it back to normal, taking away the caller.
Here we go.
All right.
So right now, everything is fine.
So what we're going to do right now, we are going basically to practice everything that we have just
learned in the past few classes.
So how we are going to do this.
So I propose you to create a small table that looks something like this.
So pretty simple, will have later letters, will have dates, will have a percentage, will have a
number.
And we are also going to do a test using the if function.
So basically, if you guys are not familiar with the function, it's not a big deal because it's not
part of the course.
But I'm going to show it to you well, in the scores as well.
So you learn a little bit more.
So pretty simple.
First thing as you guys do is simply write down all the letters of the alphabet.
So basically, ABC, until set in the dates part, what we are going to do, we are going to generate
a random number.
So we are going to write down with you random functions here in between and we are going to generate
the random number between one and let's say thirty thousand.
So why exactly thirty thousand.
So you'll see a little bit later in this case right here.
So we have percentage pretty simple.
We are going to use the random function.
So basically it's going to be rent in just the read function.
So basically it's going to generate a number between zero and one.
And finally, for the number, we can just generate a random number.
So you're run between and around.
Between here we go, we will generate a random random number between one and twenty five, let's say
one in five.
It's going to be better.
All right.
So right now, we have everything that we need.
There we go.
All right, great.
So right now that we have everything that we need, we'll do just select everything and just drag it
until down there.
So we have our dates, we have our percentage.
We have our numbers.
All right.
So next step.
Let's say, for example, we are at the date right here.
So it doesn't make sense.
It's just a number.
And let's say we need to well, we need to convert some of those dates into dates and we want to change
also their size and everything.
So how exactly we can do this?
So pretty simple.
We're going to use it on one of those dates to be able to change everything about it.
So pretty simple to change the date of this element, what steps we need to take.
So pretty simple.
We just need to go here in general and simply select the date.
So basically, it's going to be a long date.
And we if we open it up like this, as you can see, it's going to be Sunday, October 6th.
Nineteen twenty nine.
Each time you refreshed, the date will change, which is pretty cool.
All right.
So basically what I do is not applying it to everything that we have here, but just to a few of those
dates, a few dates that we select, we want to add, we want to have a complete date for for just a
few of those.
So not only this, we also want to add color to it.
So let's say we want to add the yellow color.
We want to make it bold, and we want to add this type of table.
So something that is like this, so pretty simple.
Each time we refresh it, it's going to be it's going to change automatically.
So how exactly do we do this?
So as you guys saw, we had a few steps that we need to accomplish before achieving this.
So pretty simple.
First of all, we needed to change the date.
Then we need to add a color.
Then we need to add the bold.
And then if you guys want to change also the size of the text, you can do it as well.
So we'll try it out.
So basically, if you guys wanted to do this well on some of those dates, it's going to take you five
steps each time, which takes a lot of time.
This is why we're going to create the micro.
So this is exactly what we're going to do right now.
So pretty simple.
We're going to take this date right here.
All right, we're going to bring it to general, right?
So let's start.
All right, so first step that we want to do is simply come back to our go back to our developer tab
and use relative references.
Then we are going to record our Macroom.
So we're clicking here and here.
We are going to add.
So for those of you who want to try this shortcut, you can add a shortcut.
So basically, if you want to click control A.E able to generate the macro, you can do it as well,
just like for practice purposes.
I'm not going to do it personally, but if you guys want to do it, you can do it.
So we'll call this micro date and then click on.
OK,
go.
They're.
OK, we'll try again, right?
We'll keep it back for twenty seven.
It's going to be easier.
All right.
So basically what exactly we need to do at first.
So we come back here.
First step will be changing the type of our text to basically hear what we need.
We need a date.
So basically a long date.
We're going to select this one right here.
So we have our long date.
Next step we want to do is make it bold.
So we'll click on it, then make the size of our text a little bit bigger, add some color to it and
that's it.
So basically, everything works perfectly right now.
So we have everything that we if you guys want to add a bit of coloration through your text, you can
do it as well.
So when it's all done, you just stop recording your macro.
So we'll just make it bigger to be able to see it.
So here we go.
We have our date right here.
We forgot one step, which is adding this thing right here.
But once again, it's not a big deal.
So right now, let's say I want to run this macro right here.
So pretty simple.
What I will do is simply go in here and select my macro, which would be macro twenty seven, and I'll
just run it and see everything works perfectly fine.
So I'm coming back here and I run it.
So as you can see, everything is perfect, so everything works fine and I can do it whatever I want.
All right.
So next thing we're going back to we coming to our percentages right here.
So what I want to do in this case is pretty simple.
So we want to change this some of those into percentages.
So basically, we want to generate we want to have this case, for example, in the format of a percentage
of basically zero point nine nine five, it's going to be ninety nine point five percent.
And this is exactly what we want to do.
So basically to be able to do this, it's pretty simple.
We simply need to click right here.
So let's say you want to do the same thing, but instead of the yellow color, we want to make it green.
So basically we're going to use the green color instead.
So what we're going to do is pretty much the same thing.
We're going to create Imakura for our well for some of those elements.
So we'll come back to our developer tab.
We're going to use a reference because once again, we want to do is use our macros everywhere.
So pretty simple.
And then what we're going to do, we are going to record our macro and we'll call it for something.
Just click on, OK?
And then what I do is simply create our macros.
So first step is pretty simple, is putting it into percentage so we can click on the button right here
or just select the percentage there.
So here it is.
Then the next step is pretty simple, changing the color.
So putting it into green, then next step, adding this thing right here, making the text bold and
maybe changing the size of urtext.
So here we go.
We have all our macro.
So we're coming back here.
We stop recording and we can try our micro.
So let's try it right there.
Pretty simple how exactly we run our micro.
So we just go in our macros, we have our percentage and we run it.
Those you can see everything works fine.
So if I run my percentage micro right here, it's going to look like this.
If I run my data micro, which in my case is micro twenty seven, it's going to become yellow and with
text if I try it out, as you can see it's yellow with able text.
But we have a problem because once again it works with data.
So basically it's going to generate.
That doesn't make sense.
So we'll run our percentage for you.
All right, great.
So let's thing that I want to try out is creating a function instead inside of a macro.
So basically this is exactly what we are going to do.
So here we are going to use an if function and what exactly you want to do.
So basically each time we refresh the numbers here will change.
So when I create a really simple if function, so what exactly it does.
So if we read this function is pretty simple.
So if in this case each five, which is this case right here, is greater than 20, I want myself to
be.
Yes.
Otherwise I want my self to be.
No.
So it's pretty simple to understand and pretty straight to the point.
And let's say I want to test some of my skills.
So what I can do is basically rewrite the whole function for each cell that I want to test.
But I don't want to do this.
What I want to do is pretty simple.
I want to be able to do it in one step.
Yes, I can copy and paste once again for the purpose of this quiz.
I want to use macros, so this is exactly what I will do.
So we'll start with the first step right here.
It bothers me a little bit.
So basically we'll start with the we'll do it right there.
So pretty simple.
What I want to do right now is simply using a relative reference at first, then recording macro and
we'll call it test one.
So pretty simple right now, what I will do is simply trying what I want to do, so I'll write down
equal and then I write down my function.
So if in this case.
This function right here is greater than 20.
I want Excel to return.
Yes, it's a really important not forget those parenthesis inside of your yes.
Otherwise, I want Excel to return.
No.
Once again, pretty simple, and then I can run everything, so in this case, it's yes, because it's
greater than 20.
So that's it.
I have my my career ready.
I can stop recording then.
What I want to do is simply run my crew at another place.
So that's one that's right there.
I can run it.
As you can see, it works pretty fine.
So everything works perfectly.
And also, since it's a relative reference, it will automatically do the test for DLH 14.
It's not going to do this test for the cell that is right here, which is pretty cool.
So I can run my test anywhere.
I just can see it's pretty simple to understand and pretty simple.
Well, I'm not talking about the functions, but I'm talking about the MacRobert.
It's pretty simple to another tip that I can give you guys when you are working with macros, when you're
recording your Michu, don't click everywhere, so don't click everywhere.
What's going to happen is that all those things will be recorded as well.
So we will have a lot of chances to have bugs or errors and set inside of your macros.
This is that usually that what's happened, what happens when people click everywhere when they're recording
their macros.
So go straight to the point.
So basically you select you click only on the cells that you guys will use.
So basically that's the simplest way to not have errors when you guys are working with macros.
So right now, basically, this class was really for practicing everything that we have just learned.
And to also show you what you guys can do with macros and how you can work with macros on a day to day
basis when you guys are working with Excel.
So that's at first class guys insu on our next class.