How to record macro in Excel?

You don’t have to know programming language to have your Excel tasks automated. Simply just record actions, which should be performed and launch it whenever You need. In this article I will show You how to record macro in Excel application.

What is macro?

Macro is a set of actions performed by user, recorded with macro recorder. Those actions are saved in VBA language in Visual Basic Editor module. You can modify macro if You do know VBA, but You don’t have to know it to use them. Thanks to that anyone can record and use macros to automate tasks in their own workbook.

Developer tab

To be able to record a macro in Excel You need to have visible Developer tab, which is usually hidden by default. If You don’t have this available in the ribbon, right click on the top menu and choose Customize ribbon or click File/Options and go to Customize ribbon. On the right, within the Main Tabs pane, check the Developer option.

Record macro

If You finally have Developer tab, go to the first part of it – Code.

And here is the Record Macro button. To start the process, click it and the window pops up.

Here You can name your macro, assign shortcut key (CTRL button + small or big letter), choose where to store recording (Personal Macro Workbook, New Workbook or This Workbook) and add any description.

After You set it just like You want, click OK button and start the recording.

To stop recording simply press Stop Recording button.

Example recording

So having selecting cell A1 as default (after opening the empty workbook) I type Simple, then press tab to go to the cell on the right side, type Excel, press tab, VBA. To be able to click Stop Recording I need to click ENTER for instance. The example code looks like this:

    ActiveCell.FormulaR1C1 = "Simple"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Excel"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "VBA"
    Range("A2").Select

This recording is really simple for the purpose of next point.

Relative and absolute reference

If You did not click the Use Relative References button, under the Record Macro, the recording will save the absolute positions of clicks or key strokes. That’s why the example recording, even when I was pressing tab/right arrow to go to the next cell, has selected three times specified cells.

If You choose Use Relative References button, the example code will look like this:

    ActiveCell.FormulaR1C1 = "Simple"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Excel"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "VBA"
    ActiveCell.Offset(1, -2).Range("A1").Select

As You can see, in that situation macro recording instead of selecting specified cell, it uses .Offset method to stay in the same row and to go 1 column to the right.

    ActiveCell.Offset(0, 1).Range("A1").Select

By leaving .Range(“A1”) it says, that it selects the range equal to the range of 1 cell.

Keep in mind the difference between relative and absolute reference for the recording!

Launch & edit recorded macro

The list of your recorded macros is behind Macros button in the Code chapter of Developer tab.

If You choose All Open Workbooks on the Macros in: list You will see all available macros from opened Workbooks.

As You can see on the screenshot above I recorded 9 macros – 8 stored in Personal Macro Workbook and 1 stored in This Workbook.

There are several ways to launch the macro:
– choose the one from the list and click Run button,
– use the shortcut key in the worksheet,
– click on the button or shape to which You attached the macro,
– directly from Visual Basic Editor.

Also You can change the basic information of macro with Options… or delete macro using Delete button.

To edit the recording select the macro on the list and click Edit. The Visual Basic Editor will pop up with opened code module responsible for the chosen macro.

To open manually Visual Basic Editor – ALT+F11 or first button of Code chapter (Developer tab) and find the name of your macro.
Note: You can’t edit macro stored on PERSONAL.XLSB unless You make it visible.

Store Macro in

As I mentioned above there are 3 available places on the list to store your recording. I’m counting that as 2 groups. The first one: New & This Workbook and second: Personal Macro Workbook.

The first one: New & This Workbook. I treat that as one, because You choose if You want to store that in this or another workbook. Choosing this method You need to keep in mind the format of your workbook, but about that in the next point.

The second one: Personal Macro Workbook. This is another story. If You choose at least once to store any macro in that additional workbook, it will open always with any workbook You open, in the invisible mode.

I still remember what a mystery it was for me, when I got reported that there is some flickering during work of one of my tools. In that the time I learnt, that PERSONAL.XLSB opens parallelly with any workbook.

File format to save workbook with macro

If You want to store recorded macro in your workbook, I mean This Workbook, You need to remember to save that workbook as new type. You can choose for example
.xlsm – Excel Macro-Enabled Workbook,
.xlsb – Excel Binary Workbook.

In other case all recorded macros will be gone.

Real office examples & Summary

Excel macros can be the answer for the boring, repetitive tasks in your worksheet! You can record macro how You format the same data in Excel, which You receive everyday. You can record copy paste between worksheets and even between workbooks. Adding to this a little knowledge of VBA, your simple recording can become a complex tool for daily tasks or whole reports.

In many cases Excel macros are the beginning of the VBA journey or the solution for the situation when the programmer don’t know how to solve his issue!

For more info You can go to the Microsoft documentation.