Join Dennis Taylor for an in-depth discussion in this video Running a macro, part of Excel 2016 Essential Training.
- We're looking at the worksheet PricingSheet. It's in our file 15 - Macros. It's a strange worksheet in that we cannot figure out what's going on very easily. We've already identified the need to locate the cells that have formulas. That's pretty easy. From the Home tab, far right button, Find & Select, choose Formulas. That's also a feature that we might use in other worksheets as well. In fact, maybe we're the person in the organization that people turn to to help them figure out what a file is all about, what a worksheet's all about.
We've become the worksheet guru maybe. That's a handy feature. And it's short enough to say, "I don't mind doing it manually." But on the other hand, we could turn that into a macro. Now there's a companion feature that's going to take a few more steps, but it's pretty valuable too. We might want to highlight just the cells that have pure numbers in them. And we can do that by first clicking on a single cell, going to the Find & Select button again on the Home tab, and you wouldn't have known about this feature without trying a few options here. It's under Go To Special.
Now if we choose Constants, it will highlight constant cells meaning cells that do not have formulas in them. And it's at first a little bit strange, too. The Formulas button applies to these boxes below, but so does the Constants button. If we just want to highlight those cells that contain constants that are numbers, we'll uncheck these boxes here. Click OK, and it will highlight those cells. And we might want to apply a color to these as well. So that's quite a few steps, and we would get tired of performing those single actions if we need this frequently.
And furthermore, as I'd suggested earlier, if we want this feature to be available all the time, in other words if we'd like to have a macro that does these things all the time, we can do that. Let's create a macro and execute it a few times, not only here but maybe in other worksheets, and ultimately in other workbooks as well. Sometimes when we record macros we're thinking of this idea, we can use this in a lot of different situations and in different files. So we've identified a need.
Let's go to the View tab in the ribbon and click the arrow under Macros. Record Macro. Remember, we don't go here unless we have identified the steps we want to record. And that could have meant, writing them down or remembering them. Record Macro. As always there's a temporary Macro name here, let's give it a better name. Highlight_Number_Cells If we wanted to we could go further with a longer description, and indicate that these are not formulas. For now, Number_Cells.
Keystroke shortcut we'll use here, how about control shift N. Key thought in our mind might be the letter N. Hold down the shift key, press the letter N. And then if we have time we'll put in a description. However, Store macro in, we've identified this macro, this potential macro, as something we want to have available all the time. Click the drop arrow, choose Personal Macro Workbook. If this is the first time that we are recording a macro on this computer in Personal Macro Workbook, we are in effect creating this workbook.
This workbook will have the name personal, and its file extension will be .xlsb, think binary, although we don't have to go into discussion on that. .xlsb, personal is the name of the file. It will be stored on this computer. If we click OK, record the macro, save our workbook, and then as we exit from Excel there will be a prompt to save the Personal Macro Workbook, we will do that. That means from there on into the future, every time we open Excel this Personal Macro Workbook will be available.
If we've already recorded a single macro here, at least one macro, now if we press OK, we're simply on the path to adding another macro into the Personal Macro Workbook. It's also important to stress here that if we did choose this workbook and then later we thought, "Could we put that into the Personal Macro Workbook?" the answer is yes. You can copy and paste macros from one workbook into another. It's relatively easy, it's just like copy and pasting text in Microsoft Word. We've decided we'd like to be able to use this macro with other workbooks as well on this computer.
We've chosen Personal Macro Workbook, click OK. Alright, we're in record mode. We've decided we want to be able to highlight the number cells. Go to the Home tab in the ribbon, right most button, Find & Select. Go To Special. These are the steps we took moments ago. We choose Constants, we uncheck the box for Text, Logicals, and Errors. Click OK. We've highlighted the cells that have numbers.
And let's say that every time we do this, we want to apply a background color. If we already have a macro that does this for formulas, maybe it's yellow, we'll be sure to pick a different color here. And if we do it, it's got to be a light color so that we can see the numbers through it. We're almost finished, in fact we could be finished. Recognize these cells are highlighted and they are selected. I'm going to click in cell A1. The colors will stay, but those cells will not be selected for the moment. In other words, every time we run the macro, we want the cells to be highlighted, the color applied, and then simply jump to cell A1.
Now remember, we're in record mode, and the action that I just took there of clicking on cell A1 is in the macro. We're done recording. We could click the box in the lower left corner. Stop recording. We've got another worksheet here, Sales and Profits, let's go over there. Where are the numbers in here? I click here, I see that's a formula. So's that, looks like a lot of them are. This one isn't. Active cell is anywhere in here, makes no difference. Let's run this macro.
Ctrl + shift + n. It highlights only the cells that have numbers. So this is a worksheet that's just loaded with formulas. If we had a macro for highlighting formula cells, these cells over here, and all of these and the two clusters below, these would all be highlighted in yellow if that's what the macro did. So, we've got this macro, it's available all the time. Now you might see the undo button up there. Can we undo this? If I press this button right here to undo, it's coming back here to do something.
What's happening on Sales and Profits? We cannot undo this. And as we pointed out in the previous movie, you can't undo what a macro does, although if what the macro actually performed was something about applying format, we could certainly manually take off this format. But again, be very sensitive the idea about undoing what a macro does. You can't really do that, although in some cases, like we suggested here, we can override that concept simply by applying a different format. We might also want to have this available by way of a button in the quick access toolbar.
In fact, if we start recording more and more macros we either run out of meaningful keystrokes, or we might forget which one is which. Keep in mind, at any time if you forget what that macro keystroke is, or if you want to change it, you can go back to the View tab, the drop arrow for Macros, choose View Macros. It'll show all of our macros here, in this case only one. We can go to options. If we need to apply a keystroke shortcut that we forgot to apply first and now we want one, this is where we come.
If we want to change this from N to a different letter, fine, or we can get rid of it too if we wish. We can make changes here. I'm not making any changes, I'll just do a cancel. If we forgot the keystroke shortcut, we can also come to this dialog box. If I click Run we're running the macro again. Cells are still highlighted, so we wouldn't see anything right now, but this is our fallback method for running a macro. And by the way, when we say run a macro it's the same as saying playback the macro. So the word playback, the word run, sometimes you'll hear the word execute, sometimes you'll hear the word activate, they all mean the same thing: let's make the macro do what it's supposed to do.
Run is frequently used. We'll just close this for now. And let me manually take this off, because we want to test this macro again by way of a quick access toolbar button. So all we need to do is highlight this data, and on the Home tab we'll remove that color background. Fill Color button right here, No Fill. Alright, active cell's anywhere. Let's go to the quick access toolbar and right click it. Right click anywhere in the quick access toolbar, and then choose Customize Quick Access Toolbar.
Down the left hand side you'll see some choices here. There's a panel up top, Choose commands from. Click the drop arrow and choose Macros. Now you might or might not see some system files here, I'm only seeing one, here's the one. That's the macro I just recorded. Clicking Add means add this to the quick access toolbar. There are currently three buttons in the quick access toolbar, now there's about to be a fourth one right here. If you don't like the icon to the left of it, you can choose modify, and you've got 181 choices.
And it's highly unlikely that any one of these would convey what we're trying to do here. Maybe there's one in there with a number on it, something like that. But again, sometimes it's just a button. Maybe a green button here. Good enough for now. Display name is here, you can certainly leave this as you wish or maybe just edit this to make it display what you want. So you can delete, add characters. It doesn't have to match the actual name of the macro, although it probably should be fairly close, or maybe slightly longer to describe it.
Highlight Number Cells in this worksheet, something like that, or good enough the way it is. Click OK, and then OK, and we've got that button. Now that button's going to be there all the time, even when other workbooks are open, and it takes up a little bit of space. But let's say you put it there because you need frequent access to it. We don't need to know the shortcut now. We're in any worksheet, say this one, we'll click that button, what happens? It highlights the number cells. We could go back here to the PricingSheet, click anywhere we want, where're those number cells? There's that button, let's click it.
Remember, if we forgot the keystroke shortcut or if we didn't have the quick access toolbar, we would have to go to the View tab, choose the arrow underneath Macros, go to View Macros. We only have one macro now, but if we had many we'd select the appropriate one and click run and that would do that same thing. And there are other ways too. If a macro is for this workbook only, and it could be and this one isn't, but still, we can run a macro also from a button. And we can get to those in multiple ways too.
I'm going to the Insert tab in the ribbon, there's a shapes icon here. We can pick any of these. Smiley face, pick a pentagon, hexagon. Maybe we'll use a rounded rectangle, makes no difference. Create one of these. Right click. At first you might not see the appropriate choices, right click again, you're likely to see Assign Macro. So, the only macro we have for now, this one. Click OK. I could have put text in there, I still can.
I can say Click here to highlight Number Cells. Wasn't truly necessary but that's OK. And all the other things you might be familiar with from the Home tab. Jumping back over there, make it bold, center it, top bottom left right, that sort of thing, fine. Click outside of it, it's now available. Let me manually take off the colors here. Highlight the cells this way, Fill Color bucket. Most recent use was to choose No Fill, so we'll apply that here. Try this, Click here to highlight Number Cells.
It does that. Unlikely we would have a button for the kind of action we just saw here, but imagine in certain other kinds of worksheets where maybe we sort and we've got three or four different sort macros that sort the data differently. We'd have different buttons to the right to do this. And you could also do this with a picture too. Insert, Pictures, we could go to Online Pictures. Maybe we'll type in a car. Now we're stretching this a little bit, but you get the idea. If it's appropriate, I can't really suggest this as a strong feature here, but if you have an icon, and it's a huge one here, we could shrink this, and like any other object or shape here, this too could represent a macro.
Right click, there it is, Assign Macro. So maybe a little far-fetched there, but here too. Take these cells here, no background, click over here, run this macro. Same idea. So we've seen different ways to run macros. More important than this idea of the icons here though is the central idea of creating a macro that's going to be available all the time. If we close this workbook, we would save it, but more important, when we exit from Excel there will be a prompt saying, "Do you want to save the "changes you've made to the Personal Macro Workbook?" And you must say yes if you want this macro to be available in the future.
For more information on how to work with macros, check out some of the other macros course available at lynda.com.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros