Record Macro in Excel

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.
  3. 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.

    Developer Tab in Excel Ribbon

  4. Click on Record Macro from the Code group. Now you can see the following Record Macro dialogue box on the screen.
  5. Record Macro Dialogue Box

  6. Here we can specify Macro name, Shortcut key, macro location and description
  7. 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,…

    Macro Name

    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.
    Rules of VBA Macro Name

    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.

    Macro 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.

    Macro Location

    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.

    Macro Description

    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.

    Record 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.
    Stop Recording Macro

    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.

    Run Recorded Macro

    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.

    Edit Macro in VBE

    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. Enable Macros

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

    Macro Security Settings - Trust Center

    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

2 thoughts on “Record Macro in Excel”

Leave a Reply