Exercise: Record and Run Simple Excel Macros

In our previous post, we looked at the steps to record and run a macro.  Practice makes perfect, or at least helps us improve, so let’s do an exercise.

We will record a simple macro to format the current cell in bold, italics, and single-underscored.  Then it will move one cell to the right, ready to run again. (Yes, we could select and do all the cells in one operation, but that would defeat the point of the exercise.)

1.     Prepare the Spreadsheet

• Run Excel
• Open a blank worksheet
• In row 1, enter some text that you want to use as headings, for example:

• Save the file as MacroDemo.xlsx

2.     Preview What You Want to Record

Before recording the macro, let’s practice what we want to achieve. 

• Check that cell A1 (our first heading) is the selected cell.
• Then, on the Home ribbon, click bold, italics, and single underscore. Or use the shortcuts Ctrl+B, Ctrl+I, and Ctrl+U. 
• Now select one cell to the right: Click on cell B1. Or press the Right Arrow key.

3.     Record the First Macro with the View Ribbon

• Check that the selected cell is B1 (our next heading – we used A1 above).
• Go to the View ribbon.  In the Macros group on the right, select the Macros pull-down menu at the bottom (not the Macros button at the top).  Click the Record Macro command.

• Name the Macro Heading1.
• Set a shortcut key Ctrl+Shift+H (you press only Shift+H here, of course).
• Store the macro in This Workbook and add a suitable description with the date.

• Click OK.  You are now recording, so only do the actions as instructed:
• On the Home ribbon, click bold, italics, and single underscore. Or use the shortcuts Ctrl+B, Ctrl+I, and Ctrl+U. 
• Select one cell to the right: Press the Right Arrow key.
• Stop Recording.  Use the square icon on the Status bar.  Or, in the View ribbon Macros group, Macros pull-down menu, Stop Recording.

4.     Save the First Macro

We should always save our macro and the file it will run on before testing it. We previously saved the file as MacroDemo.xlsx.  Click the Save icon, or use Ctrl+S and see what happens:

An ordinary XLSX file cannot contain macros.  Click No.

That will put us into the Save As feature of the File menu.  Choose the folder again.

Save the file with the same name but as an Excel Macro-Enabled Workbook (XLSM file extension):

5.     Test the First Macro

We are now ready to test our first macro

• The selected cell is now C1.
• Press the Keyboard Shortcut: Ctrl+Shift+H, and see what happens.
• Did the macro perform as expected?  Move one cell right and try it again, just in case.

We see (if we recorded it as instructed) that the bold, italics, and underscore happen as expected.  However, at the end, the macro always moves to cell C1 –not one cell to the right.

This is because we left the default setting in place and recorded the macro Absolute.

What we needed, instead, was to record the macro Relative.  In our previous post, we explained Absolute and Relative in detail, so we won’t dwell on them here.  We’ll just repeat the process, this time recording Relative.  For variety, we will use the Developer ribbon.

6.     Display the Developer Ribbon if Necessary

Do you see the Developer ribbon on the right of the View ribbon?

If not, you need to display it first (remember that it is hidden by default).

Click File > Options > Customize Ribbon:

In the right-hand list, checkmark the Main Tab, Developer.

Click OK.  The Developer Tab should now be visible on the right of the View Tab.

7.     Record your Second Macro from the Developer Ribbon

• Select cell E1, the first unformatted heading.
• Save the file in case you mess something up.

Click the Developer ribbon tab.

Note the button in the Code (first) group, Use Relative References.  Click it to make sure that it is active (highlighted), as illustrated below.

• Still on the Developer ribbon, in the Code (first) group, use Record Macro to start recording.  This button then becomes Stop Recording, which you will use later to stop recording.
• Name the Macro Heading2.
• Set a shortcut key Ctrl+Shift+B (you press only Shift+B here, of course).
• Store the macro in This Workbook and add a suitable description with the date.

• Click OK.  You are now recording, so only do the actions as instructed:
• On the Home ribbon, click bold, italics, and single underscore. Or use the shortcuts Ctrl+B, Ctrl+I, and Ctrl+U. 
• Select one cell to the right: Press the Right Arrow key.
• Stop Recording.  Either use the square icon on the Status bar or, on the Developer ribbon, in the Code (first) group, use Stop Recording.

8.     Save and Test the Second Macro

We should always save our macro and the file it will run on before testing it. Above, we saved the file as Macro-Enabled Workbook MacroDemo.xlsm.  Click the Save icon, or use Ctrl+S:

We are now ready to test our second macro

• The selected cell is now F1.
• Press the Keyboard Shortcut: Ctrl+Shift+B, and see what happens.
• Did the macro perform as expected?  Try it again, just in case.

We see that the bold, italics, and underscore still happen as before.  But now that we recorded Relative, the macro always moves one cell to the right, not to a specific cell.

In our next post, we will look at the code that we recorded.  That will appear in the Visual Basic Editor.