Record Macro in Excel

Record Macro in Excel is used to perform a task or action in Excel. In this chapter we learn how to record, edit, save and delete macro and its overview. We can easily record a macro. First we need to decide what we want to record. It helps to learn and understand VBA code or macros for beginners. We use VBA to automate repetitive tasks in our regular job. So that we can save time, increase quality in generating reports, speed up the work, reduce errors and many more. In this tutorial you can find step by step instructions to record a macro in Excel.

Step by Step Instructions to Record Macro in Excel

Here are the step by step instructions to record a macro in Excel. You can follow these instructions to create a macro in Excel visual basic editor.

1. Open Excel
2. Click on the Developer Tab from Excel Ribbon.

Default if it is not visible in the Excel ribbon, you can add the Developer Tab to the Excel ribbon. For step by step instructions you can click on the following link.
Link: Developer-tab

Screenshot: You can see the Developer tab at Excel ribbon as shown in the following screenshot.



3. Click on Record Macro from the Code group. Now you can see the following Record Macro dialogue box on the screen.



4. Here we can specify Macro name, Shortcut key, macro location and description

Create Macro Name

Let us know how to create a macro name here. As shown in the above screen there is a text box to specify or define macro name. You can enter macro name in the text box. Default the name will be Macro1,Macro2, Macro3,…



Rules of VBA Macro Name :

1. Macro Name allows both upper-case and lower-case letters.
2. The first character must be a letter.
3. name doesn’t include space or special characters.
4. It shouldn’t conflict with an existing name
5. The macro name maximum length is 255 letters.

Note: If the macro name doesn’t satisfy the above specified rules it appears following message on the screen.


Create Shortcut key for Macro

Here are the steps to create shortcut key for macro.

1. Go to record macro dialogue box.
2. After macro name, there is another text-box to enter shortcut key.
3. It is combination with Ctrl key.
4. after Ctrl key, there is a small textbox. Enter any letter. It shouldn’t match with any existing built-in shortcut key.
5. Once shortcut key assigned, you can run respective macro by using this shortcut key.



Where to Store Macro in & location?

Here we see the steps to create shortcut key for macro.

1. Go to record macro dialogue box.
2. After shortcut key, there is an option called Store macro in.
3. It is a drop-down box with 3 options. Those are This Workbook, Personal Macro Workbook and New Workbook.
4. Default value is ‘This Workbook’. You can change this as per your need.



Create Description for Macro

Here we see the steps to create shortcut key for macro.

1. Go to record macro dialogue box.
2. After store macro in, there is an option called Description along with text box.
3. In this text box, we can enter description of the macro.
4. You can also write macro description using MacroOptions method of application.



Example to add description using VBA macro code:

'Add description to Macro
Sub VBAF1_Add_Description_To_Macro()

    Application.MacroOptions Macro:="Macro1", Description:="Write Description Here"
    
End Sub

Start Recording Macro and Example

Let us see the step by step instructions to start recording macro. Let us say we want to apply fill color to cell.

1. Go to Developer tab from Excel ribbon.
2. Click Record Macro button from the Code group.
3. Select any cell on the worksheet.
4. Apply Fill color to cell from the Home tab.
5. Go to Developer tab back and click on the stop recording button to stop.
6. Click on Macros from the code group.
7. Select recorded macro name and click on edit to see the recorded macro.



Now the recorded macro looks like below.

Sub Macro4()
'
' Macro4 Macro
'

'
    Range("BT93").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Stop Recording Macro

Here we see the steps to stop recording macro.

1. Go to Developer tab from Excel ribbon.
2. Click Stop Recording button from the Code group.

Note: The Stop recording macro button is visible when we are recording macro only.


Run the Recorded Macro

Let us see the steps to run the recorded macro.

1. Go to Developer tab from Excel ribbon.
2. Click on Macros button from the Code group.
3. Now the Macro dialogue box appears on the screen.
4. Here you can see list of available macro names
5. You can select the macro from the list
6. Click on Run button from the right side.
7. You can find below screenshot for your reference.



Edit the Recorded Macro in VBE

Here are the step by step instructions to test the recorded macro in Visual basic editor window in VBA.

1. Go to Developer tab from the Excel ribbon.
2. Click on the Macros button from Code group.
3. Select macro to edit from the list of macros.
4. Click on Edit button from the right side.
5. Now it shows respective macro in visual basic editor window to edit the macro.
6. Here is the below screenshot for your reference.



Macro Security Settings & Enable Macros

1. Go to Developer tab from the Excel ribbon.
2. After recording the macro, close Excel file and reopen it.
3. You can see following security warning message.



4. Click on enable content to enable macros.
5. You can also enable in the following way as well. Click on Macro security from the Code group.
6. You can find below screenshot for your reference.



You can also watch this tutorial in youtube.

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays in Excel Blog

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers