Join Dennis Taylor for an in-depth discussion in this video Short examples of the recording method, part of Excel 2013: Macros.
Although there are different ways to create a macro, by far the dominant method is to turn on what's called the macro recorder. And this flows out of the idea that we've recognized a sequence that we need to use frequently. Maybe we already are using it frequently manually. We want to automate it. And let's imagine that sequence is the following. We want to know, in any given worksheet, at certain times, where the number cells are, and we'd like to highlight them and apply color. And rather than going through six or seven or eight steps, let's do it instantly.
So, after identifying the steps, we're going to turn on what's called the macro recorder and there are a few different ways we can get started here. In the lower left hand corner you might or might not be seeing a button to the right of the word ready. So if you right-click on the status bar, that's the panel at the bottom of the screen where the word ready appears, right-click, you'll see a choice called macro recording. And if it doesn't have a check in front of it, as it doesn't here, just click that option. And now you do see a box at the bottom of the screen. Once that's there, and you slide the mouse over it, a pop-up tip reads no macros are currently recording.
Click to being recording a new macro. That's one way to get started. Another way is on the View tab in the ribbon. There's a button, on the right side called Macros. There's an arrow below it, that includes the option Record Macro. A third way involves activating another tab in the ribbon. And although not necessary, the Developer tab also has buttons associated with macros. So right-click any existing tab, for example view. Choose Customize the Ribbon, and in the section here called Main Tabs, you'll see a box for Developer.
And if it's not present, check the box, click OK, and now the Developer tab is present as well. If you click the Developer tab, you'll see off to the left, a code group related to macro capabilities. So we could start recording a macro there as well, too. So we've identified the steps. When you record a macro, what happens is, every action that you take gets converted into a programming language called VBA, meaning visual basic for applications. It's a programming language also available for Word and Powerpoint and other software as well.
And for the moment we won't worry about that but we are concerned about the idea of what it is we're about to record. And so we've either written down those steps or we remember them clearly. Let's start the macro recorder. I'll use the View tab here, the drop arrow for macros, Record Macro. Now, you're going to be prompted with a macro name, macro one, macro two, three, whatever, something like that. But the convention is to use a meaningful name here. Macro names can be up to 255 characters. They cannot begin with a number and cannot contain spaces.
And, ideally, it should describe what the macro does. Highlight, now I can't use space here, so I could use underscore or simply switch into upper case. I'm using underscore here, number cells. You can use a keystroke shortcut, and that's appealing, although after you've written a few macros and have a variety of different keystroke shortcuts sometimes you forget which one is which. And you'd like to use meaningful letters here, but you might run out of them. First of all, you have 52 choices here. Any of the lower case letters, any of the upper case letters. If I'm about to use the letter n here, I will no longer be able to use Ctrl+N to create a new workbook.
If I use Ctrl+C, perhaps I'm thinking of the word cells here, I will no longer be able to use Ctrl+C for copy. So a lot of macro users, if they do use keystroke shortcuts routinely, just hold down the Shift key, and then use a letter. There aren't too many well-known keystroke shortcuts that use Ctrl, Shift and a letter. So I was holding down Shift there as I pressed the letter N. That's going to be the keystroke shortcut that allows us to run this macro in the future. And by the way, later when we want to make the macro actually do what it has to do, we use the phrase, we want to run the macro or we execute the macro or we play back the macro.
Those terms are all synonymous. Let's store the macro in this work book. Now there are other choices, which we'll see in subsequent movies. For the moment we're storing it here even though long-term we might be thinking, you know, we could use this macro in other work books, too. Provide a description. This is really helpful because later when we view the macro code, the description will be included with it. And rather than having people try and figure out what the macro does by the code, a combination of seeing the name and perhaps the description is going to help. What I often do is simply copy the name and then expand it a little bit.
So I'm copying that with Ctrl+c. Click down here. Ctrl+v. Maybe I'll put in some spaces here. And put in the idea that this is happening only in this worksheet. So I'll follow this with, in this worksheet. So we're about to click OK. What we're about to do is record actions until we indicate that we want to stop. So you want to stay focused here, and don't do things that are not really necessary for the macro. If we do scrolling, that's going to appear in the macro. If we click on different cells, that will appear there, too. Certain actions that we take with inside of a dialog box, if we then choose Cancel, will not be recorded.
So, we click OK. Just before I do this, note that in the lower left hand corner, to the right of the word ready, is an icon. It's going to change into a box as I click OK. And, there it is. Sliding over the box is the pop up, a macro is currently recording, click to stop recording. We can also stop recording from the macro's button on the View tab, as well. So, we want to highlight the number cells. What do we do? Go to the Home tab. Chose Find and Select and then go to Special and then, Constants.
But not text, logicals, and errors, so we'll uncheck those boxes. Click OK, we've highlighted all the cells that have numbers. And in order to recognize those, because clicking somewhere will remove the highlighting, we'll choose a color here from the font group on the Home tab, a light color is preferable here. Perhaps this one. And although it's not really critical, we're going to click outside of the highlighted area. That means that we'll see the colors better, and we'll click here. Now, if we happen to do scrolling, which isn't really necessary, that's going to be in the macro.
Now, it's so innocuous, it won't turn up, it's won't be bothersome. But I want to accentuate the idea that we really don't want to be taking extraneous actions. They don't have anything to do with our goal here. So, we're ready to stop recording, we could click in the lower left hand corner or we could go to the View tab, click the drop down under Macros, Stop Recording. So, we've recorded a macro, how do we play it back? How do we run it? How do we execute it? They all mean the same thing. I'm going to go to a different work sheet. Click somewhere here and try that keystroke shortcut. Ctrl+Shift+N.
There it is, it highlights the number cells. Different work sheet here with a ton of numbers in them, although on a lot of those formulas, well, let's press Ctrl+Shift+N. There we are. So, we've created a macro, we've played it back, it works. And it's our first example of recording actions. And it's relatively straightforward. And I think you can see that it's not difficult, really. And the more situations we identify as being something that we want to record, the more we'll see how easy that is and how fast it is.
So recording a macro is the basic and dominant method of creating macro code so that we can later play back the macro wherever we need those actions to take place.
- Understanding what macros can do for you
- Running macros
- Creating a Personal Macro Workbook
- Recording macros
- Creating nonrecordable VBA code
- Expanding a macro with statements
- Using absolute and relative references
- Testing a macro in Step mode
- Joining two macros
- Using loop structures
- Streamlining macros