alright so in this video we'll be using macro recorder to basically just to record some steps
that we do in Excel and just have it repeated for us so the whole idea of macro recorder it
will basically recreate the code for you without you having to write the code so let's do that
and see how it works so the first thing I'm gonna do I'm gonna go to Developer tab when
you do macro recording there are two options to use during recording absolute or relative mode
and the way you're gonna know which mode Iran is depending on this use relative references
button so if it's checked then you're recording in relative mode if it's unchecked then you're
recording in absolute mode now since I've been writing code I've never used relative mode so
I'm not gonna be talking about that right now so maybe later on when we get to know all the useful
things we'll get back to relative recording mode and talk about that but for now I'm just gonna
make sure it's unchecked and I'm gonna start my recording one of the important parts is to start
your recording from some random position that has nothing to do with the position that you're gonna
be doing something in so let's say if I'm about to do something with this data with my macro I would
make sure I click someplace away from that data first before I start my macro recording so what
i'm gonna do i'm gonna click someplace on the side go back here and click on this record macro button
and it's gonna ask me for the name for this macro so this is basically the name of the subroutine
basically just name something with no spaces no special characters you could use underscores if
you want i'm gonna call this format data you can also assign shortcuts to your macro if you wanted
to you can also store it in different places so right now i'm not gonna talk about this but i'll
get back to it in just a little bit so for now i'm just gonna do this workbook and click okay and as
soon as I eat okay it's recording everything I do including my mistakes so what do you want to do
when you're recording you want to take your time and make sure you do as few mistakes as possible
so I'm gonna start by doing some formatting for this data I'm gonna select my headers on top I'm
gonna go on their home choose some background colors maybe I'll also change the font color
I will change the formatting for this column and make it currency I'll change the background color
for my dates change the background color for my sales as well so I'm gonna go ahead and select
this and add some borders and assuming that's all I wanted to do with this data I'm just gonna click
in this upper left corner and go to Developer tab stop recording now if I go to the second
tab which is this data without all of that stuff done if I go to macros I'm gonna see that macro
available if I run that macro it will basically just repeat all those steps exactly the same way
very very quickly as you can see so let's try to go and take a look at the code that was generated
by this so I'm gonna go to macros click on this macro called format data and click add it and
that gets us to our visual basic editor where we have our subroutine so this is where all the
previous videos should help you make a lot more sense from this code all of these that have an
apostrophe are comments we don't really need that we can remove that from this code now you can see
it's basically all the steps that I did first it's selecting from a1 through f1 the labels on top and
at that point that's gonna be my selection and this is that wit block when we have a selection
we can change bunch of things about that selection so we're changing the interior color which is the
background color we're changing the font color we also select the column F which would be the column
of sales amounts and change the format then we'll select this range change the background and so it
goes so we select and then we work with that selection to add some borders and in the end
we finally just select a one cell to get back to the beginning so all of this code was generated by
this recorder without us having to write anything which is great now let's say I wanted to add a
little bit of something to this recorded code so maybe I missed some steps and I wanted to also
add an extra column here so I could record the whole thing all over again but instead what I'm
going to do I'm going to take one of these pieces that's already done with the first macro and again
click on some random spot and start another recording and I'm gonna call this new steps
press ok and now we're recording so I'm gonna go here create a new column I'm gonna call this tax
maybe click in this cell right below and I'm gonna create a formula I'm gonna take this and multiply
it by zero point one zero five so ten and a half percent and then I'm gonna take this and drag this
formula down maybe resize this column a little bit and I want to add some borders and maybe
also change the background color for these numbers as well click in a one cell before I stop and go
to Developer tab and stop recording so now I have two macros so if I go to macros I have the first
one and the second one new steps is the second macro I'm gonna click on that one and hit add it
you should see all the code that was recorded by this so again this comments I don't need I'm gonna
remove but anything going from here and if we keep scrolling down until this ends up is the code that
was generated with the second macro so I'm gonna go ahead and copy that and I want to add this to
that first macro because these were the steps that are missing that first macro should be in the same
module see it's in module 1 now sometimes when you do recording of a macro it automatically creates
another module so if you see there's a module to make sure you check in that one as well now that
first recording was called something with format if I'm not mistaken so if I scroll see format data
so this is that first macro recorded so I'm gonna keep scrolling down until I find the end of that
macro which should have an end sub closing that subroutine so there it is so what I'm gonna do I'm
gonna go here right before that and sub I'm gonna usually add a comment here so I know where the old
code ends and where a new code begins add a column and then basically just paste all the code right
here so now if I go back to this I'm gonna open a new fresh worksheet this one and now we should
have two but the first one that is format data it should basically contain both so I'm gonna click
on that and run this macro to see what happens so you can see did some of these steps it seems like
we're missing some things currency formatting here and then we're missing the formatting that
should be accurate for this column when I go and type something next to this it picks up the
formatting for me but the macro recorder didn't record exactly that it did a little less but it's
fine so what we're going to do we're going to take this and add some more steps to this one as well
so again after this whole thing is done I'm gonna do record a macro call this more steps hit okay
I'm gonna select this column G go back to home switch this one to currency and then I'm gonna
click on the sales column use the format painter to make my text label on top look the same click
on a one cell again go to developer and stop this recording so now I should have yet another macro
I'm gonna open my macros list now to have less code I'm gonna first delete that new steps macro
because I already copied all the code from this and I incorporated that in my format data macro
so I'm gonna click on this and delete that and then I'm gonna go back and click on more steps
and added this macro so again what I'm gonna do here I'm just gonna copy all of this code I don't
need this comments go back to the one before which should be this one if you're not sure you want to
make sure you scroll all the way up check the name so for my data there is the end just add a comment
and just paste that code right here I'm gonna align this just to make it look pretty and now
let's go check that out again so I'm gonna open a fresh new worksheet go to macros format data
click run now we have altering macros combined so I'm gonna go to macros again and delete that more
steps because we no longer need that so when you use macro recorder a few things that I'm gonna
mention here a lot of times people will say to use shortcuts during this recording like control
shift down control shift right to select your data to make your macros more dynamic and so on
and so on but honestly when you start to write code and modify code which is what we're gonna
get to in our next video you don't really need this as a matter of fact they will make your life
probably more difficult so what we want to do we want to just record our macro the most basic way
possible so I'm just going here and selecting everything with my mouse and then when we get
to modifying this macro we'll just modify our recording and everything should just work fine
as of right now the limitation for our current macro is gonna be if our data size is not the
same let me make a couple of copies of this just in case so if I had let's say more rows like this
and I've run that macro see that does everything again until that same road that we had in our data
so it was row 22 that's exactly what is doing in here and in the same manner if we had less data we
run this and we get this so this is usually one of the problems with recorded macros they only
work with data that is exactly the same so this is what we're gonna get to fixing in our next
video when we get to modifying our recorded macros but for now I want to finish this by talking about
storing your macros so if I go to this macros see when I was recording this macro we recorded it in
this current workbook that's where your macros are stored so you want to make sure that you save that
file as a macro enabled workbook so I'm gonna do ctrl s and see when I get this I want to make sure
that the type of the file that I save is gonna be macro enabled workbook so here I'm gonna give
this some name and it's safe if you don't save it as a macro enabled workbook it's just not gonna
have any of your code in there when you have this recorded the new data you're gonna get is probably
going to be in a different file so let's just move this to a new workbook so now I have this book one
that has this tab with the data and I want to run that macro on this so as long as this format data
file is open all the recorded macros in that file should be available in this workbook as well so if
I go to developer and open my macros see that macro that was from that format data dot XLS
M file is showing up here so I can click on that and run it on this workbook and it works just fine
I'm gonna close this so you can store macros in a workbook and then use that workbook when
you need to run your macros now another place to store your macros if I click record macro
instead of a workbook you can use your personal macro workbook and what personal macro workbook
is it's basically hitam workbook on your user on that computer so think about a workbook on that
particular computer so if you use that then this macros are gonna be available on any workbook that
you open without having that other file that has the macros open in it because that hidden
workbook is basically open all the time now the problem with using that is the those macros will
only be available on that computer so if you go to a different computer you're not gonna be able
to use those macros so if you want something more mobile so you can move around with your macros and
use them in different places then you probably want to do in a workbook instead of using that
personal macro workbook so I'm gonna cancel that that's pretty much it in the next video
we'll get to modifying our macros fix this type of situations when it goes over our data set but for
this one that should be it thanks for watching please subscribe and I'll see in the next video