Join Dennis Taylor for an in-depth discussion in this video Definition and examples, part of Excel 2007: Macros in Depth.
When using Excel, you sometimes say to yourself, "here I go again," as you repeat a frequently used command sequence or a series of steps. If so, you need to learn how macros can automate these steps. Let's take a look at a few examples based on the idea that any series of steps you take manually could be turned into a single step macro. In this example we're looking at a worksheet called CurrentStaff. Now, let's start with something just basic and small.
First of all, if I want to make this cell here be underlined, I have the Home tab active, I'll click Underline. Now not a big deal there. Maybe I will do this in different cells. Now, if there were no Underlining button, what would I do? I probably have to figure out that we'd click this button here. We'd have to click the Font tab if it wasn't already clicked, and then we'd find Underline in this drop arrow and maybe choose a different type here, but let's say single underlining. We'll click that. Now, almost anybody who uses Excel learns early on that there is an Underlining button.
So the idea might say well, isn't this like a macro? Yes, it is. We don't call our buttons in the Ribbon macros, but in a certain sense they are. Practically every button that we use is a shortcut for a series of steps, Whether it's Bold, Italic, Underline, the frequently used ones or others. How about the idea that maybe in this organization here when someone leaves the group, you don't immediately remove their name, but what you do instead is you highlight the data in question and you apply Strikethrough and one way to do this of course is to click this arrow, the Dialog Box launcher, in the Font group of the Home tab and then on the Font tab you will see Strikethrough.
Click OK. Now, if you start to use that feature a lot, you are going to get a little annoyed here and there, because it's just too many clicks. Then one day, some wise guy tells you, "by the way there is a keystroke shortcut for this." It's Ctrl+5. Now, in a certain sense, that embodies the idea of a macro, and it even goes a step further, because it's a toggle action. Ctrl+5 will apply or take off Strikethrough. So the concept of a macro in certain sense is already embedded or embodied by the idea of the buttons that we've gotten familiar with over the years in using Excel.
So the general idea, at least as we get started here, is to be thinking of those things that we do frequently throughout the day. Now, we're not going to limit our idea of macros to just short seven step, five step sequences that we want to automate, but the idea that the actions that we take frequently could be automated, sends a spark into some of our minds that says "we can be more efficient in Excel." So we've suggested some tasks that could save you time, increase accuracy and maybe reduce the drudgery if you could turn them into macros.
And looking ahead into something a bit bigger too, imagine if this list here is something that you update frequently and other people need copies of it. Maybe right now two or three times a week, you sort this data by department and you print two copies, because certain people need to see that. And then after sorting it, you sort it by employee name and five people need to see that, so you are going to print that five times. And then maybe you are familiar with Excel's Auto Filter and you will filter this list to show just those people who are full time, and you will print that list, and maybe what you also do is sort the list again by department, and if you're familiar with Excel's subtotals capability, you might insert Sub-totals and collapse that, and print that or copy it.
So you can imagine how if you do that sequence and go through those two or three times a week, you might say, "gee!" "The next time, maybe if there were a way, if I could somehow record that activity, those 47 steps or whatever it is," maybe write them down even or maybe you know them well enough to go through them manually. By turning on a feature known as the Macro Recorder, you will be able to take that sequence of actions, record them once and then in the future, just press a keystroke shortcut or a new button perhaps in the Quick Access toolbar.
So we've suggested some tasks that could save you time and reduce the drudgery of day-to-day work that we don't always like. Using macros will increase accuracy as well. And it's going to make your use of Excel a lot more pleasant.
- 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