Automating Excel Tasks with Macros

In this section, we'll discuss macros. Macros help you to automate the work you do in Excel so you don't have to manually repeat steps over and over again that are involved in creating your reports or creating your pivot tables or formatting inside of a spreadsheet. So let's start with finding out where you can start to create a macro.

By default, if you head over to the view tab and go all the way over to the right, there is a macros group. If you click on the drop down, you will see commands for viewing existing macros, recording macros, and using something called relative reference. We'll take a look at all three options.

Now, if you're working with macros on a regular basis, you'll probably want to add the developer tab. The developer tab will give you more options when it comes to working with your macro. So I'm going to right click on the word view and choose customize ribbon.

When I choose customize ribbon, I can see that there's a tab called developer that's currently not checked. I want to add that to my ribbon. So I'm going to click the checkbox for developer and then click OK.

Now I have a developer ribbon tab and if I click on it, I do not have to click the drop down to access macros, recording macros, or use relative references. I can also access the visual basic editor and look at macro security. So let's take a look at how you would record a macro.

First, you'll see that you can click the record button on the developer tab. You did have the record button over on the drop down. Here's another location where you can initiate the recording of a macro, regardless of what tab you're on.

If you move down to the status bar, this icon here is an icon for initiating the recording of a macro. Now I'll go to the developer tab and click record. Let's look at the record macro dialog box.

We need to come up with a name for our macro, but we need to use a name that works. A name that will not work is a name that begins with a number, but I'm actually going to break the rule here and attempt to do this just to show you what happens if you try to name a macro not using the proper naming conventions. When you click OK, Excel gives you a reminder of what all the best practices are for naming a macro.

So no harm, no foul. I'll go back and just give this macro an appropriate name. I'll call it Word Macro.

I can apply a keyboard shortcut, but I wouldn't recommend you type the letter C here. If you press control C, you will not copy anything. You will run this macro.

Microsoft will override the default control C command. So you may be at a loss as to what keyboard shortcuts you can use because almost every letter is taken. Microsoft allows you to use the shift key, and since this is a word macro, I'll use shift W. So the entire W is there, even though you might only see part of it.

This is just a really small box. You can store the macro in this workbook. You can store the macro in a brand new workbook, in which case the macro will be stored in another workbook and not this workbook.

You can also store the macro in what's called a personal macro workbook, and this is an invisible workbook that will always be open in the background whenever you work in Excel. We're going to stick with this workbook just to make things simple. You can write a description for your macro.

This is useful if you're collaborating with someone, or this is a project that you haven't worked with in a while and you want to remind yourself what this project was about. Now, before I click OK, I want you to take a look at the record macro button up in the command bar. When I click OK, it now says stop recording.

So my actions are now being recorded. Just to let you know, when I click on tabs, that is not part of the recording. So I don't have to worry about clicking on the wrong tab while I'm making a recording.

I won't have to start over because this is not being recorded. Now, our macro is simply going to type the word word in the cell. It's not revolutionary, but we just want to get a macro recorded that is very simple, and then we can test to see how it works.

So I'm going to type word, and then I am not going to press ENTER. I'll press control enter. Control enter allows me to stay in the cell that I'm in, and that is our macro.

I'll click stop recording, and now I want to test it. Now, I use the keyboard shortcut control shift w. Let me test it to see if that works. It does.

I'll go here, control shift w. Now, it may not be practical to share my macro with someone using that keyboard shortcut. If they add the developer tab to their ribbon or they go to the view dialog box, they can click macros, and there they can see the word macro that I recorded, and they can click on it and choose run. Run.

Another way that you can run a macro is you can add a button to your spreadsheet. This is a good use of the developer tab. If you go to the controls group and click the drop down underneath the insert dialog box, the one that has the icon of a drop box, of a toolbox, you'll choose from form controls the very first button form control.

Not active x controls, but form controls. I'll click on the button, and I'll draw the shape of a button. The first thing you're going to be asked to do for this button is choose the macro you'd like to associate with that button.

I'll click okay, and now I can go in and make an edit to the text because button one is a little too generic. I'm going to select the text and call this word macro. I'll click outside of the button, and now it's active.

If I click anywhere in the spreadsheet, all I have to do to run the macro is click word macro. One last way that you can run your macro, if you click the drop down and go to more commands, you'll see an option you probably never noticed before. When you click the drop down for popular commands, you'll see macros, and if you click that, you'll see any macros that you've created in this listing.

I'm going to add it to my quick access toolbar, and if I don't like this icon, I can choose modify and choose from any of the icons that I see here. I'm going to go and pick the letter a, since this is text, and click okay. I'll click okay, and now if I want to run that macro, I can select anywhere in the sheet and simply click on the quick access toolbar.

Because of the way that I recorded the macro, I could select multiple cells and click the quick access toolbar icon for that macro, and apply that word across multiple cells.