Guys, and welcome back to the class of our course about the complete introduction to Microsoft Excel
VBA.
So basically in the past few classes, we talked about plenty of things and we talked about how to program
and VBA, so how to write down in macro how to work with Imakura and basically, well, all the things
that are around in micro.
Absolutely.
What we are going to talk about will be also inside of the code, basically in the programming part.
And what I want to teach you is really a little bit more advanced.
So it's how to correct the record in case that you guys made a mistake somewhere.
But you are not sure where exactly is the mistake or you simply want to change something inside of your
code.
So instead of just playing around and trying to find it, you can correct it directly.
So basically, in my opinion, it's pretty simple.
So how exactly will do this?
So it's pretty simple.
So for the purpose of this course, what we'll do, we are going to create a small, basic micro and
we are going to learn how to use it.
So pretty simple will start by simply going somewhere right there.
So open up your Excel spreadsheet and then we are going to record.
So basically inside of a macro, we are going to have the letter.
So basically it's going to be ABCDE or just a doesn't really matter.
Then after that we are going to have two numbers and we are going to make an addition of those numbers,
any subtraction of those numbers.
So you can see it's pretty simple.
So let's start.
So as I said, first thing that we want to do is writing down our letter.
Just don't forget to record the Microsoft usability reference and record the macro.
So call it a test.
All right, so step one will be writing down the letter then the step two is pretty simple.
As I said, it's going to be writing down number one, then writing down number two, then writing down
addition, then writing down substraction because we're going to perform an addition and subtraction.
Then next step is pretty simple.
We are coming back right there.
Or letter in this case will be a.
Then for the first number, we are going to use a function, so we are going to generate a random number
that will be between one and 10 in this case.
So pretty simple.
Second number, pretty much the same thing.
So we are going to generate a random number that will be between one and 10.
And finally, for the addition, what exactly we are going to do, we are going to make an addition
of those two numbers to basically seven plus nine in this case.
And for the substraction, pretty much the same thing.
We are going to subtract six will treat from six.
So here we go.
All right, so right now we have our macro that works perfectly so we can make it a little bit bigger.
So take everything that we have right there and just bring it down there.
And we can even change the letters if we want to.
So basically we can write down there, see there the e f g h.
I got kids there, so we have everything ready and set up, so right now, if we try to run our Macroom,
so we have our test artist when we run it.
So it works perfectly for a test that we can simply try it out.
So everything works fine.
Everything is perfect.
All right.
So what exactly is the next step?
The next step will be checking out the code of what we have just created.
So if we go right there, pretty simple right here, we have all the code that we well, we have all
the code that we have created and what exactly we can do right now.
Well, it's pretty simple.
We can check the code well as it is right here and basically checking if there are some things that
we need, some other things that we don't need necessarily maybe deleting them.
But in my opinion, I'm looking at the code right now, and I'm pretty sure that we need absolutely
everything.
Well, the majority or the majority of those, we need them because it's a text.
Ready.
All right.
So this would be the first step.
So what we'll do right now to understand a little bit more the code, we are going to use a very simple
tool and that will allow us to check line by line what's happening.
So instead of running the whole macro, we are going to run our text.
Well, not our ethics, but our macro line by line.
Basically, if we want to bring some modifications, we are going to be able to bring those modifications.
And at the same time, if someone committed a mistake somewhere, you guys will be able to find your
mistake pretty simply.
So let's start.
So first of all, we'll just delete one of those things so we don't necessarily need it.
And right now what's going to happen is that we are going to generate our Maku right there.
So how exactly we're going to generate it, I'm pretty simple.
We are going to run it line by line.
So for the purpose of this course, we are going to need two more buttons.
And basically those buttons are pretty useful.
So the first one will be the step into and the breaking point.
Those are two really useful buttons.
Basically, you can find them in the debugging part right there, but you can also add them this way.
So basically, it's the two buttons that the two buttons that you can see right there.
So you can just customize your ribbon if you want to make them appear right here and you simply go here
into the bag, then you simply take the buttons that you need.
So in this case, we have my button right there and my button right there.
So pretty simple.
It's going to be step into so we can take it and bring it right there.
So simply by taking it and adding it to the ribbon.
And you can do pretty much the same thing with the tool breakpoint.
So simply taking it and bring it right there.
So basically, let me explain those two buttons.
So first of all, the step into button is pretty simple.
What it does, it will allow you to jump and check out each and every line of your code.
So basically, line by line, you'll be able to check out each and every line of your code.
There is another button that is step out.
So basically it will allow you to just step out if you don't need to check it anymore.
But basically, this button, my opinion is really important because once again, you'll be able to
check your code line by line.
And if there is a mistake somewhere, you will be able to mark it out with this button right there,
which is the toggle breakpoint.
So basically, let's start.
So right now, you guys should have your buttons right there.
You should have your macro created and now it's time to generate or I couldn't see what it looks like.
So first of all, we'll go right there and we'll start with our step into.
So right here we have nothing that important.
So this is the name of our macro, which is test.
So if you want to change the name of our macro, we can do it.
So you can see it's not going to check out well, it's not going to check out the comments.
So first thing that happened, it generated letters, so basically this is the first line that have
been that have been what basically that have been created.
So we have letters that have been generated right here.
Right.
So if we continue, next thing that will be generated will be, well, this thing right there, so it's
going to jump one cell.
And then no one will be treated, so in this case, we can write it down so we can see no one have been
created.
So next step, pretty simple.
We are jumping one one step again.
So basically one cell.
We're going one cell and another number will be created.
So we have jumped one cell and now number two will be created.
So zero one means from well, it moved from zero.
And that one.
All right, so next thing we are going to write down, Ed. So basically you can move it right here and
what's happening in addition, right there, we can change, for example, instead of writing down Ed.
We can write down something else.
So we going to mark this place right here.
So if we want to change it in the future, we can market and change it in the future.
So we have additions that have been written down right here.
Then once again, jumping one cell and we are going to write down substraction.
So as you can see right here, we have substraction.
So substraction have been written down.
So once again, if we don't want to change additions anymore, we can come back here and deliver the
same thing for substraction.
Let's say you want to change substraction from forceable.
I can come back here and delete it.
All right.
So next thing that happens, we are going to jump four cells in the back and the one cell will don't.
So if we run the code, as you can see, this is what happens before the letter happening, the first
letter is the letter A..
So if, for example, they want to change letter H, I'm able to do it so I can, for example, change
a letter A for the letter.
I don't know the letter.
A capitally.
And we can continue.
So here we can see the capital letter that have been generated.
So right now, next thing, once again, we jump one cell.
And we continue.
Then what's happening?
We are going to generate.
We are going to write down this function to between one and 10.
So basically, if, for example, instead of writing down the function random between one and 10, we
got, we want to write down another function.
We can change it.
So basically right here, as you can see, selection, that formula formula have been created.
So instead, let's say we want to generate a number that is between, I don't know, one and five instead
of one in ten.
We can do it right there.
So here we go.
We have changed our code once again.
So here we'll have a number that will be generated.
That is between one and five.
So we can continue here pretty much the same thing, let's say we want to generate the number between
one and seven.
Great, so we can continue once again, once again, we're jumping one sale, so this is the code that
is saying to us that we have jumped one cell and we can continue right now.
So what's happening right now is that we are going to make an addition.
So this is the addition that we are making, so from this cell right here, we are going back to cells.
So this cell right there, plus this cell right there.
So basically from the first cell, we are going back to cells in the back then, plus one cell in the
back.
So this cell plus this cell.
So pretty simple to understand.
So if, for example, we want to make an addition of, I don't know those numbers right there.
So those two numbers, it's possible, but we will have to jump really, really far in the back.
So we'll need to jump one, two, three, four, five, six, seven, eight, nine cells.
So we are going to write down minus nine here in minus eight there to be able to make an addition of
those two numbers.
But it's possible this is how basically Excel works.
All right, so when it's done, we can continue.
So basically here, everything works perfectly fine.
We'll do the same thing for the substraction.
So once again, for the substraction, what happens here?
We can see minus three, minus two.
So what's happening is pretty simple.
We are jumping once we're jumping three cells in the bag, then two cells in the back.
And that basically this is what happens.
So let's say we want to change it.
We are able to change it.
We can work it and change it a little bit later.
And will continue.
So then we are going to hear, as you can see, this is the formula for Autofill.
So basically we are going to autofill our well, all this other thing that we have created and we're
going to autofill it for all this part right there.
And then we are going to change our letters.
So let's continue.
So pretty simple, as you can see, it has been filled.
Then next thing.
Next thing that's happening, we are changing out our letters, so basically we're going down AF1 each
time and we're changing out our letters.
So we can check it out, so here, for example, we have a small bit, we can make it a capital B. So
once again right now, we're going to have a small bit, but we can make it a capital B instead.
So we are deciding.
So each time that we are moving in the coop, we can change some parts of the code.
So pretty simple.
So same thing right here for for the for e OK, let's say if I want to change it, we're going to market
and change it at the end.
Great h i g and let's say we want to do the same thing for Kate.
She would go want to change our kitty, not this one.
This one right here.
So if we continue.
Right, and this is for our well, basically this is for our family.
So, as I said, we wanted to change some parts of the culture, so basically wanted to change this
part right here.
So.
Wanted to do what, not this one, I mean, this one.
So basically wanted to do is pretty simple instead of minus three.
We wanted to do what?
Let's do minus three.
Minus one.
So what's going to happen is that we will have a substraction of this number minus this number right
there.
So it's going to be something that is pretty cool.
Well, we can subtract the additions minus the first number.
So in this case, it's going to be minus one and minus two.
So we can write it down right there.
So our C minus one in here, minus three.
So with this, we will have always positive numbers and same things here, wanted to change our F for
A capital F.
And our key for a capital case, we can delete this and then change key for a capital case.
And the same thing we are going to take this race right now, as you can see, is we have created a
macro that is well, it's not that complicated, but once again, we have created a macro that well
is a little bit complicated.
And we have worked to change all the parts of the code that we wanted to change.
So let's try our micro right now.
So what's going to happen is that in substraction numbers, we are no longer going to have, well,
negative numbers.
So it's all going to be positive numbers because we did we subtracted the additional minus the first
numbers.
So basically the addition will automatically be greater than the first number.
So we are going to be sure that we have only only in this case, positive numbers.
So let's try it out, so pretty simple.
We're going to go inside of our developer tab and we are going to run or Microsoft will go here and
we're going to run our test micro.
And as you can see, everything works perfectly fine.
So we have our letters.
Some of our letters are kept.
The letters are numbers right there are there.
And finally, these abstractions are positive.
And this is exactly what we need.
So I hope you guys understand this part of the class.
So pretty simple.
What we have what we saw today are those two buttons.
So we have this button right there and this button right there.
So the step into and the toggle breakpoint button, those two buttons are pretty important, in my opinion,
are really, really useful in the case that you want to make analysis of your VBA code and be able to
take away all the little bugs or simply run your code step by step to be able to play inside of it and
change some parts of the code so that first class guys all in the next class.