Excel Tutorial - How to create macros

The best way to get started creating a macro is by recording your actions. Even Power Macro users will use this as a fallback method for creating a macro. In this particular worksheet, we've decided it's important to us. We're 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 and 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 as 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're 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. 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's 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'll 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're ready to do recording. In the lower left corner, I'm going to click that button, and here's a dialog box. Record Macro. We need to give the macro a name. We always are presented with a default name, but we want it to be meaningful. The name can be pretty freeform, 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 look somewhat like a sentence, that's fine, but use the underscore character or simply shift between upper and lower case. Ideally, we want this to describe what the macro does. As you see more and more macros, many, many times, a good name helps define and describe what the macro does. Why not highlight value cells? I'm not using underscore. I'm just shifting case here. A keystroke shortcut is extremely value, and it's a fast way to run a macro later. Make it happen. We have only 52 choices here. Any of the lower case letters, any of the upper case letters. If I were to use the letter C here, thinking of the word cells, I would not be able to use control C for copy anymore. There's a tendency amongst some users to simply use the shift key, because these combinations are not widely used. I'm holding down the shift key here, and maybe I'll use the letter H. I'm thinking of the word highlight. You can't always come up with the letter that you always want here. Control 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 control C. Down here, I'll press control V to paste. Maybe do this, highlight value cells, and maybe we'll just add the phrase, in this worksheet, because that's what it means, 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. As I click OK, and you'll notice in the lower left corner of the screen, the previous icon has been replaced with a box. A macro is currently recording. Click to stop recording. Nearly everything we do will be recorded. Now, if I do scrolling like this, 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's 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 and select, go to special, constants, uncheck text, logicals, errors, click OK, let's apply a color, maybe we'll make it green. And although it's not critical, right now these cells are highlighted and they're in color, it's a convention to click outside of it, maybe we'll just click on cell A1. And they are no longer highlighted, but the color is there. We're done recording. Lower left corner, there's 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 that 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. And it looks like there's some color already there, why don't we take it off 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 used control, 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? Control, 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.