How to Record a Macro in Excel?

Excel Macro is simply a record button that records a set of actions performed on Excel that can be run multiple times. For example, if you have to format some raw data on weekly basis you can use a macro to record yourself formatting the data once and let excel automate the task in the future. it’s used to help users save time and avoid performing redundant tasks.

In order to record a macro the Developer tab on the ribbon needs to be activated as it isn’t on by default within Microsoft Excel. The developer tab contains all the macro tools among various other features. 

To activate it click on the file tab> goto options>Customize Ribbon Under Customize the Ribbon and under Main Tabs, select the Developer check box. (The developer tab stays on the ribbon unless checked off ).

To record a macro follow these steps :

Step 1: click on the view tab, select Macros(extreme right), and hit Record Macro.

Step 2: A Record Macro  dialog box pops up.

       The record macro dialog box has 4 sections to be filled

• Macro name: give a name to your macro (avoid spaces and keep it short)
• Shortcut Key: you can select any letter to create a shortcut key, be careful not to override an existing shortcut key. (you can press shift and any letter in the box to create a ctrl+shift+letter  key)
• Store macro in: select This workbook to run it only in the current workbook, personal macro workbook so the macro can be used within any workbook on the computer that contains that personal macro workbook and new workbook to run it in a new workbook it creates at the time of recording.
• Description: Here you can provide additional information about the macro(what it does) for the users or ourselves.

Step 3: Click ok and Excel will start recording all of your actions henceforth.

Step 4: Once done, remember to hit the stop recording button in the lower-left corner of the worksheet or head over to view>Macros>stop recording.

Stop recording button bottom left corner

The above steps should help you get started recording a macro.

The Visual Basic Editor 

Excel has a tool called the Visual Basic Editor or VBA that makes note of all the actions you perform while recording a macro in the backend. When you record a macro inside excel through the macro recorder all the code gets stored in a module. To see the code that excel has written head to the Developer tab>code section>Visual Basic this opens up a separate window called the Visual Basic Application window along the left side of this window you will find the module under the project Explorer(if you don’t see it click on View>Project Explorer) double click it and all the text is the code that excel generated while recording the macro.

Absolute and Relative recording:

A macro can be recorded in 2 ways namely Absolute and Relative recording. When you record a macro in Excel it is by default in the Absolute recording mode. In the absolute recording, the steps are performed exactly in the cells the steps were recorded in despite the active cell. In simple words, absolute recording restricts the location of steps. Relative recording executes the steps recorded in any part of the worksheet selected by the user as its relative to the starting location of the macros. To turn on Relative recording head to Developer tab>Code>Use Relative References(gets highlighted in grey when selected) and then start recording the macro.

Limitations of an Excel Macro:

Macros have certain limitations pertaining to them and can be combated by writing the VBA code manually. Some of them are as follows

• A macro doesn’t record what options you select within a dialog box.
• If you need to make any decisions in your process (code), the macro recorder can’t help you make them. you can use the IF statement or other conditional statements.
• The macro recorder can’t throw message boxes to prompt the user.

Macro-Enabled File Extensions:

There are some file extensions that do not store a macro hence you need to be careful about using a macro-enabled format to save your workbook or the macro won’t be saved. 

The default Excel format of the Excel workbook is .xlsx which does not support macros.  To store macros you save and open workbooks in a new macro-enabled workbook format .xlsm. this format is the same as .xlsx, but with the capability to support Excel macros.

The Excel Binary Workbook(.xlsb) and Excel macro-enabled template(.xltm) are some more file extensions that support macros within them.

Steps to save a macro-enabled workbook :

• Click on the File tab and then choose Save As. The Save As dialog box appears.
• Enter a name for the and select a location for your workbook.
• Click the Save as Type drop-down arrow. A list of file types appears.
• Select Excel Macro-Enabled Workbook. Click on Save.

Some more real-time applications of macros can be used to record the creation of charts based on some data, filtering and sorting data, formatting data, or importing it.

My Personal Notes arrow_drop_up

Save