Excel Macro Tutorial

This tutorial describes how to use the Macro Recorder to create a simple macro in Excel. Macro Recorder works by recording all keystrokes and mouse clicks. The macro created in this tutorial applies a series of formatting options to the sheet header.

In Excel, all commands related to macros are in the tab Developer on the tape. Often this tab must be added to the ribbon to access the macro commands. Topics covered in this guide include:

  • Add a developer tab

  • Start recording macros in Excel

  • Excel macro options

  • Macro shot

  • Run a macro

  • Macro Errors/Editing a Macro

Remark . The information in this article applies to versions of Excel 2019, 2016, 2013, 2010, and 2007.

Content
  1. Add Developers Tab
  2. Add a developer tab in Excel 2019, 2016, 2013, or 2010
  3. Add a developer tab in Excel 2007
  4. Add sheet title
  5. Excel macro recorder
  6. Understand macro shooting options
  7. Record Macro Steps
  8. run macro
  9. Troubleshoot and edit macros

Add Developers Tab

Add a developer tab in Excel 2019, 2016, 2013, or 2010

  1. Select File .

  2. Select parameters: to open the dialog Excel options .

  3. Select Customize ribbon in the left pane to view the options available in the right pane of the dialog.

  4. In the main section of the options tabs, select: Developer .

  5. Select Okay .

  6. The Developer tab has been added to the ribbon.

Add a developer tab in Excel 2007

  1. In Excel 2007, select the Office Button to open the drop-down menu.

  2. Select Excel options at the bottom of the menu to open the dialog Excel options .

  3. Select Popular at the top of the left pane of the opened dialog box.

  4. Select Show developer tab on ribbon in the right pane of the opened dialog box.

  5. Select Okay .

  6. The Developer tab is visible on the ribbon.

Add sheet title

Before you start recording the macro, you need to add the title of the sheet to be formatted.

Because the title of each sheet is usually unique to that sheet, you don’t want to include the title in the macro. Therefore, you add it to your worksheet before running the macro recorder.

  1. Select cell A1 on the sheet.

  2. Enter title: Cookie Shop Costs for June 2008 .

  3. Press key Enter on keyboard.

Excel macro recorder

The easiest way to create a macro in Excel is to use the Macro Recorder. For this:

  1. Select developers .

  2. Select Macro shot to open the dialog Macro shot .

Understand macro shooting options

There are four options in this dialog box.

  1. Macro name . Give your macro a descriptive name. The name must start with a letter and spaces are not allowed. Only letters, numbers and underscores are allowed.
  2. Keyboard Shortcut (not necessary). Type a letter, number or other symbols in the free space. Allows you to run the macro by holding down the CTRL key and pressing the selected letter on the keyboard.
  3. Save macro in (parameter):

    1. This workbook: The macro is only available in this file.

    2. New Workbook: This option opens a new Excel file. The macro is only available in this new file.

    3. Personal macro book. This option creates a hidden file personal.xls which stores your macros and is available to you in all Excel files.

  4. Description (optional): Enter a description for the macro.

Set options in the dialog box Macro shot match the settings in the image above. Please note the following before proceeding:

  • When you press Okay in the dialog box Macro shot recording of the newly defined macro begins.

  • As mentioned before, the macro recorder works by recording all keystrokes and mouse clicks.

  • Create a macro format_titles involves clicking the mouse on some format options on the main ribbon tab while the macro recorder is running.

Record Macro Steps

After configuring the macro recording options as shown in the previous step, you are ready to proceed.

  1. Select Okay in the dialog box Macro shot to start the macro recorder.

  2. Select House .

  3. Highlight cells A1 to F1 on the sheet.

  4. Select Fusion and center to center the title between cells A1 and F1.

  5. Select Fill color (similar to painting) to open the fill color drop-down list.

  6. Choose from the list Blue, Accent 1 to change the background color of selected cells to blue.

  7. Select font color (it’s a capital “A”) to open the font color drop-down list.

  8. Choose from the list white to whiten the text in the selected cells.

  9. Select Font size (above the paint can icon) to open the font size drop-down list.

  10. Select sixteen from the list to change the text size in the selected cells to 16 points.

  11. Select Developer .

  12. Select Stop recording stop macro recording.

At this point, the title of the sheet resembles the title in the image above.

run macro

Now you can run the macro you just recorded.

  1. Select tab sheet2 at the bottom of the table.

  2. Select cell A1 on the sheet.

  3. Enter title: Cookie Shop Cost for July 2008 .

  4. Press key Enter on keyboard.

  5. Select Developer .

  6. Select Macros to open the dialog View macro .

  7. Choose a macro format_titles in the window Macro name .

  8. Select walk .

The macro steps are performed automatically and the same formatting steps are applied as for the header on sheet 1.

At this point, the title on Worksheet 2 resembles the title on Worksheet 1.

Troubleshoot and edit macros

If your macro didn’t work as expected, the easiest and best option is to repeat the steps in the tutorial and overwrite the macro.

An Excel macro is written in the Visual Basic for Applications (VBA) programming language. When selecting buttons Change or get in in the dialog box macro the VBA editor will start (see image above).

Using the VBA editor and coverage of the VBA programming language are beyond the scope of this guide.

Leave a Reply

Your email address will not be published.