How to Automate Tasks with Macros in Excel 2007

You can use macros in Excel 2007 to save time by automating tasks that you perform frequently. A macro is a series of commands grouped together that you can run whenever you need to perform the task.

Although you can write your own complex macros in the Visual Basic programming language, the easiest method for creating many macros is to use the macro recorder. When you record a macro, Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or play back, the commands.

The macro recorder records every action you complete. Therefore, planning your macro before you begin the recording process is very important so you don’t record unnecessary steps.

Display the Developer tab

The Developer tab provides access to the macro commands, but this tab doesn’t appear by default. To display the Developer tab, follow these steps:

  1. Click the Office button and then click the Excel Options button.

    The Excel Options dialog box appears.

  2. From the Popular section, select the Show Developer Tab in the Ribbon check box.

  3. Click OK.

    The Developer tab appears in the Ribbon.

    Display the Developer tab to work with macros in Excel 2007.
    Display the Developer tab to work with macros in Excel 2007.

Record a macro

Follow these steps to record a macro:

  1. Choose Record Macro in the Code group of the Developer tab.

    The Record Macro dialog box appears.

  2. Type a name for the macro in the Macro Name text box.

    The first character of the macro name must be a letter, and the name cannot contain spaces or cell references. Macro names are not case-sensitive.

  3. (Optional) Assign a Shortcut Key.

    If you select a shortcut key already used in Excel, the macro shortcut key overrides the Excel shortcut key while the workbook that contains the macro is open.

  4. From the Store Macro In drop-down list, select where you want to store the macro:

    • This Workbook: Save the macro in the current workbook file.

    • New Workbook: Create macros that you can run in any new workbooks created during the current Excel session.

    • Personal Macro Workbook: Choose this option if you want the macro to be available whenever you use Excel, regardless of which workbook you're using.

  5. (Optional) Type a description of the macro in the Description text box.

    Use the Record Macro dialog box to begin creating a macro.
    Use the Record Macro dialog box to begin creating a macro.
  6. Click OK.

    The Record Macro option on the Developer tab changes to Stop Recording.

  7. Perform the actions you want to record.

    Excel records your steps exactly — such as (Select cell C3) — but you can also record the steps relative to any current cell — such as (Go up one row and insert a blank line). To do so, click the Relative References button on the Developer tab. You can turn the Relative References feature on and off as needed while recording the macro.

  8. Choose Stop Recording in the Code group of the Developer tab.

    The macro recorder stops recording keystrokes, and the macro is complete.

blog comments powered by Disqus
Advertisement

Inside Dummies.com