Hands On: Make your first macro

When readers ask me how to do something in Excel they frequently precede the request with the phrase, “How do I create a macro that…”. Invariably I reply that a macro is already built in to do that job.

For instance, if you enter Jan in cell B1, point to the cross in the bottom right-hand corner of the cell and drag it to cell M1, the months Jan through Dec will appear along row 1.

If you enter ABC1 in a cell, point to the cross and drag the cell down the column the range will fill with ABC2, ABC3, and so on.

Such replies are all very educational about the features, functions and facilities provided in Excel but I wonder if I’ve been missing the point of the questions. People are fascinated by macros. It can be very satisfying to be the wizard who creates the magic of a series of actions happening by themselves.

There are two principal ways to make a macro. One is to record the keystrokes you make and have the code written automatically. That’s only suitable for short macros. The other is to write the code yourself.

You could have a macro that changes the default worksheet names to the months of the year, adds enough sheets to complete the year if necessary, sorts them in chronological order, and puts any other worksheets at the end of the series. Let’s use that as our example here.

The first decision is where to put the macro. If you put it in the workbook that will have its tab names changed then it’s only available in that workbook, or if that workbook is open.

One solution is to put all your macros in a file created as a library of your macros. You could call the file MyMacros.xls or MyMacros.xlsm with Excel 2007, but you would have to arrange for that file to always be open. Ideally you should be able to start Excel, open a new workbook, press a keyboard shortcut and the tab names change immediately.

Excel lets you do that with a Personal Macro Workbook. If you have one it will probably be in the path, C:\Documents and Settings\User\Application Data\Microsoft\Excel\XLSTART.

Unlike a MyMacros library file, it will remain hidden when you open Excel but its macros will be available.
If you don’t have a Personal Macro Workbook, then it’s easy to create one. In any version of Excel prior to Excel 2007, click on a worksheet. Under the Tools menu choose Macro, Record New Macro, pick Personal Macro Workbook, OK. Click the Stop button. You now have a Personal Macro Workbook.

With Excel 2007, click on the Office button and then the Excel Options button. Under Popular, Top Options, check the box against ‘Show Developer Tab in the Ribbon’. Click on Record Macro under that new tab. In the displayed dialogue box, enter a name for your macro like MonthTabs; a shortcut key like Ctrl, Shift and M; and a description of what the macro does like, “Changes sheet tab names to months of the year”. Under, Store macro in: click the down arrow and choose ‘Personal Macro Workbook’. Click OK and then the Stop Recording button.

Introducing VBA for Excel
Excel macros are usually written in a tailored variation of the language called Visual Basic for Applications, or VBA for Excel for short. It has its own editor which can be opened by clicking on a worksheet and pressing Alt and F11.

The name of your new Personal Workbook file will now be displayed in the panel on the left. In Excel 2007 it will be called, VBAProject (PERSONAL.XLSB). Open it to display Module 1 and the details you previously entered in the Record Macro dialogue box will be shown on the right. The top line will be “Sub MonthTabs()” and the bottom line “End Sub”. The details in between will be preceded by a single apostrophe and displayed in green. This indicates the words are just notes and not a part of the macro. You can delete these notes if you like without affecting the macro.