How to start building a macro arsenal

Face it, macros will make your life easier. You might not know how to write them yet but it shouldn’t deter you from using them. This post will make it easy for you to start collecting macros and keeping them handy for when the need strikes.

First, you will need a personal macro workbook. Macros are stored in workbooks and I’ll give you three types of workbooks:

  • The plain old workbook
  • The personal macro workbook
  • The add-in, which enables easy distribution of macros and functions. I won’t get into any more details on the add-ins here.

You should only store in the workbook macros that are either only relevant to that specific workbook, or are triggered by specific events on the workbook, or that need to accompany the workbook if you were to send it to somebody else.

All other macros should go to your personal macro workbook.

Do you have a personal macro workbook? If you don’t know the answer, it’s probably no, but I’ll show you how to set it up. The easiest way is to record a macro. A few options for this:

  • Use the shortcut Alt+t m r (for Tools \ Macro \ Record).
  • Click the Record Macro button on the Developer tab in the ribbon. It’s not installed by default, but you can right-click the ribbon, “Customize the Ribbon” and check the Developer box in the Main Tabs window to install it.
  • Right-click the status bar on the bottom left of your excel window (it should say Ready) and select Macro Recording, You now have a new status bar button to start (and stop) recording macros.

Once you’ve picked one of those options, you’ll see the following window:

Record Macro window

In the Store Macro In dropdown, select Personal Macro Workbook, then click OK. A small blue square (Stop Recording) will appear on the bottom left of your screen on the status bar. Click it (or use the same shortcut Alt+ t m r) to stop recording the macro.

You now have a personal macro workbook, stored somewhere in the depths of your user profile (something like C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART depending on your version of windows). To see it, you’ll have to venture in the Visual Basic Editor, using the Alt+F11 shortcut, or press the Visual Basic button in the Developer tab of the Ribbon (see above). Expand the VBA Project (PERSONAL.XLSB), expand the Modules and you’ll see the first Module of your Personal Macro workbook, as shown in the screenshot below. Double-click to view the code, add any macros you want, and don’t forget to save.

Visual Basic Editor Screenshot

You can now close the Visual Basic Editor, your Personal Macro workbook should open whenever you open Excel and you can start filling it with wonderful macros!

Try it, you might like it.