Join Dennis Taylor for an in-depth discussion in this video Short examples of the recording method, part of Excel 2007: Macros in Depth.
The best way to get started creating macros is by actually recording your actions. Now, that might be preceded by you writing down the very steps that you want to occur every time you activate your macro. So, as we begin the process here, either you have written that down, imagine your real-life situation, or you are prepared to remember everything you need to do here. And keep in mind this fact also. Many power users of macros rely upon the recording method for much of what they do.
That will vary somewhat depending upon the nature of what it is you attempt to do with macros. But let's say we've identified a need and here we are on the Formulas worksheet, and we've mentioned this idea that it would be very valuable to be able to quickly highlight the formula cells and maybe have a color associated with them and then also highlight perhaps the content cells or highlight them a different color. And again you might not need the macro for highlighting formulas because remember it's very fast. Of the Home tab, you simply can click Find & Select.
that right-most button. Choose Formulas and there they are. It doesn't take very long. Highlighting cells with values takes a bit longer and you've identified the steps, let's say, and you've also identified the need. That's all important. The why behind all this. Why would we want to do this? Because we're envisioning needing this often. Perhaps a lot in this particular workbook, maybe it has many more sheets than we are currently seeing. And down the road you might have been thinking about another aspect of macros. We might want to have macros available to any different workbook, no matter what.
For the moment, we're focused on just the idea of creating a macro that's going to work for us in this particular workbook. Now, the macro recording process can start in a number of different locations. You'll notice in the lower left-hand corner of the screen here in the Status bar next to the word Ready is an icon. No macros are currently recording. Click to begin recording a new macro. So we're about to do that. However, you might not be seeing that button there and if you are not, here's what you probably should do.
Right-click in the Status bar and you will get a large list of items that you can either make appear or not appear in the Status bar. If you're not seeing the icon that I just referred to, the box next to Macro Recording has not been checked, or let's say it hasn't been checked and when you do click this, it will become checked and you will see that button there. That's all you need to do, and it will stay there indefinitely. Now there's also a way to record a macro starting from the View tab. The rightmost button says Macros and there is a drop arrow there called Record Macro, and there is a third way we will see it a bit later using a new tab in the toolbar called Recorder.
So one thing you wouldn't necessarily know about the feature that we're about to use is that if you've got three cells, two cells, five cells, any cells more than one highlighted, if you attempt to select them, just the cells that have pure values, you'll only be looking inside that selected range. So perhaps it's a non-intuitive idea, but just click on one cell in a given worksheet and then let's imagine that you've used this sequence before and now you are ready to turn on the Macro Recorder and go through it again.
So let's click this button in the lower-left corner, click and the Record Macro dialog box appears. We need to give the macro a name, and although we could go with the default macro 1, macro 2, why not give it a meaningful name? Macro names cannot begin with a number. They cannot contain spaces. You can use upper and lowercase freely. You can also use the Underscore character and certain special characters work and some don't. Rather than worrying about the ones that don't, just stick with Underscore.
Use Upper and Lowercase. That's what most macro creators use for macro names. So a name here might be, for example, Highlight or Select Number cells. Remember we'd like to highlight just the cells that have pure values, and if you are thinking of values, maybe that's a better word. And if you'd like to accentuate the idea that this is somewhat like a sentence, maybe we'll put a space in here, but we'll use the Underscore character. You cannot use a space. So a Highlight_Value_Cells, meaning numbers. And let's give this a keystroke shortcut. And by the way we have 52 choices here to help you remember this, perhaps V for value.
That might be one, but if we use Ctrl+V, we won't be able to use that shortcut for Paste anymore. So without holing down Ctrl, but holding down the Shift key right now, I am going to press V and we see what happens there. Now let's say we want to store the macro in this workbook. We want to test it out. Now eventually, we'll learn that there might be a better place called Personal Macro Workbook if we want to make this macro available for other workbooks. Let's say for the moment we just want to confine it to this workbook, you want to test it out, work with it for a little bit.
Provide a description? Sure, it's probably the last thing that we do sometimes and sometimes we don't do this. Perhaps the name by itself is good enough. On the other hand if you want to be a little bit more helpful not only to you, but to others who might be using this, maybe we would just start by pasting this here and then maybe filling out a comment or something. We could possibly use Ctrl+C here, down here, Ctrl+V. Highlights. Maybe turned into a sentence that sort of thing. Not critical but on the other hand helpful. Value_Cells and maybe to be a little bit more explanatory here, in the current worksheet because that's what it means.
Now we are ready to click OK and as we do this, we want to remember that during the recording process almost everything we do gets recorded. So if you scroll or you happen to click on a cell, those things get recorded. So as we click OK here, first of all do notice in the lower left-hand corner of the screen before we click, you see the word Ready and you see the icon that we just clicked to activate this dialog box. We'll click OK and now that icon has been replaced with the square that says, "A macro is currently recording. Click to stop recording." So we're in Recording Mode right now, and the steps to highlight the Value_Cells here begin on the Home tab.
So we'll click this and the extreme right button is Find & Select and although we could choose Constants, it would highlight ID numbers and it would highlight text as well, so let's not use that. Let's use the Go To Special sequence. That's not so widely known perhaps, and here we want to choose Constants and then not choose Text, Logicals and Errors. And as we click OK, the only cells highlighted in this worksheet are those that contain values.
And since we wanted them to stand out more prominently and we don't want to lose this selection, immediately we want to go to the Fill Color button in the Font group on the Home tab and choose the background color that more or less makes sense here. Perhaps this one, good enough, and those cells of that color. Now it's not major here but those cells currently are selected and in color. If we simply click in A1 or some other cell you'll see how it changes slightly, and the active cells in one location rather than having all those be selected at once.
So that's all we need to do. Now if I do scrolling right now, that's going to be in the macro. If I go into a command sequence and back out of it and choose nothing, that will not be in the macro but nearly everything else is in the macro recording process. So again, the idea is to stay focused as you do this. We are ready to stop recording and there's the button at the bottom in the lower-left corner. Click to stop recording. We've stopped doing that. We could test it on this worksheet, just doing an Undo. Now you can undo steps you've done manually, but you cannot undo actions taken from actually running a macro.
It might seem a little confusing for the moment but I am going to uncheck this and then go here, and imagine that we want to test this macro. So the active cell is anywhere in the worksheet. I am going to press Ctrl+Shift+V, and all those cells are highlighted. And to test this a step further, I am going to go to the next worksheet in this workbook. It's called Profits, and again the question would be, which cells in this list here have pure numbers? Ctrl+Shift+V, and there we see it.
Just those cells and rows 4 and 5 contain numbers. All the others to the right and below those are formulas. So the recording process is relatively straightforward as you've seen and for many macro users it might be all they need to know as they attempt to automate their daily tasks.
- Running macros
- Understanding the VBA environment
- Storing macros in a Personal Macro Workbook
- Recording macros in stages
- Writing VBA code
- Using loops
- Debugging macros
- Testing in step mode
- Optimizing macros for fast performance