Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this last chapter of Excel 2011 for Mac Essential Training, I'll show you how to work with workbook macros. Basically, a macro is a series of recorded actions that you can run to change your workbook in a predictable way. I'll just scratch the surface of Excel macros in this course, but if you'd like more information, I'll give you a list of additional resources in the last movie in this course. But for now let's turn our attention back to running an existing macro in Excel 2011. I have a blank workbook here. I just created a new workbook. I did that so that I can show you what it will look like when you open a workbook that has macros in it.
So, I will press Command+O to open the Open dialog box, and then in the exercise files, I will click RunAmacro and then click Open. When I do, you get a program window for the RunAmacro file, and then you also get this message box. And it's a warning saying that the workbook contains macros, and asking if you want to open it, and if you do there are three options. Either, you can open it Enabling macros; you can avoid opening it, just in case there is anything funny going on in the file in case the macros, a virus, or you're not expecting it - then you can just close the file and talk to the person who sent it to you to verify that it's okay; and then, finally the default option is Disable macros.
That means that you can open the file, but that any macro capability will be disabled, so you won't be able to run them. If there's any doubt, you should not open file. If you're not certain, but you want to look at the contents of the file, then click Disable macros. If you're certain the file is correct, you know who it's from, and you were expecting it, then you can click Enable macros. If you're at all in doubt and you're in a company with an IT department, talk to them, and they can give you guidance on what their best practices are. In this case, I happen so that the file is okay so I will click Enable macros, and I will maximize the window.
So, here I have my workbook, and there is a macro already saved in it, although I don't have it linked to anything yet. If you want to work with macros, the first thing you need to do is display the Developer tab on the Ribbon. You'll see that it's not there right now, but if you want to turn it on, you can press Command+Comma to display the Excel Preferences dialog, and then on the Ribbon page, you'll see here that we have a list of groups and tabs. If I scroll down, you'll see the last one is Developer.
If I check it and click OK, the Developer tab appears on the Ribbon. So I'll click the Developer tab on the Ribbon, and you see that we have a number of different tools. You can work with the macros, you can edit an existing macro, view existing macros, and so on. In this case, I want to run a macro, so I'll click the macros button, which is here, and then you'll see that I have a macro called WriteTaxTotal. What this macro does is it displays a message box, indicating the amount of sales tax at a 9% rate that will be due on the amount in the active cell.
So, you just look in the workbook and you see that cell C7 is currently selected. So Excel will use this macro to display a message box calculating the sales tax due on that amount. I have the macro name highlighted and when I click Run, Excel displays the message box, and it gives me the 9% sales tax amount. Click OK, and it goes away. That's one way to run a macro. The other way to run a macro is to assign it to a shape. That way you don't have to go on to the Developer tab, go into the Macros dialog box, and so on.
So, I have a shape over here, and if I want to assign a macro to it, I'll hold down the Ctrl key and click it with my left mouse button. From the shortcut menu that appears, there is the option Assign Macro. When I click it I can either create a new macro called Rectangle2_Click, or I can use an existing macro, and in this case I want to use WriteTaxTotal. Everything looks good. I'll click OK. I'll just click back over here, so that cell C7 is the active cell. Now, when I move my mouse pointer over the shape, you'll see that it turns into a pointing hand. That indicates that it's a clickable object, and that there will be some sort of an action that occurs after I click it.
So, when I click the shape with the left mouse button, I get the message box that I had before, with the sales tax calculated at 9%. One problem of assigning a macro to a shape is that if you just click it normally, you'll run the macro. So, there's really no easy way - or at least no apparent way - to edit that shape. But if you do want to edit the shape, you can Ctrl+Click the shape, and when you do the macro will not run; instead, you'll get your shortcut menu again, and you can use Format Text, Format Shape, and anything else - you can edit the text.
Just remember, if you want to change a shape that has a macro assigned to it, you need to Ctrl+Click it. If you're given a workbook that contains a macro, make sure that it's from someone you recognize and that you're expecting it. Once you're sure it's legitimate, you can run the macro and take advantage of its recorded procedure.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87049 Viewers
80 Video lessons · 136197 Viewers
59 Video lessons · 54883 Viewers
52 Video lessons · 68759 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.