And welcome back to another class, of course, about the complete introduction to VBA with Microsoft
Excel.
This class don't want to practice is also a little bit of programming with that Microsoft Excel.
But what exactly they want to practice is basically in.
Well, moving inside of your Excel document.
So what I want to practice this because it's a huge part of VBA.
In other words, when well, let's say you are creating your macro and you want to move from there to
there to there to there to there will be there is a lot of movement and understanding how exactly all
this movement works.
It's pretty important.
So what I'm proposing to do today is something that is pretty simple.
And we're going to create two basic applications and learn to work with that, some well, some elements.
So basically what we are going to learn that we put will be with that range.
So we are going to learn how to use a range and we also going to learn how to use offsets.
Basically, you guys have a basic level of basic knowledge about it, but what exactly I want to do
is just practice a little bit.
So this week, you guys will get a certain understanding of how that works.
And you will also understand what is the difference between working with a relative reference and not
a will and an absolute reference.
So basically a reference means that it's always going to be generated at the same place and how you
can write a code that will be generated as a relative reference and that will be generated as an absolute
reference.
So let's do it.
So I said, what are we going to do today is just practicing how to move inside of Microsoft Excel when
we are creating our well, when we are creating our macro.
So first thing that I want you guys to do is very simple.
So I want you to create yourself in modules.
So simply go in insert and you can insert a module.
So you can it's very emotional.
So here I already have a module, so I'm not going to insert modules.
And then when it's not what we want to do, this is pretty much starting out our procedure because we
need to generate a sub procedure in this case.
So if we want our micro works or how exactly we can read this, it's pretty simple.
Once again, we're going to insert and we are going to generate a sub procedure.
So we'll go for micro test and we're going to click on OK, so we have our sub procedure.
So for the first part of the course, what I'm going to do is pretty simple.
I want to generate the well, let's say something right there.
So let's say I want to write down step one, then step to the START treaty and step forward and soever.
And basically, this is exactly what I will do.
So let's start basically how exactly can I write down step one right here?
So pretty simple.
It's pretty much the same way that we have done in the past few classes.
So we'll use the active self.
So basically we're going to write down in the act itself and we're going to use formula or once you
want, and write down step one.
So let's write it down.
So it's going to be active self.
So in the active so that we are on we are going to write down.
Or a movie
or one sequel.
That's basically what we have learned, this is the function or the element that we are going to use
to be able to write down our text.
So we have learned this in the past few places.
So when it's done, we'll do we are going to write down the text that we want to write them.
So let's say I want to write down step one in this case or just write down step one.
Good.
So right now I have everything that one just put it in one word.
So if I run my app, my step one is right there.
So pretty simple.
Next thing that I want to do.
Pretty simple.
Let's say I want to make inside of my Excel spreadsheet.
So in the case that I want to know how exactly I can inside of my Excel spreadsheets.
In this case.
Well, here at this particular moment, what's happening is that I want to have a relative reference.
So basically what I will use will use the upset function.
So once again, we are going to start our writing with the same thing.
So we are going to start with Active Cell because we are going to move from our active cell that is
right there and where exactly we want to.
So let's say we want to move.
I don't want to move from three cells, so one to three.
So this is exactly what we are going to write down.
All right.
Don't upset.
So we want to and in this case, we want to move from three steps and then will be arranged to basically
arrange will be all this will be the spreadsheet.
So we're going to use eight one.
So write it down.
Write this to arrange in this case, it will be a one.
And you want to select the cell in which we are going to simply write down, not select go.
But so all right.
So we have everything that we need.
So I know what happened is that we moved from here to one to three.
So you understand the basics of moving inside of your Excel spreadsheet.
Right.
So we have got our second element.
What to do right there is pretty simple.
Let's say we want to write down step two.
So pretty much the same thing.
We are going to write down step two.
So we are selecting our cell at first.
So we are going to write down selection and then what exactly we're going to use.
We are going to use formula or one, see what
they will do and what we'll write down a simple step two.
So if we decide to run our macro right now, what's going to happen is that it will right down step
one there and three cells on the right and right down, step two.
So as you can see, this is exactly what happened.
If I run my macro, it's going to spike from here.
So it will right down step one there from three cells and right down step two on the third cell.
So, again, can see it's pretty simple to understand.
So let's continue practicing it a little bit more.
So let's say I want to move it down.
So right now I want to move it down of three cells.
How exactly do this so pretty simple.
I will use the same formula that is right there.
I will start with my active cell and I will use the offset function in the range, each one.
So I want to move three cells down.
So it's going to be three and then zero.
So just select my function right there because I will use it here.
But right now, instead of moving three steps on the side, I wouldn't three steps down.
So I will write it this way.
All right, so what I will do when what I have.
So right now what I want this function says it's pretty simple.
It says that we are going to move from here to basically there and what exactly we want to do there.
We want to write down step three.
So we're going to reuse this formula right there to write down step three.
So pretty simple.
Here we go.
And here we are going to write down step three.
So basically, you get a little bit at that point.
So if we run out, what's going to happen will have stuck on there one to three.
Step two there.
And one, two, three.
And step three there.
So it will look something like this if that's going to happen.
Step one, step two or step three, which is pretty cool.
All right.
Next thing that we want to do.
So if you guys, for example, if you see here that my text is an italic, it's simply because my self
and it's bold.
It's not because I have written this in the code.
So I'll just select everything that I have there
and I'll take away all the Italian.
So let's come back.
Great.
So right now we have everything that we need.
So last thing that I want to I want to practice that.
Let's say I want to arrange that is on the side as well as Delp.
So right now, let's say I'm somewhere.
I don't know.
Let's just delete all this.
Let's say I am right there and I want to move two steps there, about three steps, then three steps
down.
But now let's say I want to go right there.
So how exactly can I do this?
So pretty simple.
I will simply move two steps on the side and two steps down.
So pretty simple.
Same thing.
If when want to come back for for example, move back.
What I will do, I will simply write down minus the key.
So in this case, let's say we want to move back.
So pretty simple how exactly will write it down.
So we are going to reuse this formula right there.
So the first thing we want to know where exactly we want to.
So let's say we have made three steps down, let's say when I go to steps up.
So we're going to write down minus two and let's say we want to go at five steps on the other side completely.
So it's going to be minus five in this case.
And why exactly?
We want to write we want to write less steps.
So we are going to reuse this formula right there to write down less step, less step.
And what we can do right now is simply everything to be sure that it works fine so we can see everything
works fine if we check out our code.
So the first step was simply writing down step one.
We have it there.
Then the second step is moving two steps on the side.
So I'll just do it the right way so you guys understand it a little bit more.
So let's try it out.
So first step is writing down step one.
So we've done the same step will be moving two steps on the side.
This is exactly what happening, what's happening right there.
So we have two steps on the side.
Next step running down, step two.
So we have it right there.
Then we want to move three steps down again.
So this is what's happening.
And the next step will be writing down step three.
So we write down step three.
Now we are going to move two steps and two steps up and five steps in the back.
So this is what happened when two steps up and five steps in the back.
And we're going to write down the last step.
And this is exactly what happened.
And this is basically how we are going to write down our function in Excel.
So you can see it's pretty simple what not to write down our function, but how we can in Microsoft
Excel with the use of VBA.
So as you can see, it's not that complicated.
So you said that you guys move to a certain self.
You can add a formal function.
You can add I don't know, for example, you want to make text bold if you want to add a certain function,
if you can do whatever you guys want.
But again, it's pretty simple.
So this is how we right now, let's say you guys want to work with specific cells or in this case with
absolute references.
So how exactly we do this?
So it's pretty simple.
And instead of using the range that well, instead of using the range, how we are using it, when we're
going to use it in a little bit different way.
So first thing that we are going to do, we're going to create another module.
So we'll simply ask you guys to create another module.
You personally?
I have another one right there and we're going to create another.
And so pretty simple.
We'll go back in and we're going to insert our procedure to be able to generate this procedure, so
we'll call it what we call it.
We'll call it the test tube.
And we will click on.
OK, great.
So right now, we have our second micro mystery.
What we're going to practice is pretty much the same thing.
But instead of having a well of weapons, we're going to have an absolute reference.
This means that whatever we are doing, exurbia will always work on some specific cells.
So basically, it's not going to automatically move from one to another and adapt to where which cell
we have selected.
So very simple.
So basically, how exactly do you want to start?
So let's say we want to start by writing down something in our active cell that is there.
So this is exactly what we'll do.
We are going to write down in our active cells so that our active cell we're going to I don't suspect.
So how exactly we do this?
Pretty simple.
We're going to reuse the same formula that we have used in our past class.
So basically, we are going to write down active cell and in our active cell we are going to write down
or Pueblo.
So pretty simple.
Let's do it.
So active
in this case cell.
Or one see, one day here, it is great.
So what is that right down there?
Why don't step?
Step one.
All right, great.
So basically what's going to happen right now is pretty simple if we run our function.
Let's come back there if we're on it.
What's going to happen is that right down step right there.
But if we are mouse, what's going to happen is that it will still with it.
So basically, we still have a function and step up.
But if we want to look our step one in a specific cell, what we need to do, we need to specify the
range at first.
And this is exactly what we'll do right now.
We want to specify the range.
So this is what we haven't done.
So let's say we want to write down our step one in, I don't know, the keys at the peak, so let's
write it down.
So basically, we're starting out with the free range and in this case, our range will be in the eight.
So the eight will be the cell where we are going to write down our function in this case.
So how exactly where you will write down range, each will be acceptable at it and then select this
one.
So basically, that's silly.
So what's going to happen is Excel is going to select this place, is going to select the eight and
then it will write down step one.
So if we run our function, what's going to happen?
As you can see, it's going to write down the function at the end.
So even if I'm somewhat right there, what will happen?
Pretty much the same thing.
So it will move back here and it will write down our function at the end.
So pretty simple.
So right now, let's practice what we have just learned.
So if we want to do pretty much the same thing, so instead of using a relative reference and using
that function, we need to use we will have to use in this case, we will need to specify each time
what what's the exact cell in which we want to write down our function or the text or whatever we want.
So let's say the second step will be writing, going to the cell, I don't know, creating and writing
down step two.
So basically what we can do is simply reuse our formula because it's pretty much the same thing.
So in our second cell right there.
So it's going to be 18.
We are going to write down step two.
And if we run our text, well, if we're micro, as can see, pretty much the same thing.
So it goes from step one to step two.
If we wanted to write it down in a relative reference, what would what would happen is that we will
what we would except the formula are once you one, basically.
Right.
They would start started there and then we would have to move from one to three cells there.
And then one, two, three, four, five, six, seven, eight, nine, 10 cells don't.
So this would be the same thing if we wanted to write it down in reference.
We basically to write want to use absolute references.
It's like this.
So let's say I want to add a little something basic because it's possible to add some basic things.
Let's say I want to make my second step.
So how exactly I will do this.
So it's very simple.
I will simply write down selection and in this case I will use the font and I want to make it.
So here we go.
And basically, I want it to be true.
If we want everything so as you can see, step two became.
So it's pretty much the same.
The way it works is pretty much the same thing as the.
The only difference between the absolute reference and reference from a clothing perspective is that
in relative reference, as you can see there, what we're going to use when he was the upset function,
basically to move from one cell, another cell, which makes a lot of sense, because what we want to
do is simply knowing how many cells we need to move on the right, on the left or whatever.
And in the absolute reference way, what's going to happen is that instead we are going to select the
exact cell in which we want to work one cell where range of cells.
So basically it can be more than one cell.
So hope you guys right now understand the differences.
And I hope that right now you guys will be able to move across your Excel spreadsheet simply by using
VBA programming language.
So it's a first class game and you all know it.
Next class.