How to Create & Use Excel Macros (Real world example)

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.