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)