Why use Excel Macros? Well, if you want to
automate boring tasks, like copying and pasting data
from one place to another, creating reports or update
formatting of your reports then Macros are going to help
you do that with one click. So let's say you received this
dataset on a weekly basis, and sometimes it's shorter,
sometimes it's longer, but every time you need to create a chart that looks like this. Now every time you have to
remember and apply to same steps. What you could do instead is
to record those steps once, so that every other time you just have to click a button to get it all done. That's what Excel Macros can do. Let me show you. (upbeat music) Here, I have information
about the position, the average salary and the
company average salary. Now, I have this information
by different departments each is sitting in its own tab but notice sometimes I have more data and sometimes I have less data. What I need to do is
to create a chart based on this dataset. I want to make this process dynamic, so that I don't have to do
it every time from scratch. To do that, we can record a Macro. There are different ways you
can start recording a Macro, one easy way is to click on
this record button down here. In case you don't see it,
just right mouse click and place a check mark
beside Macro Recording. Another way, you can record Macros is to add to Developer tab to your ribbon. Just right mouse click on
your ribbon, customize it and place a check mark beside Developer, and then click on okay. And you're going to see Developer up here, and you get the ability to
record Macros, view Macros and a lot more. So now that we're all set up, let's start with
recording our first Macro. One tip before you start to record is to think about the
steps that you want to do because every click that you make, every change you make to
a cell will be recorded. If it's not something you want don't do it during the recording. But also don't worry
too much if you mess up because you can always
go and delete a Macro and do it again. Okay, so I'm in the Developer Tab, I'm going to go ahead and record a Macro. Here, you can give your Macro a name, you can assign a shortcut key. This macro is recorded in this workbook, we can give it a description if we want, I'm just going to leave these
as is and click on okay. Now, every change that
we make will be recorded from this point on. Notice on the bottom our icon changed and it says a Macro is
currently recording. Click to stop the recording, well we still need to record
so let's go ahead and start. I'm going to click on A3 because
that's the starting point and remember, I also have
the same starting point in all my other tabs, what I don't have is
the same ending point. So the wrong way of recording this macro is to highlight this area and then go to insert and
insert the chart that we want. Now, why is this the wrong way? Well, let me show you, I'm
going to stop the recording and I'm going to go to the finance tab. This one has more data,
let's run the Macro. How do we run a Macro? Well, if I had given it a shortcut key then I could have used that. Otherwise you can run it either from the View tab
or the Developer tab. I run it from the view
tab, go to Macros here, I can see my Macro and click on run and notice my data is cut off, why? Because the macro recording fixed it to this range from A3 to C8. Now, if you want to see this and you want to see the VBA screen you can use the shortcut key Alt + F11 and that's going to bring up the Microsoft Visual Basic
for Applications window. Here in modules, if you open this up, you're going to see a module one and this is the macro that was recorded. Then notice the range that it selected is restricted to A3 to C8. And that's because we
recorded this the wrong way, we want to make the end variable. So now let's do it the correct way. I'm going to go back to my first sheet, this time let's start the
Macro recorder from here. I'll give this a name and
I'll give it a shortcut key, so I'll do Control + A and click on okay. Now let's go back, click on A3 but this time I'm not going to do this, instead I'm going to use shortcut keys, Control + Shift + Down and
Control + Shift to the right. This is going to make
the recording dynamic. Let's go to insert, insert a Combo chart And now with the chart selected, so don't click away and click back because then the Macro
recorder hard codes, the chart object name as
long as it's selected, this is the active chart. So just go ahead and make the
adjustments that you want. So let's say I want to
update the formatting of the bars to be a darker color. I want to add data labels to
these, make the data labels stand out a bit more. Let's also adjust the transparency
of our average line here. I'll go with the orange but let's go to more outlined colors and adjust the transparency
here and click on, okay. Now, let's send the Legend to the top. So right mouse click, format
the legend and select top. Let's give this chart a
title, click to the side, and we're done. I'm going to stop the recording. Okay, so now let's go ahead and
try this on the finance tab, I gave it a shortcut key
that was Control + A. I'm going to use that and my chart is here and everything is included. How did the Macro
recorder record this time? Well, let's go and check it out. I'm going to go back to the
VBA editor using Alt + F11, all of this was recorded with my clicks. So notice it did range A3.Select because that's the start of my dataset and then it's dynamically
selecting my ranges. So remember I did Control + Shift + Down and Control + Shift to the side. That's what was recorded. Like I said, this is
what makes it dynamic. In addition to using shortcut
keys, whenever you can you also have the option
to use relative references when you're recording. I have a separate video on
this if you're interested, I'm going to add the link to it, to the description of this video. Now there is one thing
we need to correct here and that's the title for our chart. We'd like to is to be dynamic
so that it takes the value that we have in cell one. This is something we can
manually adjust in the VBA code. Let's bring up the
visual basic code window, we can also click on
this icon to bring it up in case you forget the shortcut Alt + F11. Now this is our Macro, if you scroll down, we can see selection.caption
= procurement department. So this is what the macro
recorder did for us, instead, we wanted to reference the cell. Now here, you can see the type of syntax that we're going to need,
something like this. So let's copy this, paste this here and instead of B3, I
want to get this to be A1 and instead of .select we need .value. So if you learn a little bit of VBA, you can easily make these type
of adjustments in your code. Now, optimally another
code that I'd like to add is to make sure that I have
the right data on my sheet, because right now if I have an empty sheet or if I have other types of data, I'm probably going to get an
error when I run this code. To get this done you also
have to adjust the code and this is something you can't record with a macro recorder, you
actually have to type this in. In this case, I'm going to go with an if and say if range A3, so this
is where I have positions, if range A3.value, doesn't
equal to the word position, now I'm also going to
add a second condition because I want to make sure
I have at least one row of data here, so let's go with
an or range A4.value=nothing. So if either of these
conditions are present then I don't want anything to happen, I want to exit the sub,
else I want this to run and if I scroll down, I have to close the IF function with an end IF right here. So obviously if you're new to
VBA, this is another level. If you'd like to learn VBA
from beginner to advanced, I have a complete course
check it out on my website, xelplus.com. Right now, we're going to
go ahead and test this, let's go to marketing. Our shortcut key was Control + A and we get marketing
department right here. Let's go to sales, Control
+ A and our chart is done. What if we're in an empty sheet here and we do Control + A nothing happens. When it comes to saving your file and you go with Control + S,
you're going to get this message that because it contains the VB project, if you want to keep that you need to save it as
a macro-enabled file. So don't click on yes, because that would get rid of the macro and save it as an Excel file. Instead, click on no,
change the type here to XLSM and then save your file. As you can see, macros help
you automate repetitive tasks. If you like to become advanced and create your own custom
macros and VBA code, so that you can go
beyond the macro recorder check out my complete Excel
VBA course on xelplus.com. Thank you for watching, do
subscribe to this channel in case you aren't subscribed and I'm going to see
you in the next video. (upbeat music)
