Recording a macro

Macros are indispensable when we have to do repetitive tasks. In this recipe, we are going to create a macro in Excel. Instead of manually typing several lines of VBA code to create a subroutine, or short program, we can simply record a series of actions in Excel and then store it in the same file. The macro recorder will automatically create the VBA code, as we will see in future recipes.

These recorded steps can then be replayed, in order to execute a series of steps in a fraction of a second.

Getting ready

Like functions, macros make use of absolute and relative referencing. In this first macro, we will use absolute referencing because we have two other sheets with exactly the same layout.

Open the 02_RecordMacro.xlsx file and confirm that Sheet1 is active. Click on the Developer tab. Then, in the Code group, make sure that Use Relative References is not active:

Figure 1.7 – The Relative References option

How to do it…

We will now proceed with the steps to record a macro:

1. Make sure that 02_RecordMacro.xlsx is still open on Sheet1, and that cell A1 is selected. In the Code group of the Developer tab, click on Record Macro. The Record Macro dialog box appears.
2. In the first textbox, under the Macro name heading, type Format_Range. That will be the name of the macro we are going to record.
3. Press the Tab key, or click in the textbox under the Shortcut key heading to the right of Ctrl+:

   

   Figure 1.8 – The Shortcut key textbox, Ctrl+

4. While holding down the Shift key, press F on the keyboard.

   The shortcut key for this macro is now set to Ctrl + Shift + F.

5. Under Store macro in, make sure that This Workbook is selected. Other options will be discussed in later recipes.
6. Under Description, enter a short description of what the macro will do. Click on OK to start recording:
   

   Figure 1.9 – The Record Macro dialog box with all fields completed

7. Observe the Code group on the Developer ribbon. The Record Macro icon has been replaced with the Stop Recording icon. This means you are now in recording mode, and all your actions on the keyboard and with the mouse will be recorded:
   

   Figure 1.10 – Stop Recording is displayed when in recording mode

8. While in recording mode, click on the Home tab. Then, select the range C1 to F2, hold down Ctrl, and select the range A3 to B7:
   

   Figure 1.11 – The selected ranges

   Now, change the format of the selected cells to bold.

9. Next, select the range C3 to F8:
   

   Figure 1.12 – The selected range

   Once done, center the cell content and change the format to currency (US$).

10. To finish this recording, select cell A1, navigate to the Developer tab | the Code group, and click on Stop Recording.

You have just successfully recorded a macro.

How it works…

Now, this was simple enough, wasn't it? Let's have a look at what just happened.

We set the referencing for the macro to absolute because we want to use the same macro on Sheet2 and Sheet3, too. By starting on cell A1 on Sheet1, we can run the macro from cell A1 on Sheet2 and Sheet3 and get exactly the same results.

Macro names must start with a letter, and there should be no spaces in the name. Furthermore, VBA keywords are not allowed. Format, Copy, and Select are three examples of keywords. Special characters are also taboo.

A keyboard shortcut makes it easy to run a macro, but that's all it is – a shortcut. In the next recipe, we will run a macro from the Macro dialog box.

Storing the macro in This Workbook means it will only work in this specific workbook. To make it available for other workbooks, it must be stored in Personal Macro Workbook. We will find out more about that in future recipes.

Many VBA users leave the Description field empty. This will have no effect on the macro, but it is good practice to explain to other users what you had in mind when saving this macro.

Once you are in recording mode, all your actions are recorded, including errors. It is, therefore, a good idea to makes notes of the steps you want to record.

When you click on Stop Recording, the macro recorder is switched off, and you can relax. Each of your actions has been recorded and converted into VBA code in the background. By opening the Microsoft Visual Basic for Applications editor, you will be able to see the recording code.

There's more…

Once you understand how easy it is to record a series of actions in Excel, you will start looking for reasons to automate all your repetitive tasks.

You can, for instance, record a macro to select the entire sheet and clear it of all formatting. That is much faster than doing so manually, especially if there is more than one sheet.