Hello and welcome back to another class of our course about the complete introduction to VBA with Microsoft
Excel.
So in this class, what I want to do is basically practicing everything that we just saw and really
creating a formula.
Well, not a formula, but really creating a macro from scratch with all the knowledge that we just
have.
So what I want to do today is pretty simple.
I want to practice a little bit more what we have, what we have learned in the past few classes.
This is exactly what we are going to do.
So what I want to create is really a simple macro.
So let me just show you what I was thinking about.
So my goal here is not to make something complicated, but really a basic macro that we will that we
can use.
So we are going to generate eight mahu in which we are going to have a random function like we always
do.
So basically, step one, what I want to have is a name.
Then I want to have an age, then I want to have a number.
And basically this number will be somewhere between one and let's say 30 or I'll decide between which
number and which number.
Then for the name I want to write down eight or any other name.
I can decide it for the age.
I want to have a random number.
So we are going to use this formula, which would be around between, as we always do, let's say in
this case is going to be between 20 and 30.
And finally, for the number, I want to have pretty much the same thing, but instead having a number
that will be generated between zero and 30.
So she will at the end.
What I want to have is just something like this.
But if I want to if I want to put all this inside of a Marquel, it's possible I can put it inside of
a macro.
But let's say I want to write it by myself.
So in this case, I'm writing all this by myself from scratch.
Is it possible?
Well, the answer is yes.
And my goal today is really to practice writing all this.
And this is exactly what will do.
So what I'll ask you guys to do is pretty simple.
Just go in your developer type right there and you can click on your visual basic element right there.
And you will be redirected to this page right here.
So, as always, what we are going to do, we are going to create another module we have here Module
one, and what we are going to do, we are simply going to create another module.
So we are going to go into insur and we are going to insert another module.
So just to be sure, we're in module two.
All right.
So what's going to happen right now?
We have our module and we are able to create whatever we want inside of this module.
So basically we can create our macro.
Pretty simple.
So just start by deleting all this and I'll start I'll start writing down my macro.
So sure is.
So pretty simple, how exactly do we start our Michael?
So, as I explained, we need to start our macro with the sub procedure and how exactly we can start
with this.
Well, it's pretty simple.
We are going to insert once again a procedure right there.
So here you guys can select the type of procedure you want.
If you want to create your own function, you will select function for properly.
It's pretty much the same thing when I do want to create a macro that will use this procedure.
And in this in this case, we are going to give a name to our micro.
So let's call it I don't know how we can call it Final Micro.
So here we go.
And we just click on.
Okay.
So as you can see, as we have our public sub and sub, so basically we have our sub procedures that
have been treated.
So our micro have been created.
And from here, what we can do, we can start writing down our code.
All right.
So as I explained, basically the first step of what exactly we want to do is pretty simple.
We want to write down a name.
So what exactly we are going to use for this?
It's pretty simple.
Well, we are going to use some elements and I'm going to describe them step by step.
So first of all, what to do?
We want to select the active cell that we are working on.
So basically this cell right there and how exactly we are going to do this.
So it's pretty simple.
We are going to write it down.
So we are going to start writing down active.
So we'll just give a few pointers and we'll write it down right there.
So it's going to be active.
So and then what exactly we are going to use as a comment, what to do in this active cell, we want
to write down a name.
So in this case, how exactly will write down your name?
So we are going to use another element.
So in this case is going to be formula.
Here we go.
So we have it right there.
Ah, one, you want to go, we have it right here and the final step is what exactly we want to write
down so you can decide whatever we want to write down.
So if I want to write down a name, I can write the name.
I want to write down anything else.
Well, I can do it so I can decide whatever I want to write down.
Just change the language of my keyboard.
All right, so here's what I want to write down.
I want to write down a name so we want to have a name for our person.
All right.
So now that we have a name, next step, what exactly we want to do.
So we want to move to another cell.
So basically, we want to move from this cell to this cell right there.
So how exactly we will move.
So there is another way that we are going, what we are going to use active cells once again.
And in this case, we want to so basically to be able to we're going to use the offset function.
So pretty simple how exactly we write this down.
So we are going to copy the active cell that we have right here and what exactly we want to we want
to do right now.
We want to move from this cell to this cell right there in the range eight one, which is in all this
range.
And this is the same set.
The second step is really moving.
So we are going to do it right now.
So we are going to use the offset function.
So upset.
And in this case, we are moving from one zero one.
So we're moving from zero and one on the side, then in what range exactly we are going to move, we
are going to move in the range in this case, eight one, which means our table.
So eight one.
Here we go, and we are going to select this subcircuit.
So once we have done this step, what what exactly we want to do?
It's pretty simple.
We want to write down each.
So right now we have our second line of code and now it's time to basically write down the salient element.
So how exactly we are going to write down the second element?
Well, it's pretty simple.
We are going to reuse this formula right here.
Which is active sell formula or one seat one, so we'll just copy everything and we'll pass that right
there so we can run it and write down each right here.
So pretty simple.
Everything works fine.
So if we want to try just this piece of the formula, we can do it.
So we'll have our name and our age, basically, this is exactly what we have wrote down.
So here we go.
We'll come back right there soon.
Until now, we can see that everything works fine.
All right.
So right now that we have our name and are each, the next step will once again be moving another cell.
So we have our name right there are each right there and want to generate a random number right here.
So this is exactly what we are going to do right now.
We are going to generate a random number, but before generating well, before writing the number,
what we need to do is pretty simple.
We need to move up another step.
So basically another cell.
Sorry.
So what we'll do is simply copy this formula right there, because this would be the next step of what
exactly we need to do.
So we'll jump off one step so we can copy this formula because it's pretty much the same thing.
If you want to jump from two cases just right down to if you want to go back to cases, we will write
down minus two.
So in this case, want to jump off just one case, so we'll write down one.
All right.
Next step we want to do want to reuse this formula, but instead of each one to generate will be number.
So we'll do it right there.
So here we go.
So instead of each we want to generate.
No.
All right, so right now that we have all this, what exactly?
So we have down name, we have rolled down each we have Rudan number now.
We want to go back one, two steps and go under one step.
So how exactly we are going to write down this.
So pretty simple.
We are going to reduce the active step right there to move while the active cell, the offset function
to be able to move inside of our well, inside of our micro.
So let me just run the macro and show you what it looks like until now.
So, as I explained, we have created the name, we have created the age, we have created number.
So now it's time to move.
So we're moving from here to there to there and down of one.
So we are going to use this function right there, which is active cell offset zero.
Well, in this case, it's not going to be zero.
One is going to be something else.
So copy and paste that right there.
So, as I said, we need to move back up to cells and down one cell.
So here is to move up or down the front.
To move up is going to be minus.
If one is done down, it's going to be plus.
So in this case, we are moving one down and minus two in the back.
So we're moving minus two to minus one, minus two and down of one to four to go up.
One is going to be minus one instead.
So here we go.
Right now, we have everything that we need.
Next step, what exactly we need to do.
So pretty simple in this cell.
Now that we moved to the cell, we want to write down a.
So in this case, the name will be eight.
So what we'll do just once again, we use this formula right there.
So active cell, the formula or one C1.
And in this case, we can give a name to our person so we can give it any name that we want.
And in this case, let's give it a.
So as I said, it's going to be then the next step is pretty simple.
Once again, we need to move up another cell.
So right now we are right there.
Want to do want to move from here to there.
So how exactly will move?
It's pretty simple.
We are going to reuse another formula.
So basically we're going to use this formula right there.
So since we're moving of just one cell on the right next step, it's going to be writing down another
formula.
So in this case, we want to write down the formula, read between.
So how exactly we write down this formula?
Well, it's pretty simple.
Well, we are going to use the same formula that we use right there, which is Formula or one C1.
So this function right there and instead of writing down something, well, writing down a name or just
a letter or a number, what we're going to write down, we're going to write down or function.
So it's going to be equal to what exactly?
It's going to be equal to round between.
So write it in capital letters just to remember that we're writing down a function right there.
So we're going to generate a random number that will be between.
So just a mistake right there.
So, Rend.
Between.
And in this case, want to generate a random number that will be between 10 and 20, so they said something
that is pretty simple.
And now we have created what we have created until now, so we have created our macro until the age.
So what we can do, we can basically just try it out.
So we'll go right there, will delete everything, and we'll try out our macro to see if until now it
works fine before writing down the last step or if we have a certain problem with our macros, we'll
come back in our visual basic will run pretty much everything.
So we'll come back right there and we'll run everything.
And as we can see, everything works fine.
So finally, the last step is pretty much the same, the same thing as right there.
We're going we're moving of one cell.
And what exactly we're going to do right now, well, it's pretty simple, we're simply going to change
the numbers that are inside the realm between some go right here.
Instead of being between 10 and 20, we are going between.
We're going to be between zero and third.
So right now, we have all our function ready.
Well, our Maku ready so we can run it to see if it works fine.
Those can see everything works fine.
Now, let's say I want to put some of my numbers.
Well, some of my elements in capital letters and other elements are well, not in capital letters but
in bold, for example, or any other things.
Well I can do it.
So let's say, for example, instead of name.
Right.
I want to put my name in bold.
So how exactly out will do it?
So pretty simple.
So first thing that we want to do is simply come back right there and we are going to add another element.
So in this case, we are going to use the fund.
So basically we're going to use the selection function and we're going to use fund and simply use the
board, as we learned in the first class.
So this is exactly what we're going to do.
So we'll just write down the selection.
And in this case, what we want to do is simply using selection that in this case, what exactly we
are going to do.
So we're coming back and we're using that fund.
So we're using fund and what exactly in the fund we want to use.
We want to use Bolt so we don't want to change nothing right there.
We simply want to change board or make making making it into italic, whatever.
So we're going to simply add the bolt board.
I don't want to play too much with the other stuff.
So pretty simple.
We are coming back to our element right there.
So what what exactly what do we want to write right now?
So we have our selection, so we want to select our fund and then inside of our fund what to do.
We want to make my name bold.
In this case, we have arbo.
And do we want it both?
Well, yes.
So we will write down.
True.
And right now, if you run our function, our Microsoft, so you can see the name is both so pretty
simple.
We'll just put it in capital letters at the end.
So if we run everything, you'll see the name is going to be in vaults.
We'll run it next to it.
Pretty simple.
We can run it right now so can see everything works fine.
So if you guys want to add some details that say, for example, you guys want to change this part right
there, the name will basically you can do it there.
I just change it to put it board.
They can, for example, change change it to add the italic parts will make it bold and italic.
If, for example, I want to change this right there and basically add something else to it, I don't
know, add a type of cases.
I'm able to do it.
If I want to change the color, I'm able to do it as well.
So as long as it's under this set.
So basically I'm working on this.
So then here this line of code says that I'm moving of one cell.
So here at the second cell where I'm writing down each and everything that I want to do with each,
I can write it under under this cell.
So basically, all the modifications that I want to do to this cell, I can write them down right there.
Same thing for no.
So after that, I'm moving of well, I'm moving of two cells in the back, so I'm coming back right
there.
Everything that I want to do as a modification right here, I'm going to write it under this function
right there.
Same thing for all the other functions.
So basically, if I want to make make it bold, I can make it bold if I want to make it italic, for
example.
So I'll just write it down.
So selection that I'm working with font and in this case, I want to make it italic.
Well, I'm it's possible to do it.
Just write down the true.
And I can run my microphone, as you can see, it will change so you can see the name has changed to
my macaron right here because I finished my code, I started right there.
So it's pretty simple.
So if I delete everything and I, I come back to my visual, to visual basic studio and I run it, as
you can see, it works pretty fine.
So as I want it to work, so it's once again pretty cool.
So one other problem, as you can see right there, what what happens is that this true?
Well, the true right here have been applied to all of the elements right there.
So what exactly will need you do?
I will need to add, for example, selection from that board pretty much everywhere and write down Truffaut's
once again, this is the way to write down the code.
You can write it down in other ways.
But once again, this is a way that you guys can write down the code and modify everything that you
guys want to modify inside of it.
So I hope you understood how exactly we have ruled our country right now, so you can see it's not that
complicated to write, to write them.
It's just a question of practice.
So basically, if you want to add some more details to this code that we have right there, you can
do it.
It's up to you.
And once again, as you can see, it's not that complicated.
So it's at first class gets into our next class.