Tuesday, June 26, 2018

Run a macro

Run a macro

There are several ways to run a macro in Microsoft Excel. A macro is an action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language. You can always run a macro by clicking the Macros command on the Developer tab on the ribbon. Depending on how a macro is assigned to run, you might also be able to run it by pressing a combination shortcut key, by clicking a button on the Quick Access Toolbar or in a custom group on the ribbon, or by clicking on an object, graphic, or control. In addition, you can run a macro automatically whenever you open a workbook.

Before you run macros

Before you start working on macros you should enable the Developer tab.

  • For Windows, go to File > Options > Customize Ribbon.

  • For Mac, go to Excel > Preferences... > Ribbon & Toolbar.

  • Then, in the Customize the Ribbon section, under Main Tabs, check the Developer check box, and press OK.

  1. Open the workbook that contains the macro.

  2. On the Developer tab, in the Code group, click Macros.

  3. In the Macro name box, click the macro that you want to run, and press the Run button.

  4. You also have other choices:

    • Options - Add a shortcut key, or a macro description.

    • Step - This will open the Visual Basic Editor to the first line of the macro. Pressing F8 will let you step through the macro code one line at a time.

    • Edit - This will open the Visual Basic Editor and let you edit the macro code as needed. Once you've made changes, you can press F5 to run the macro from the editor.

You can add a combination shortcut key to a macro when you record it, and you can also add one to an existing macro:

  1. On the Developer tab, in the Code group, click Macros.

  2. In the Macro name box, click the macro that you want to assign to a combination shortcut key.

  3. Click Options.

    The Macro Options dialog box appears.

  4. In the Shortcut key box, type any lowercase or uppercase letter that you want to use with the shortcut key.

    Notes: 

    • For Windows, the shortcut key for lowercase letters is Ctrl+letter. For uppercase letters, it's Ctrl+Shift+Letter.

    • For Mac, the shortcut key for lowercase letters is Option+Command+letter, but Ctrl+letter will work as well. For uppercase letters, it's Ctrl+Shift+Letter.

    • Be careful assigning shortcut keys, because they will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For example, if you assign a macro to Ctrl+z, you'll lose the ability to Undo. Because of this, it's generally a good idea to use Ctrl+Shift+Uppercase letter instead, like Ctrl+Shift+Z, which doesn't have an equivalent shortcut in Excel.

    For a list of Ctrl combination shortcut keys that are already assigned in Excel, see the article Excel shortcut and function keys.

  5. In the Description box, type a description of the macro.

  6. Click OK to save your changes, and then click Cancel to close the Macro dialog box.

To run a macro from a button on the Quick Access toolbar, you first have to add the button to the toolbar. To do that, see Assign a macro to a button.

You can create a custom group that appears on a tab in the ribbon, and then assign a macro to a button in that group. For example, you can add a custom group named "My Macros" to the Developer tab, and then add a macro (that appears as a button) to the new group. To do that, see Assign a macro to a button.

Run a macro by clicking an area on a graphic object

You can create a hotspot on a graphic that users can click to run a macro.

  1. In the worksheet, insert a graphic object, such as a picture, or draw a shape. A common scenario is to draw a Rounded Rectangle shape, and format it so it looks like a button.

    To learn about inserting a graphic object, see Add, change, or delete shapes.

  2. Right-click the hotspot that you created, and then click Assign Macro.

  3. Do one of the following:

    • To assign an existing macro to the graphic object, double-click the macro or enter its name in the Macro name box.

    • To record a new macro to assign to the selected graphic object, click Record, type a name for the macro in the Record Macro dialog box, and then click OK to begin recording your macro. When you finish recording the macro, click Stop Recording Button image on the Developer tab in the Code group.

      Tip:  You can also click Stop Recording Button image on the left side of the status bar.

    • To edit an existing macro, click the name of the macro in the Macro name box, and then click Edit.

  4. Click OK.

On the Developer tab, click Visual Basic to launch the Visual Basic Editor (VBE). Browse the Project Explorer to the module that contains the macro you want to run, and open it. All of the macros in that module will be listed in the pane on the right. Select the macro you want to run, by placing your cursor anywhere within the macro, and press F5, or on the menu, go to Run > Run Macro.

Create a Workbook_Open event.

The following example uses the Open event to run a macro when you open the workbook.

  1. Open the workbook where you want to add the macro, or create a new workbook.

  2. On the Developer tab, in the Code group, click Visual Basic.

  3. In the Project Explorer window, right-click the ThisWorkbook object, and then click View Code.

    Tip: If the Project Explorer window is not visible, on the View menu, click Project Explorer.

  4. In the Object list above the Code window, select Workbook.

    This automatically creates an empty procedure for the Open event, such as this:

    Private Sub Workbook_Open()

    End Sub

  5. Add the following lines of code to the procedure:

    Private Sub Workbook_Open()
    MsgBox Date
    Worksheets("Sheet1").Range("A1").Value = Date
    End Sub

  6. Switch to Excel and save the workbook as a macro-enabled workbook (.xlsm).

  7. Close and reopen the workbook. When you open the workbook again, Excel runs the Workbook_Open procedure, which displays today's date in a message box.

  8. Click OK in the message box.

    Note: The cell A1 on Sheet1 also contains the date as a result of running the Workbook_Open procedure.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice

Top of Page

See Also

Automatically run a macro when opening a workbook 

Automate tasks with the Macro Recorder

Record a macro to open specific workbooks when Excel starts

Create and save all your macros in a single workbook

Save a macro

at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)