Join Dennis Taylor for an in-depth discussion in this video Identifying the need for macros, part of Excel for Mac 2016: Macros.
- [Instructor] One of Excel's most powerful tools is a macro. Kind of hard to define without an example. But the process begins with a simple recognition that you have identified certain steps or sequences of actions that you take frequently, you would like to automate them. In a certain sense, we've been using macros all along. We'll get to that in a moment. Let's just start here with a simple idea. Maybe you've started to use a certain feature frequently. I'm going to highlight the information here for Robin Hutchinson, she's just left the organization.
What we've decided to do in this worksheet is, every time someone leaves, we simply indicate that person's left by using the feature called Strikethrough. Later we will get rid of the name, we will get rid of the record. How do we apply Strikethrough? Well we need to got to Format Cells, it's one of those features you might expect to find in the font group on the Home tab, but it's not there. How do we get to Format Cells? Either by pressing Command + one, or by right clicking within the selected area and choosing Format Cells.
And when we get there, we might have to click on the Font tab, if it's not already selected and then we'll see Strikethrough. Okay, we apply it. If at a later time if we need to remove it, and it's too late to do an undo, we'll have to highlight those cells again and come back here again. This time I'll press Command + one, back to the Font tab, let's remove Strikethrough. Then one day somebody happens to mention, by the way, did you know there was a key stroke shortcut for Strikethrough? Really, what is it? It's Control + five, and furthermore, we can also use it for removing Strikethrough.
It's going to be really handy. Just like a little macro. We don't call it a macro, but it encompasses the idea of a macro, which is a single action that comprises multiple steps, or takes the place of multiple steps. And in a certain sense, we've all been using macros for years, although we simply don't use that term as we consider the various buttons that we see in the ribbon menu system. It's hard to imagine using Excel without the Bold button here. Or its keystroke shortcut Control + B.
But, if we didn't have that feature there, what would we do? We'd go to Format Cells. And then we would apply bold this way. And that certainly is cumbersome. I think a lot of us would get pretty upset at the lengthy process, particularly if we use that frequently. But we've also got the keystroke shortcut and the button in the ribbon. So you could make a case for saying, every button in the ribbon is like a macro. It's one action that comprises multiple actions. Now, let's expand this a little bit.
I'm going to go to the next worksheet over called Formulas. Now, as it turns out, maybe I've become the worksheet guru in my organization for quickly scoping out worksheets. We have a lot of activity in our office. A lot of time we get spreadsheets, worksheets from other sources. We're trying to figure out what's going on and it sure would be handy if we could figure out where the formulas are. How do we do that in Excel? Could we go to the Formulas tab here? Can we find the formulas this way? This says Show Formulas, looks like it would be handy, but it doesn't really highlight the cells that have formulas.
Is there another technique here? How about going to the Edit command in the menu and choosing Find, Go To. Not exactly obvious as a place to search. Once we come here, to this dialog box, the Go To dialogue box, we can click Special, and then Formulas, and then Okay. And then maybe we'd like to apply color. Home tab, here is the Fill Color icon bucket, right there. Be sure to choose a light color if you are using this so you can see the numbers through this.
I'm going to use this orange color right here. And just to make it slightly better, because these cells are selected and they have color, to see a little bit more clearly, I'm going to click outside of the cells here. Now, imagine, if we could go through those six or seven steps with a single keystroke shortcut, or command way to get to those with only a couple of clicks, that would be really handy. Now again, the impetus behind all of this is your recognition that you have begun to use this feature frequently. Or you're pretty sure you'll be using it often.
You want a keystroke shortcut, you want a quick way to go through those steps. Anytime you find any sequence becoming a little annoying, a thought process leads you toward thinking about macros, let's automate our work here. Here's another idea, maybe a little bit longer in concept. Back to this first worksheet called Current Staff. What if we say, once or twice a week, we sort this data, and after sorting it, we print five copies of it. Then we sort it a different way.
Maybe we sort it first by department, later we want to sort it by employee name, and after that we'll print three copies. Because these need to be seen by other people in our organization. We might then come back and run a filter that only shows the hourly people, because maybe we need to get that list to the manager of hourly employees. And you can imagine other scenarios here of manipulating the data. After sorting the data by department, we might want to insert sub totals, collapse that show sub totals by department and so on. We've identified the sequence that might take five or ten minutes.
We want it to take a minute or so. Not always counting and tabulating the difference there, but the more we work with the concept of being able to automate, the more we realize what a huge time saver it might be. So we might start small with the idea of a macro, but at different times, you'll see that sometimes working with large amounts of data and manipulating data in multiple ways, could be recorded. And the process begins simply with the recognition that we know that we're performing something frequently enough, that we'd like to make it faster.
There's another thought about macros you should be aware of. Some macros, for example, the one we suggested over here, highlighting formula cells, could be something that we would use in multiple workbooks, maybe any workbook. In other words, the thought is, this is a macro I'd like to have available all the time, not just for this workbook. Another kind of macro, one I suggested over here, one where we would sort this data and print it, then sort it again, do some filtering and so on. Maybe would be applicable only to this workbook.
When you create macros, in other words, when you start the process of going through these steps and turning on what's called the macro recorder, you will say in effect, I want this macro to be stored only in this workbook, or possibly, I want it available in all workbooks. The dominant method of creating a macro is called Recording. In other words, you have either written down or you remember the steps that it takes that you want to automate and you turn on the feature called Macro Recording.
And you actually go through the process while recording, doing those steps one by one. That's the dominant method. And when you complete the recording process, in the background what has happened, unbeknownst to you, perhaps at least initially, is this idea that every step you have recorded has been converted into a programming language and stored as a program. We also don't use the work program as we talk about macros, but the steps that you have recorded are recorded into a language called VBA, Visual Basic for Applications.
And later in this course, we'll talk more about that and what it means. So, the whole idea behind a macro is, you want to automate work. Again, it starts with the recognition of just simple, repetitive processes that you'd like to make faster it may eventually expand into something much, much more powerful and involve many, many steps you're going to be saving once you become familiar with this process.
- Working with existing macros
- Creating macros with the recording process
- Viewing and modifying VBA code
- Running a macro from the Developer or View tabs
- Running a macro from a keystroke shortcut
- Running a macro from an object or picture
- Creating and updating the Personal macro workbook
- Recording a formatting macro
- Expanding a macro with an IF statement
- Creating interactive macros
- Stepping through macros