Join Dennis Taylor for an in-depth discussion in this video Recording a simple macro, part of Excel 2010: Macros in Depth.
If you're relatively new with recording macros, it's quite likely that the first few macros are going to be short, quick, easy. It's better to understand the small processes first. One question that might occur to you at some point is, do I ever need to change these? We've already suggested the idea that you will from time to time want to do that. We want to expand this idea, and as we work with macros and go into VBA back and forth into the world of the VBA and back into Excel, we want to become familiar with the techniques for why we want to alter a macro and then how we do that.
I am going to take a small process here, create a new macro for double accounting underlining, a feature that some people might find useful. Part of the reason there is I am using this is that it's more multi-step than we might want it to be if we do it manually. If I want double accounting here, I am going to have to go into the-- either the Home tab, or possibly make it as fast as possible, you know, right-click > Format Cells, click the Font button, drop arrow here is Double Accounting. That's the one we want. There we go. Click OK, and there it is.
But why not make it faster? Let's record a macro to do this. I am going to press Ctrl+Z right now to undo that. Let's record a macro. This time we will also emphasize the idea that we want this macro to be available to any workbook, or potentially any workbook. So, as we highlight this we want to be thinking where we are going to be storing the macro. We will click the button in the lower left-hand corner for activating the recording process. Give this macro a name like DoubleAccountingUnderlining. Give it a keystroke shortcut, Ctrl+Shift+U. Store it in a personal macro workbook, the workbook that's always open every time we open Excel.
Once it's been created, it's always open. We will do the Description later. Click OK. We are in recording mode now. We can also get into that Format Cells dialog box with Ctrl+Shift+F. We'll do it that way this time, Ctrl+Shift+F. There's the Font tab. Underline > Double Accounting. Okay, we are done. Stop recording. Let's look at the code. I always recommend looking at the code every time you record a macro, even if you are not going to make changes. It's good to start getting familiar with more Visual Basic.
Alt+F11, this doesn't always take us where we want. Where do we want to go here? This is stored in the personal macro workbook. The downside, if any, of Alt+F11 is that it doesn't always take us to the exact code window that we want. On the left-hand side of the screen, we see Personal, plus in front of the Modules. Double-click Module 1. It's the only one there, and there is the code that just got recorded. If you look around a little bit, you get familiar with what's going on. Many of these settings are defaults.
In a bit, we will show you how you can actually get rid of them just to tighten up the code. It's not necessary; it doesn't hurt to leave them in there either. Here's the key line, DoubleAccountingUnderline. You wouldn't know that, but certainly when you see it, it makes perfect sense. So this macro does what we want. Now, it's sounding a little contrived, but nevertheless emphasizing the idea that sometimes you want to make changes. Maybe when you do this, to make it stand out even more prominently because you are not going to use it maybe in all the locations, you will also want to apply a background color.
You remember that there's another macro out there that you recorded a while ago that applies color to value cells. Let's see if we can find that. Alt+F11, we are back here. We have only one other workbook open. It's called WatchCode. It has some modules in it. Let's take a look at Module 1. That doesn't contain the macro we are talking about. How about Module 2? This is the one; it highlights cells on all worksheet that have values. After selecting those cells--that's done here. Once again, a line you would never guess what it is, but having recorded it, you begin that recognize that it has some kind of a code. It's making constants, it looks like.
But all of this code here is simply about applying a color, a solid color, to the selected cells. Now we could, if we wanted to, record another macro that just applies the color and then take that code and put it into the other macro. But here the code is already, so what are we going to do with this? Let's just copy this code. Now remember, this is from a workbook called WatchCode. It's a macro that was recorded there, and this portion of the macro simply applies color.
We are going to copy this with the Copy button and then jump back to the macro under PERSONAL. It's in Module 1. This is the one we just recorded. We want to alter this macro by saying that not only will it underline using DoubleAccountingUnderlining the selected data, but after doing that it's going to apply a color. So at the bottom here after End With, I just pressed Enter, click here, and now I'm going to press either Ctrl+V or the button in the toolbar up top, Paste.
And there is the code. Now, we could click Backspace here; that's certainly not critical. But here's the code that got copied. That's the portion of the previous macro that applies the color. Here's the macro that we just recorded to apply DoubleAccountingUnderlining. Now, although not a heavy-duty use of the concept, it does remind us that there will be times when we need to change a macro, and certainly one way will be by copying code from another macro. So I am going to press Alt+F11 and try that revised macro right here.
Control+Shift+U, and there it is, and you see what's happened. A minor use of it, but it's opening the door to the idea that we've got control over the Visual Basic environment, and when we record macros, there will be times when we want to make some changes.
- 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