What Is An Excel Macro?
Macros are pieces of code written in VBA (or Visual Basic) usually designed to automate repetitive tasks.
First, A Warning About Excel Macros
At one point I built macros for things like my number formatting, and assigned a convenient keyboard shortcut. I also did the same for some basic data cleanup and formatting for reports coming out of the General Ledger. I thought I was brilliant. I was sadly mistaken as Excel macros (for me) have a major flaw: you lose the ability to Undo (on all open workbooks). You can imagine how I felt, having 10 workbooks open, in the middle of experimenting with several of them, when I realized I couldn’t undo the experiments and destructive mistakes I had just made. Sure, I had saved a few minutes by formatting my new file with the macro, and it sure looked pretty. But I was in deep trouble with my other files. I couldn’t get back to a working version of those files. It’s because of stories like this that I am very hesitant to embrace macros. It would be great if Microsoft were to fix this limitation.
I do make one exception. If I have zero workbooks open (or I’m not concerned about Undoing anything), and then I export a big data sheet into Excel, then I have a special macro that saves me time. I use Ctrl+Q to add data filters and then bold and freeze the top pane. This is how I like my data formatted and set up. In this scenario I’m not risking anything – no other open workbooks, and nothing that I would want to undo in my open data sheet.
You may decide to use macros more often than me. They can certainly save you a lot of time. And they can be used for tasks that are far more complex than my simple example below. Just be fully aware of the risk you are taking — and take the necessary precautions (for example, always check your file before you run your macro).
How To Create a Basic Excel Macro
How do you create a macro? Pretty easy. In the Developers tab, click on the “Record Macro” icon that you see below:
Once you click on “Record Macro” you get the following dialog box:
Here you can name it and assign a keyboard shortcut (note: this will override any pre-existing keyboard shortcut, including those defaulted by Microsoft). I chose Ctrl+Q because it’s a convenient left hand key and Excel 2010 hasn’t reserved it for any other shortcut (same with Ctrl+E, J and M).
Once you type in the name and shortcut key, click OK and you basically start recording your macro. Excel records the exact keystrokes you make, and when you eventually execute the macros it simply repeats what you did when you were recording. In my hypothetical recording, I recorded Bolding the first row, applying Filter, and then applying Freeze Panes to the first row.
When you’re done recording your macro, go to the Developer tab again and click Stop Recording.
Those are the basics.
At this point, Excel has recorded your macro. Once you save it (I will discuss this shortly), you can execute it just by typing the shortcut. In my example above, if I hit Ctrl+Q for my macro shortcut, Excel will Bold the first row, apply Filters, and apply Freeze Panes to the first row — all in the blink of an eye.
Remember that you will not be able to Undo after this. So you may want to double check your file before you execute the macro.
For shortcut macros that you want to be able to use in all files, I recommend sticking with Excel’s default of “Store macro in: Personal Macro Workbook.” The macro will then be stored in a special file, PERSONAL.XLSB, which is the Personal Macro Workbook. Storing the macro in this file allows the macro to work in any other open workbook, even if it’s not a “macro-enabled” workbook. By default, PERSONAL.XLSB opens each time you open Excel, but remains hidden. (Note: PERSONAL.XLSB is only created once you create a macro that is stored in it). PERSONAL.XLSB must be open for the macros stored in it to work on other workbooks.
That’s it for creating a basic Excel macro.
In Part 2 I’ll show you how to edit and delete basic Excel macros.
In Part 3 I’ll review some issues with Personal.xlsb, and show you how to add a macro to your Quick Access Toolbar (QAT).