Join Dennis Taylor for an in-depth discussion in this video Understanding what a macro does, part of Excel 2010: Macros in Depth.
When using Excel, do you sometimes say to yourself--or maybe even out loud--"here I go again," as you repeat a frequently used command sequence or maybe a series of steps--something you do frequently? If so, you need to learn how macros can automate these steps. Let's take a look at a few examples, and I also want to suggest that, in a certain sense, you've been using macros all along; you just haven't thought about it in that way, or you maybe haven't used that word. Let's take a simple process here. Maybe in this list here, when someone leaves the organization, at least for a while, what you don't do is remove the record.
You simply want to use strikethrough. In other words, Madelyn O'Brien here has left; I want to use strikethrough on these cells. Most of you probably know where to find that feature. You'll go to the Home tab, and possibly click the Dialog Box launcher right here. You might know a shortcut by pressing Ctrl+1, or you could right-click and go to Format Cells. The point is, it's a few clicks here and there. You will track down this capability. If you're going to Format Cells, you might have to activate the Font tab if it's not already active, and there is Strikethrough.
Now, I wouldn't suggest that a lot of people will use that feature frequently, but you could imagine if it becomes pretty important to you, and you use it a lot, you'd like to make it faster. And you certainly can use a couple of shortcuts like I suggested here. Maybe click this next time, or press Ctrl+1 to get into that dialog box a bit faster. But it still is going to to take a few clicks. And then one day somebody tells you out of the blue "Oh, by the way, there is a keystroke shortcut already built into Excel, Ctrl+5 and furthermore, it's a toggle; it's an on/off." We don't call that a macro, but in a certain sense it is.
It embodies the idea that a short sequence, in this case a keystroke shortcut, takes the place of those four or five clicks that we just went through before. It would be incorrect to suggest that macros are simply about short, little processes that are going to save you five steps here or five steps there. But we start with this idea that anytime you want to take a series of steps or actions and essentially turn them into a single action, that's the starting point for talking about macros.
At other times we do this as well too. With so many different features, we have shortcuts already. If you didn't know Excel very well, and you said, "I want to make this bold," I certainly couldn't imagine anybody using Excel and not knowing about the letter B, but if you were using the Data tab for whatever reason, and you couldn't find it, you'd be complaining a little bit saying, "I got to go back here." and "What's the sequence?" You'd think out how many steps it takes you to get to a certain feature. Let's go into things that maybe are not quite so well known and suggest more ideas regarding macros.
Here is a worksheet called Formulas, and it's a real mess. It needs some cleanup, and maybe you encounter worksheets like this from time to time, and you need to get your bearings kind of quickly. In prior versions of Excel going way back, it used to take quite a while to figure out where the formulas are. What I'm about to do here is going to be fast and easy. It probably doesn't require a macro, but sometimes you get annoyed at processes that even take only three or four steps. How to we find all the formulas in this particular worksheet? On the Home tab, the extreme right button called Find & Select, there is a choice called Formulas.
It highlights all the formulas. Now you might want to add a color to them. That would take another step. Make them yellow or something like that if you wish. But that's certainly a handy feature. If you use it often, does it bother you that you always have to go to the Home tab, then click that button, then make the choice, then add the colors? Well, certainly the steps that we just went through could be turned into a macro. So what I'm suggesting here, at least initially, is the idea that any sequence of actions could be turned into a macro by a process known as Recording.
We can turn on a recorder and go through those various steps manually while they're being stored for us. Then in the future, we can either use a keystroke shortcut or a new button in the Quick Access Toolbar to actually execute that sequence with a single action. I'm going to click Ctrl+Z to undo this. A companion feature to this would take considerably longer. I'm not necessarily suggesting this is a feature that everybody needs, but it certainly could be valuable in some worksheets if you say, I would like to highlight all of the cells that just have pure values in them. That cell's got a pure number in it.
So does this cell. Not that cell, that's got a formula. We already know how to find those. So how do we find the cells that have pure values? It's important in both of these examples that you click on just one cell. If you highlight two or three or five, the search will only take place within those cells. So we click on the single cell here, and again on the Home tab, Find & Select. This time the not so obvious choice, Go To Special.
We see a choice here called Constants, but that will also select text and logicals and errors, so then we would uncheck Text and Logicals and Errors, and click OK. What do we have highlighted? Just values. And here, too, if you would like to apply a color, go do that. So the issue is, is this the kind of sequence that maybe you've already been performing from time to time, and you start to use it more frequently, and it's six or eight or 10 steps-- we're not really counting here-- you say, I wish I had a faster way to do that.
That's the starting point for certain kinds of macros. For want of a better term, perhaps we would call these "utility macros," but that's certainly one thought process that we have here. But let's suggest another idea as well. Suppose a couple of times a week it's important for different people in the organization to get a list of the employees and who they are and where they work and so on. Maybe every time you do this you sort the list first by Department, and then you sort it again by Employee Name.
There are a few people that need that list. Maybe another thing you need to do is to use a filter on this list and show only the hourly people. Now I'm not just suggesting make-work projects. I'm suggesting these are things that you do on a regular basis. Maybe you do that twice a week. And it doesn't take that long really, but it's five, ten minutes, whatever. What if you could click a button, and have all those steps essentially run for you? It's going to save you some time. That's the idea behind macros.
Initially, we want to focus on the idea that any actions that you take in Excel could be recorded so that the next time you need to use them, those actions happen all at once. It even can be set up in such a way that no matter how big this list becomes, or whether it shrinks or grows, the macros will still work. So we're giving you some ideas for the rationale of macros. Why do I need macros? To automate your work and save some time--and sometimes lots of time!
- 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