facebook google plus twitter
Webucator's Free Advanced Microsoft Excel 2013 Tutorial

Lesson: Recording and Using Macros

Welcome to our free Advanced Microsoft Excel 2013 tutorial. This tutorial is based on Webucator's Advanced Microsoft Excel 2013 Training course.

As you work with Excel, you may find that you repeat certain operations frequently. Instead of performing each step of the operation, you can create a macro that stores these steps. You can then run the macro, or let Excel perform the steps for you, saving you both time and effort.

Lesson Goals

  • Record macros.
  • Run macros.
  • Edit macros (kind of).
  • Add macros to the Quick Access Toolbar.
  • Manage macro security.

Recording Macros

To record a macro in Microsoft Excel:

  1. On the VIEW tab, in the Macros group, click the Macros command:Macros Command
  2. Click Record Macro:Record Macro
  3. In the Record Macro dialog box:
    1. Enter a Macro name. Choose a name that clearly identifies the macro. You cannot use spaces.
    2. Enter a Shortcut key. You will be able to run the macro using this shortcut key.
    3. Choose where to store the macro. You will normally accept the default (This Workbook).
    4. Enter a Description. Briefly explain what the macro does.
    5. Click OK.Explain Macro
  4. After clicking OK, every keystroke is recorded.
  5. To stop recording, on the VIEW tab, in the Macros group, click the Macros command and then click Stop Recording:Stop Recording

Copy a Macro from Workbook to Workbook

You can copy macros to different workbooks. First you need to ensure the DEVELOPER tab is active on the Ribbon. To add it:

  1. Right-click the Ribbon and select Customize the Ribbon. Customize the Ribbon Command
  2. Select Customize Ribbon, and on the right, select the Developer check box and click OK. Select Options

Now you need to enable and copy the macros:

  1. Select the DEVELOPER tab, and select Macro Security from the Code group. Macro Security
  2. Under Macro Settings, select Enable all macros and click OK. Enable All Macros
  3. Open both the source and destination workbook.
  4. Select the DEVELOPER tab, and select Visual Basic from the Code group. Visual Basic Option
  5. From the Project Explorer pane, press Ctrl and drag the module from the source to the destination workbook. Drag to Destination Workbook
  6. Close the Visual Basic window and save the workbook.

Recording Macros

To record a macro in Microsoft Excel:

  1. On the VIEW tab, in the Macros group, click the Macros command:
  2. Click Record Macro:
  3. In the Record Macro dialog box:
    1. Enter a Macro name. Choose a name that clearly identifies the macro. You cannot use spaces.
    2. Enter a Shortcut key. You will be able to run the macro using this shortcut key.
    3. Choose where to store the macro. You will normally accept the default (This Workbook).
    4. Enter a Description. Briefly explain what the macro does.
    5. Click OK.
  4. After clicking OK, every keystroke is recorded.
  5. To stop recording, on the VIEW tab, in the Macros group, click the Macros command and then click Stop Recording:

Recording a Macro

Duration: 10 to 20 minutes.

In this exercise, you will record a macro that adds a custom header and footer to a Microsoft Excel workbook.

  1. Open Macros.xlsx from your Excel2013.3/Exercises folder.
  2. Record a macro named "CustomHF" to add a custom header and footer to your workbook. Specifically, the macro should:
    1. Add the word "CONFIDENTIAL" in the center of the header.
    2. Add the date on the right side of the header.
    3. Add the page number to the center of the footer.

Solution:

  1. On the VIEW tab, in the Macros group, click the Macros command:Macros Command
  2. Click Record Macro:Record Macro Command
  3. In the Record Macro dialog box:
    1. Enter the Macro name of "CustomHF".
    2. Enter a Shortcut key.
    3. Accept the default to store the macro in this workbook.
    4. Enter a Description such as "Custom header and footer."
    5. Click OK.Record Macro Dialog Box
  4. After clicking OK, every keystroke is recorded. Following are specific instructions:
    1. Click FILE, then Print, and then Page Setup:Page Setup
    2. Select the Header/Footer tab and then click Custom Header:Custom Header
    3. Tab to or click in the Center section and type "CONFIDENTIAL". Then tab to or click in the Right section, click the Insert Date icon, and then click OK:Insert Date
    4. Click Custom Footer:Custom Footer
    5. Tab to or click in the Center section, click the Insert Page Number icon, and then click OK:Page Number
    6. You are now back in the Page Setup dialog box. Click OK:Page Setup Dialog Box
    7. On the VIEW tab, in the Macros group, click the Macros command and then click Stop Recording:Stop Recording Command

Running Macros

To run a macro in Microsoft Excel:

  1. On the VIEW tab, in the Macros group, click the Macros command:Macros Command
  2. Click View Macros:View Macros
  3. In the Macro dialog box, choose the macro you want to run and click Run:Macro Dialog Box

Unless they are very large, macros run very fast, meaning that almost immediately after clicking Run the macro will finish executing. If the macro doesn't change anything that is readily visible, it may seem as if nothing has happened.

Editing Macros

If you need to make simple changes to a macro, you can do so using Microsoft Visual Basic for Applications (VBA). The steps below briefly describe how to do this. For most Excel users, however, it is easier to record a new macro than to edit an existing macro. To learn how to write and edit complicated macros, you should take a course on VBA.

To edit a macro in Microsoft Excel:

  1. On the VIEW tab, in the Macros group, click the Macros command:Macros Command
  2. Click View Macros:View Macros Command
  3. In the Macro dialog box, choose the macro you want to change and click Edit to open the macro in the Microsoft Visual Basic for Applications window:Macro Dialog Box
  4. You can see and edit your code in the Microsoft Visual Basic for Applications window:Code

Microsoft Visual Basic for Applications is a powerful programming language that can be used to make major customizations to Microsoft Excel and other Microsoft Office applications. Editing code is far beyond the scope of this class, but it's fun to think about just how powerful Microsoft Excel is.

Adding Macros to the Quick Access Toolbar

The reason to create a macro is to make it easier to complete a set of operations that you regularly perform. Given this, it is likely that you need to run the macro frequently. To make it easy to do so, you can add the macro to the Quick Access Toolbar.

To add a macro to the Quick Access Toolbar:

  1. Click the Customize Quick Access Toolbar drop-down arrow:Customize Quick Access Toolbar
  2. Select More Commands:More Commands
  3. In the Excel Options dialog box on the Quick Access Toolbar tab, from the Choose commands from drop-down list, select Macros:Excel Options Dialog Box
  4. Select the macro you want to add to the Quick Access Toolbar and click Add and then OK:Select Macro
  5. You can now see and run the macro on the Quick Access Toolbar:Quick Access Toolbar

Managing Macro Security

When working with macros, it is important to maintain security, that is, ensure that no dangerous code is going to be run.

To enable security when working with macros in Excel 2013:

  1. Enter Backstage View by selecting FILE > Options. Backstage View
  2. In the Excel Options dialog box, select Customize Ribbon. Excel Options Dialog Box
  3. Under Customize the Ribbon, on the right, check the Developer check box and then click OK. Developer Checkbox
  4. Now, on the DEVELOPER tab, in the Code group, select Macro Security. Macro Security
  5. Use the options in the Trust Center dialog box to set security options. Trust Center Dialog Box

Adding a Macro to the Quick Access Toolbar

Duration: 5 to 10 minutes.

In this exercise, you will add a macro to the Quick Access Toolbar.

  1. Open Add Macros to QAT.xlsm from your Excel2013.3/Exercises folder.
  2. Add the macro named "CustomHF" to the Quick Access Toolbar.

Solution:

  1. Click the Customize Quick Access Toolbar drop-down arrow:Customize Quick Access Toolbar
  2. Select More Commands:More Commands
  3. In the Excel Options dialog box on the Quick Access Toolbar tab, from the Choose commands from drop-down list, select Macros:Macros Command
  4. Select the macro named "CustomHF" to add it to the Quick Access Toolbar and click Add and then OK:Add Command
  5. You can now see and run the macro on the Quick Access Toolbar:Run the Macro