Excel Macros

Well, I've finally gotten into the swing of things at work ... it seems like it's been a long road getting here! I've somewhat forgotten how manual things can be when there's no automation. For example, entering financial information into a workpaper or a model by hand after you've printed out an SEC document. Presumably, it should be straight forward enough where the worker-bee would not have to put too much effort into data entry, but moreso on the analysis of such data. Unfortunately, my thought is that this is the exception rather than the norm (later we'll discuss XBRL which hopes to change this ... so do I!). This is where VBA and macros can help (amongst other tools that I will get into in later posts). In this article, I would like to give a basic example of how to use a macro to your benefit (nothing fancy).

The thought of this came to mind when I was reading the JoA from this past month. A subscriber had sent in a question with respect to how could he easily put the file name and file path on most of his worksheets. The author astutely noted that since the person wanted this custom footer on most of his worksheets, he should modify Excel's default template (i.e., the empty window that is automatically openned when you hit "File | New" or hit "Ctrl-N"). This was a great answer for someone who wants EVERY new workbook that they create to have this footer - but what if you only wanted it on (for example) half of your worksheets? Then this answer might actually cause more trouble than it's worth; hence, a macro would be much more appropriate.

To create a new macro, you simply go to "Tools | Macro | Record New Macro...". You will be prompted for a name for the macro, a place to store it, an optional shortcut key, and finally a brief description (if you so choose). Typically, you're going to want to store new macros in a "Personal Macro Workbook", which creates a new file that is available whenever Excel is open. You would choose "This Workbook" if you only needed the macro for that particular file (no other files).

So, now that we know how to create macros, what exactly are they? To put it simply, they record keystrokes that you type after you enter all the above information. So, just so that we can finally get to the end of this article and actually learn something useful, let's create our "add_footer" macro (notice there are various naming rules that you MUST follow or Excel will get very angry). The following are the steps you'll need to follow:

1. Tools | Macro | Record New Macro...
2. Name it "add_footer" (without the quotes) and store it in your "Personal Macro Workbook". We won't add a shortcut key yet (and you'll find out why later), but you can add a simple description if you want.
• NOTE: remember that everything you type after this point will be recorded in the macro!
3. Now, to add the footer: simply proceed as normal (using ONLY the keys you would to create the header). That is, go to "File | Page Setup" and then go to the "Header/Footer" tab and add your custom footer. You can add "&[Path]&[File]" to get the pathname and file in your current sheet.
4. Finally, you want to hit the little box in the macro toolbar that came up to "Stop" recording the macro. (Alternatively, you could stop it by going to "Tools | Macro | Stop Recording").

Now that we've created our macro, we want to test it out. Open up a new workbook and then go to "Tools | Macro | Macros..." (or just hit Alt-F8 for faster access) and pick your macro from the list. Then hit the run button, and voila, the footer is there! Note that I didn't add a shortcut key (although feel free to do so if you choose). The reason for this is simple: I use virtually every shortcut key that Excel offers, and I rarely find a macro that is worth overwriting. For example, you might choose Ctrl-Y as a shortcut key - but that is for the "Redo" command (after you've "undone" something). If you don't use shortcut keys provided by Excel, you should definitely start; that alone might double you're productivity (complete guess, but might very well be accurate over the long term). Anyway, that's why I usually don't assign shortcut keys to macros.

If you want to add it to multiple sheets at once, simply click on the starting sheet and then hit the Ctrl key before selecting any additional sheets you want to add. It's that easy!

Macros can be used for a variety of things, and this is only a VERY simple example. Anything that you do on a repeated basis (e.g., change numbers to Currency with the $ symbol and 3 decimals and then change the font to blue 8pts) can be automated with macros. Once you get used to using macros, you can then move on to more advanced usage via VBA (see the "Visual Basic Editor" under "Tools | Macro"). We will get to such examples later on ... good luck for now!