Join Dennis Taylor for an in-depth discussion in this video Reviewing the recording method, part of Excel 2010: Macros in Depth.
The best way to get started creating a macro is by recording your actions. Even power-macro users will use this is a fallback method for creating a macro. In this particular worksheet, we've decided it's important to us. We are also thinking how important it might be in other worksheets here as well. We'd like to highlight just the cells that have pure values in them. A quick reminder of how we do this: on the Home tab, the rightmost button, Find & Select, has an option called Go To Special and by clicking Constants and then unchecking the boxes for Text, Logicals, and Errors and then clicking OK, we have highlighted only the cells with pure values in them.
We could then apply a color to these. But we'd like to have that happen instantaneously because we've identified it is something we'd like to use often, and we want it to be fast. If we highlight a few cells and go through those steps, by the way, we will only be selecting the cells within this range. By clicking on a single cell, we are about to check the entire worksheet. So how do we create a macro by recording? In the lower-left corner of your screen, you'll see the word "Ready," and to the right of it you might or might not be seeing a button.
I'm not seeing a button here, so I'm going to right-click in that status bar. And you'll notice a lot of selections here, including one called Macro Recording. By checking this option, you see the check in front of it, and also at the bottom of the screen, we now have a button. This is one of two major ways to start recording a macro. The current message says, "No macros are currently recording. Click to begin recording a new macro." Another way to start macro recording is to click the View tab in the Ribbon and the rightmost button called Macros.
There is a drop arrow there, Record Macro. A third way is adding a new tab in the Ribbon called Developer. We don't really need to do that, but that's another way. It will give us some buttons up here for starting a macro recording as well. We can use either of the two techniques I first alluded to. We've identified the need, possibly we've written down the steps; we are ready to do recording. In the lower-left corner, I am going to click that button, and here's a dialog box, Record Macro.
We need to give the macro a name. We always represent it with the default name, but we want it to be meaningful. That name can be pretty free-form with a few obvious exceptions. It cannot begin with a number. It cannot contain spaces. If you'd like to simulate different words and make it looks somewhat like a sentence, that's fine, but use the underscore character or simply shift between upper- and lowercase. Ideally we want this to describe what the macro does. As you see more and more macro, many, many times, a good name helps define and describe what the macro dose.
Why not "HighlightValueCells?" I am not using underscore. I am just shifting case here. A keystroke shortcut is extremely valuable, and it's a fast way to run a macro later--make it happen. But we have only 52 choices here: any of the lowercase letters, any of the uppercase letters. And if I were to use the letter C here, thinking of the word "cells," I would not be able to use Ctrl+C for copy anymore. So there's a tendency among some users to simply use the Shift key because these combinations are not widely used.
So I am holding down the Shift key here, and maybe I'll use the letter H. I am thinking of the word "highlight." You can't always come up with the letter that you always want here. Ctrl+Shift+H will be the way that we make this macro happen later. We want to store it in this workbook. There are some other options here, which we'll see a bit later, and describe the macro. Ideally, you want to provide this because the more macros you see--as you're looking at code, rather than trying to figure out what's going on--you want a description. We want to say also here we are on the verge of creating a macro, and what that really means is whatever actions we take are going to be recorded so we can play them back, but they actually get converted into a language called Visual Basic for Applications, VBA. More about that later.
The description here what I often do is simply copy the name, make some adjustments, maybe expand it a little bit. I just press Ctrl+C. Down here I'll press Ctrl+V to paste. Maybe do this: "HighlightValueCells," and maybe we'll just add the phrase "in this worksheet" because that's what it mean, of course, only this worksheet. As we click OK, a quick reminder here: we are on the verge of saying let's record everything until we stop recording. So as I click OK, and you will notice in the lower-left corner of the screen, the previous icon has been replaced with a box: "A macro was currently recording.
Click to stop recording." Nearly everything we do will be recorded. Now if I do scrolling like this and-- maybe that was a mistake--that's actually going to be in the macro. It's so innocuous we'll never know it anyway, but you want to stay focused and do only the things that you want to have recorded. If you happen to enter a dialog box, if, for example, if you were to right-click on a cell and press Format Cells--anything you do here-- if you press Cancel, it is meaningless. So those kinds of actions don't appear in the Macro; nearly everything else does.
So what do we want to do? Exactly what we did before, Home tab > Find & Select > Go To Special > Constants, uncheck Text, Logicals, Errors, click OK. Let's apply color; maybe we'll make it green. And although its not critical, right now these cells are highlighted, and they are in color. It's a convention to click outside, or maybe we'll just click on cell A1. And they are no longer highlighted, but the color is there. We are done recording. Lower-left corner, there is a box. Click it.
We have stopped recording. We have created a macro. It's fast, it's easy. And let's test it. Now something you'll learn early on when you run macros: you cannot use the Undo feature to undo what you just did. And that makes us think about macros might be deleting data--and we want to stay away from those for a while. But since we went through the action of recording these, maybe the best thing to do if we want to test this is go to a different worksheet, over to Profits here. It looks like there's some color already there.
Let me take it of manually. Click here, No Fill. Are there cells in this worksheet that contain values? Well, we know there are, but where are they? We'll use that keystroke shortcut. I use Ctrl+Shift+H. It highlights these cells in color. We could go to different worksheets and try this. How about over here? Any cells with pure values? Ctrl+Shift+H. We see that it's working. The macro was recorded when using the formula sheet. But it's not stored here; it's stored in this workbook, so it can be used anywhere. And we've just demonstrated the relatively easy process of recording a macro.
It's going to be the dominant, but certainly not the only, method of creating macros.
- Understanding what a macro does
- Recording and adding functionality to a macro
- Running macros
- Creating non-recordable VBA code
- Testing a macro in Step mode
- Joining two macros
- Using loop structures
- Streamlining macros
- Creating a Personal Macro Workbook