Join Dennis Taylor for an in-depth discussion in this video Recording a simple macro, part of Excel 2013: Macros in Depth.
With certain macros, the thought occurs to you that maybe the macros could be used in other worksheets as well. In this particular workbook called WatchCode.xlsm, there are macros already in this workbook And we're about to record a new macro that applies a feature called double accounting underlining. It's a feature we already have started used a lot let's say and we want to use it in other workbooks. And although it doesn't take that long, we want it to be instantaneous. So, let's highlight a few cells here and show you what the feature does.
It simply is going to put two lines under the text, but we get there by going into Format Cells, either with Ctrl+1 or right-click Format Cells, or the dialog box launcher found in the font group on the Home tab. Font, underline, there's the drop arrow. Double accounting. The two accounting options, by the way here, move the lines farther away from the text. Looks good. We click OK. But we want it to be faster. And we want to be able to do that in any number of different cells instantaneously.
So let's record a macro to do that. And we'll start simply by selecting these three cells here as our test case. And later, of course, we will want to test this out in different locations on one cell or a group of cells. We're ready to record the macro. To the right of the word ready in the status bar, you'll see a button there. If you don't see the button to the right of the word ready, right-click>Macro Recording. So we do see it in this case. Let's click the button, we're ready to start recording. Let's give it a name like double accounting underlining. Double Acct.
Remember, macro names cannot have spaces, they cannot begin with a number, you can shift between upper and lower case, you can use underscore if you wish. We might want to use a key stroke shortcut, Ctrl+Shift+D. And instead of storing it in this workbook, we want to make sure that it's going to be stored in Personal Macro Workbook. Any macro stored there will automatically be available later. And, if this is the first time you are recording a macro here, you are in effect creating the Personal Macro Workbook. So let's click OK, and simply go through those manual steps again.
Remember we can also get into format cells by way of Ctrl+1. There we are again. Underline > Double Accounting, looks good, we'll click OK. The feature's been applied. That's all we want to do with a macro. We're ready to stop recording. After stopping recording a macro, it's always good to not only test out the macro, but also to look at the code. How bout a quick test right here, we'll highlight these cells and press Ctrl+Shift+D, it looks like it's working. With underlining features, you often need click away from the highlighted cells to get the full effect.
That might look like single accounting or single underlining. Click somewhere else and we'll see the effect has taken hold there. So, let's go view the code by way of Alt+F11. Remember, any time your working in Excel, if you want to go to the Visual Basic window, you can press Alt+F11 or you can return to Excel from here with Alt+F11. You don't always end up viewing the code that you want when you press Alt+F11 though. We're in world of Visual Basic here but not necessarily in the right spot. The macro we just recorded got put into personal macro workbook and there we see personal here, recognize its name too.
Personal.XLSB, that's its full name with file extension. We don't see much about it here but there's a plus to the left, click the plus to expand it. Macros are stored in modules, it looks like we have modules here Click the plus to the left of modules. There's module 1, you need to double-click it to see the actual code. And there's the code. Double counting underline there's our keystroke shortcut. Eventually you can clean up this code, it's not really critical to do that now. Most of these options in here are default settings anyway.
But here's the line, of course, that we really are interested in. And that's what actually occurred in the macro. The other settings we might get rid of eventually, but no harm in leaving them there. So every time we run this macro, it's going to apply double accounting. Doesn't do a whole lot but it emphasizes the idea that when we record a macro we sometimes will put it into personal macro work book and we might also at some other point decide to expand this macro. Now the current work book that we opened with, Watch Code, does have some modules in it.
It's not necessarily clear at first why we've got macros in different modules, but let's double-click here on module3. There's a macro there that converts cells to values. Let's see how it works there. We're not going to be using that. There's nothing in it of particular interest right now, maybe. Here's another one here that highlights value cells. I've seen it work before, you haven't. What's happening here at the end of it? It's applying a border feature. Now, when you see that, you say, oh yeah, I sort of get it. You wouldn't have typed it, you wouldn't have known what to have typed, but that applies border features.
Would you like to use that in the macro we just recorded? Maybe it would be a good idea when you use double underlining, to put borders on it. I don't know that it is or isn't, but if you want to do it, fine. Do we really have to record this and do this all over again? No, and we're emphasizing the idea that although much of the macro creation you do will be by way of recording, you can also copy code from another macro. So we're going to highlight this here and copy it either with Ctrl+C or the Copy button or Edit Copy, any of those methods.
And go back to the module in personal for the macro we just recorded. So I'm double-clicking here on module1 in personal. And we'll just put this feature at the end. Just before the end here. Press Enter. And now we'll do a paste, a paste button or edit paste. Ctrl+V, any of those and we've added this code. I'll backspace here to make it look a little neater, that wasn't critical, but the next time we run this macro now it will perform the double accounting underlining feature as well as applying the borders.
So we'll press Alt+F11 to return to Excel, highlight some different cells here, we'll just try it right here maybe, and press Ctrl+Shift+D. And it looks like the features apply, but let's click outside of it. Yes, not only did it do the double accounting, but it also put these borders on as well. A border around the perimeter of all the selected cells. So I've emphasized the idea that we've recorded a macro, and because we wanted it to be available to all workbooks at any time from now on, we stored the macro in personal macro workbook.
And then we decided to add some functionality to it by borrowing code from another macro. Simply copying the code and adding it to our recently recorded macro. Every time we run this macro now, it applies two features. So increasingly, you'll begin to see as you come familiar with the world of Visual Basic how easy it is, not only to create macros by recording, but also adding on to them by copying code.
- Understanding what macros can do for you
- Running macros
- Creating a Personal Macro Workbook
- Recording macros
- Creating nonrecordable VBA code
- Expanding a macro with statements
- Using absolute and relative references
- Testing a macro in Step mode
- Joining two macros
- Using loop structures
- Streamlining macros