Excel Tutorials: How to create and use Excel 2007 Macro

Excel Macro Recorder Tutorial

Excel 2007 Macro Recorder Tutorial

� Ted French

For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series. 

Excel Macro Recorder Tutorial

Note: For help on these steps, refer to the image above.

Related article: Excel 2003 Macro Tutorial

This tutorial covers using the macro recorder to create a simple macro in Excel. The macro recorder works by recording all keystrokes and clicks of the mouse.

The macro created in this tutorial will apply a number of formatting options to a worksheet titles.

In Excel 2007, all macro - related commands are located on the Developer tab of the ribbon. Often, this tab needs to be added to the ribbon in order to access the macro commands.

Adding the Developer Tab

Excel 2007 Macro Recorder Tutorial

� Ted French

Adding the Developer Tab

Note: For help on these steps, refer to the image above.

1. Click on the Office button to open the drop down menu.
   
   
2. Click on the Excel Options button located at the bottom of the menu to open the Excel Options dialog box.
   
   
3. Click on the Popular option at the top of the left hand window of the open dialog box.
   
   
4. Click on the Show Developer Tab in the ribbon in the right hand window of the open dialog box.
   
   
5. Click OK.
   
   
6. The Developer tab should now be visible in the ribbon.

Adding a Worksheet Title / The Excel Macro Recorder

Excel 2007 Macro Recorder Tutorial

� Ted French

Adding a Worksheet Title

Note: For help on these steps, refer to the image above.

Before we start recording our macro, we need to add the worksheet title we will be formatting.

Since the title of each worksheet is usually unique to that worksheet, we don't want to include the title in the macro. Therefore we will add it to the worksheet, before starting the macro recorder.

1. Click on cell A1 in the worksheet.
   
   
2. Type the title: Cookie Shop Expenses for June 2008.
   
   
3. Press the Enter key on the keyboard.

Excel Macro Recorder

The easiest way to create a macro in Excel is to use the macro recorder. To do so:

1. Click on the Developers tab.
   
   
2. Click on Record Macro in the ribbon to open the Record Macro dialog box.

The Macro Recorder Options

Excel 2007 Macro Recorder Tutorial

� Ted French

The Macro Recorder dialog box

Note: For help on these steps, refer to the image above.

There are 4 options to complete in this dialog box:

1. Macro name - give your macro a descriptive name. The name must begin with a letter and spaces are not allowed. Only letters, numbers and the underscore character are permitted.
   
   
2. Shortcut key - (optional) fill in a letter, number, or other character in the available space. This will allow you to run the macro by holding down the CTRL key and pressing the chosen letter on the keyboard.
   
   
3. Store macro in
   Options:
   • This workbook
     • The macro is available only in this file.
   • New workbook
     • This option opens a new Excel file. The macro is available only in this new file.
   • Personal macro workbook.
     • This option creates a hidden file Personal.xls which stores your macros and makes them available to you in all Excel files.
4. Description - (optional) enter a description of the macro.

For this tutorial:

1. Set the options in the Record Macro dialog box to match those in the image above.
   
   
2. Do Not click OK - yet - see below.
   
   • Clicking the OK button in the Record Macro dialog box starts recording the macro you have just identified.
     
     
   • As previously mentioned, the macro recorder works by recording all keystrokes and clicks of the mouse.
     
     
   • Creating the format_titles macro involves clicking on a number of format options on the home tab of the ribbon with the mouse while the macro recorder is running.
3. Go to the next step before starting the macro recorder.

Adding the Macro Steps

Excel 2007 Macro Recorder Tutorial

� Ted French

Recording the Macro

Note: For help on these steps, refer to the image above.

1. Click the OK button in the Record Macro dialog box to start the macro recorder.
   
   
2. Click on the Home tab of the ribbon.
   
   
3. Drag select cells A1 to F1 in the worksheet to highlight them.
   
   
4. Click on the Merge and Center icon to center the title between cells A1 and F1.
   
   
5. Click on the Fill Color icon (looks like a paint can) to open the fill color drop down list.
   
   
6. Choose Blue, Accent 1 from the list to turn the background color of the selected cells to blue.
   
   
7. Click on the Font Color icon (it is a large letter "A") to open the font color drop down list.
   
   
8. Choose White from the list to turn the text in the selected cells to white.
   
   
9. Click on the Font Size icon (above the paint can icon) to open the font size drop down list.
   
   
10. Choose 16 from the list to change the size the text in the selected cells to 16 point.
    
    
11. Click on the Developer tab of the ribbon.
    
    
12. Click the Stop Recording button on the ribbon to stop the macro recording.
    
    
13. At this point, your worksheet title should resemble the title in the image above.

Running the Macro

Excel 2007 Macro Recorder Tutorial

� Ted French

Running a Macro in Excel

Note: For help on these steps, refer to the image above.

To run a macro you have recorded:

1. Click on the Sheet2 tab at the bottom of the spreadsheet.
   
   
2. Click on cell A1 in the worksheet.
   
   
3. Type the title: Cookie Shop Expenses for July 2008.
   
   
4. Press the Enter key on the keyboard.
   
   
5. Click on the Developer tab of the ribbon.
   
   
6. Click the Macros button on the ribbon to bring up the View Macro dialog box.
   
   
7. Click on the format_titles macro in the Macro name window.
   
   
8. Click the Run button.
   
   
9. The steps of the macro should run automatically and apply the same formatting steps applied to the title on sheet 1.
   
   
10. At this point, the title on worksheet 2 should resemble the title on worksheet 1.

Macro Errors / Editing a Macro

Excel 2007 Macro Recorder Tutorial

� Ted French

Macro Errors

Note: For help on these steps, refer to the image above.

If your macro did not perform as expected, the easiest, and best option is to follow the steps of the tutorial again and re - record the macro.

Editing/Step Into a Macro

An Excel macro is written in the Visual Basic for Applications (VBA) programming language.

Clicking on either the Edit or Step Into buttons in the Macro dialog box starts the VBA editor (see the image above).

Using the VBA editor and covering the VBA programming language is beyond the scope of this tutorial.