Macros for Beginners:

Hello everyone and welcome to the first installment of Alex’s Excel tip o’ the day! You all can also let me know what kind of questions you need answered, and I’ll include them in a future post. If you have a tip to share with the world, send it in . . . I’m not a glory hog. Those of us with mind-boggling cell slinging skills don’t need to be. Just don’t be surprised when I take credit for it. 

Our first tip o’ the day is macros. Macros are those wonderful little programs that make life easier in excel and enable us to get that beautiful warning message every time we open a file asking us to disable them before someone takes over our computer and uses it to sell “that little blue pill” to Eskimos in Nigeria via Western Union. Commercial opportunities aside, macros are powerful and versatile tools to have in your toolbox and suitable for jobs big and small. This issue is going to talk about those small jobs (quiet down in back!). First we’ll use the macro recorder to create a new macro that will automate a rather common task that annoyingly does not have a keyboard shortcut. Then, we’ll take a look at the vba editor to modify our macro.  

First, open up a new workbook where we can play. Now is probably a good time to let you know that I’m using the newest version of excel. This means my screenshots will mean absolutely nothing to many of you out there at this time. I apologize for that, but I’m not going to upgrade to a previous version just to make it easier to understand me. Part of being an excel guru is maintaining a certain mystique. Don’t worry, each of you will learn to cultivate this mystique as well. For now, just try to figure it out where to find everything whether you use toolbars or ribbons.  

After you have your new spreadsheet, type in some stuff in a random cell. It doesn’t matter what it is, but I typed “goofball” into cell C3 and I’ll be using this in my example going forward. Like just about anyone else, I’ve found myself repeatedly formatting the word goofball as Garamond font bold 18 points with Red background and white font color. So, let’s set it up so I can use a keyboard shortcut to do it all for me.

First, select the cell C3. Next , hit the Record Macro button on the developer ribbon (see fig 1). You excel 2003 users will need to use the macro toolbar. If you don’t see it, select it via view -> toolbars.  


 
The dialog box in figure 2 shows up. Here we can make up a name (I used GoofballFormat), assign a keyboard shortcut, give excel some info about where to keep the macro, and add a description. First off, give your macro a name; I suggest making it descriptive in order to make maintaining a lot of macros easier. Second, you may assign a keyboard shortcut. Don’t use something like ctrl+c because that’s copy. Be unique. In order to get the cool Ctrl+Shift+ you see below, just type a capital letter in the field. Really important if you want to use this macro in all your workbooks – store the macro in your personal macro workbook by selecting it from the dropdown menu below the shortcut assignment. This will make the macro available to you no matter what workbook you’re using. Click OK to keep going.


 
Here’s where we get to the meat of the macro. Whatever you do from now until you stop recording will go into your macro, so no typing bad names for your boss here, folks. Let’s right click on the cell containing goofball, select format cells from the context menu that appears and pick the following from the dialog box: font bold, Garamond, 18 pt, font color white, fill red. Click Ok. Right-arrow once (just so we have something to remove from our macro later). Next, stop the recorder by clicking the stop recording button as shown in figure 3.


 
Now let’s take our macro for a test drive. Type “goofball” into cell C6 and press enter. Select cell C6 again and hold ctrl+shift+T all at the same time and . . . woohoo! Your goofball format has been applied!
Notice excel automatically selected a cell way the heck out in Timbuktu? That’s because of the little right-arrow we did at the end of recording our macro. Let’s pop the hood and take a look at the engine in order to get rid of this undesirable behavior.
First, we’re going to open the visual basic editor (figure 4).  


 
In Excel 2007, click the visual basic button in the developer ribbon. In all versions, you can hit Alt+F11 to open the VBA editor. You’ll get the window shown in Figure 5.


 

On the left hand side, you can find the navigation window (top) and the properties window (bottom). Use the navigation window to select your personal.xls and expand it. Expand the modules folder and select the last one you have. You should be looking at the code behind the macro we just recorded. If not, try opening different modules until you find it. Don’t worry about the code itself. VBA is pretty descriptive, so we’re just going to look for the part that says something that sounds like it’s selecting a cell, and since we did the right-arrow as the very last step, it should be at the very end of our macro. Lo and behold, it’s the line just before “End Sub” phrase (ironically enough, that phrase ends the macro). Delete the line that says Range(“D3”).Select (or something similar). Save and close the editor and return to our test workbook. Type “goofball” one more time in cell C8, hit enter, and then select C8. Run our macro (Ctrl+Shift+T) and . . . Excel didn’t move the selection anywhere else. Bravo! You’re ready to record some more macros to perform simple, common tasks.

Where to go from here? Macros are great ways to automate both simple and complex tasks, but complex tasks can be difficult to code using the recorder and then editing the output. If you’re interested in expanding your visual basic chops, there are about a billion books about excel and vba. Run down to your local bookstore and check them out before buying to make sure it covers what you want to cover in a way you understand.

That’s it for today, folks. Submit your ideas for the next post and I’ll be happy to oblige. Until then, happy cell slinging!