How to create a Macro in Excel?

As we have seen in the previous post, it is possible to create Macros in Excel without the need for coding. For doing this, we need to record it using the Macro Recorder.

Internally, a recorded Macro generates a VBA procedure (with code). The only difference is that this code was generated automatically by Excel. Many Excel users call any VBA procedure a macro, but normally Macros are only referred to the recorded ones.

WHAT IS THE MACRO RECORDER IN EXCEL?

The Macro Recorder is an Excel function that allows saving different steps or actions we perform in Excel to execute them automatically at a later moment.

For instance, imagine you are doing a repetitive task, like entering some formulas to the end of a column. You could save how you need to do it for one of them, and then, for the rest, you can call the Macro once you have selected the right cell from where to do this action.

You can find this function in any Excel file. Although to save a Macro, you will need to use only Excel file types that allow Macros (extension “.xlsm”).

WHERE IS THE MACRO RECORDER IN AN EXCEL FILE?

The Macro Recorder is in the Developer Tab of the Menu.

If you don’t find this tab, it might be because it was not yet activated (by default, it is not). In order to activate it, follow this steps:

1. Go to the Menu option: File -> Options
2. Choose the option Customize Ribbon in the left panel
3. Select the Developer Check box under the Customize the Ribbon panel
4. Click OK

Once activated, you will see the Record Macro option within the Developer tab under the Code group.

It is also possible to access directly from the tab View. However, its access is direct from the Developer tab and, in any case, you will need this tab for the rest of the developer features.

HOW TO RECORD A MACRO IN EXCEL?

Once we click in the Record Macro option, we will see the following dialog box:

Where we can enter the following information:

1. Macro name: By default, Excel proposes the name Macro followed by a sequential number. But we should enter a descriptive name so that later on, we can quickly identify what this macro does.
2. Shortcut key: A macro can be run manually or triggered after some Excel event. If we want to run it manually, we can enter in this field the letter that hit in combination with the key Ctrl will launch it. This is an optional field that we can leave empty.
3. Store macro in: By default, Excel proposes to store the macro you are going to record in the same workbook you are using for recording it. But you could decide to store it in a different way. In any case, next time you will like to run it, you will need to open the file where it has been stored.
4. Description: This is an optional field. It can be useful if you create many macros, so you can add additional information to understand what each of them does.

In order to create your first macro, follow these steps:

1. Enter a new name for this new macro
   • For instance, let’s create a macro that enters the number 1 in the selected cell at the moment of running the macro.
   • So the name will be Enter1.
2. Enter a shortcut
   • Let’s enter the letter a. So the macro will be run when we press Ctrl+a (both keys at the same time)
3. Click OK
   • This will start the recording
   • Whatever you do in Excel till you stop the recording will be added to it
4. Select one cell
   • This will be starting cell
5. Enter 1 in that cell and press Enter
6. Press the menu option Stop Recording (which is now in the place where it was the Record Macro option)
   • You can also use the stop icon in the status bar in the bottom left corner (see option 2 in the screenshot)

Congratulations! you have created your first macro.

Now, to test it, select a different cell and press Ctrl+a. 

You will see that a 1 has been entered automatically in that cell.

You will also see that the cell below the cell used during the recording is automatically selected. This is because, when pressing Enter after entering the 1 you have also recorded to select the cell below.

HOW TO SEE THE CODE THAT AN EXCEL MACRO GENERATES?

Once we have recorded a macro, we can also check what code Excel has generated in the background.

For that, we need to select the option Visual Basic, also in the Developer tab, Code group (in the left corner).

Once we press it, a new window will be open with the VBA procedure generated for this simple macro.

Excel has created the Sub procedure Enter1 (same name as we gave to the macro).

Within this procedure, the sentences starting with ‘ are comments. They tell us in this case that we can run this macro with the shortcut we entered (Ctrl+a).

And the other two sentences tell Excel to perform these two actions:

• ActiveCell.FormulaR1C1 = “1”
  • Enter the number 1 in the selected cell at the moment of running the macro
• Range(“G14”).Select
  • Select the cell G14 (this is the cell just below the selected cell in the recording, the one it was selected when I pressed Enter, in your case it will be a different one)

This procedure can be edit in this window, so that if the macro is run again it will perform these actions considering these changes.

For instance, imagine that now:

• We want to enter 2, instead of 1
  • For this, we will need to replace 1 by 2 in the first sentence:
    • ActiveCell.FormulaR1C1 = “2”
• We want to select the cell “A1” (the top-right cell) after the macro is run.
  • For this we will need to repace G14 by A1 in the second sentence:
    • Range(“A1”).Select

Once we enter these changes and press the Save button we can come back to the Excel spreadsheet and run again the macro (selecting a cell and pressing Ctrl+a).

We will see now that a 2 is populated and that the A1 cell is selected.