Applies To

If you have tasks in Microsoft Excel that you do repeatedly, you can record a macro to automate those tasks. A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes. After you create a macro, you can edit it to make minor changes to the way it works.

Suppose that every month, you create a report for your accounting manager. You want to format the names of the customers with overdue accounts in red, and also apply bold formatting. You can create and then run a macro that quickly applies these formatting changes to the cells you select.WindowsMac

How?

Before you record a macro    Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it. For more information, see Show the Developer tab.

Record a macro    In the Code group on the Developer tab, click Record Macro.Optionally, enter a name for the macro in the Macro name box, enter a shortcut key in the Shortcut key box, and a description in the Description box, and then click OK to start recording.Perform the actions you want to automate, such as entering boilerplate text or filling down a column of data.On the Developer tab, click Stop Recording.

Take a closer look at the macro    You can learn a little about the Visual Basic programming language by editing a macro.To edit a macro, in the Code group on the Developer tab, click Macros, select the name of the macro, and click Edit. This starts the Visual Basic Editor.See how the actions that you recorded appear as code. Some of the code will probably be clear to you, and some of it may be a little mysterious.Experiment with the code, close the Visual Basic Editor, and run your macro again. This time, see if anything different happens!

Run a macro in Excel

Applies To

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 selecting 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 selecting a button on the Quick Access Toolbar or in a custom group on the ribbon, or by selecting 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, then, in the Customize the Ribbon section, under Main Tabs, check the Developer check box, and press OK.
• 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.

Run a macro from the Developer tab

1. Open the workbook that contains the macro.
2. On the Developer tab, select Macros.
3. In the Macro name box, select 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 or Step Into – 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.

Run a macro by pressing a combination shortcut key

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, select Macros.
2. In the Macro name box, select the macro that you want to assign to a combination shortcut key.
3. Select 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. Select OK to save your changes, and then select Cancel to close the Macro dialog box.

Run a macro by selecting a button on the Quick Access Toolbar

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.

Run a macro by selecting a button in a custom group on the ribbon

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 selecting an area on a graphic object

Run a macro by selecting 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 select 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, select Record Macro, type a name for the macro in the Record Macro dialog box, and then select OK to begin recording your macro. When you finish recording the macro, select Stop Recording  on the Developer tab in the Code group.Tip:  You can also select Stop Recording  on the left side of the status bar.
   • To edit an existing macro, select the name of the macro in the Macro name box, and then select Edit.
4. Select OK.

Run a macro from the Visual Basic Editor (VBE)

On the Developer tab, select 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.

Configure a macro to run automatically upon opening a workbook

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, select Visual Basic.
3. In the Project Explorer window, right-click the ThisWorkbook object, and then select View Code.Tip: If the Project Explorer window is not visible, on the View menu, select 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. Select OK in the message box.Note: The cell A1 on Sheet1 also contains the date as a result of running the Workbook_Open procedure.

Automate tasks with the Macro Recorder

Applies To

To automate a repetitive task, you can record a macro with the Macro Recorder in Microsoft Excel. Imagine you have dates in random formats and you want to apply a single format to all of them. A macro can do that for you. You can record a macro applying the format you want, and then replay the macro whenever needed.WindowsMacOS

When you record a macro, the macro recorder records all the steps in Visual Basic for Applications (VBA) code. These steps can include typing text or numbers, clicking cells or commands on the ribbon or on menus, formatting cells, rows, or columns, or even importing data from an external source, say, Microsoft Access. Visual Basic Application (VBA) is a subset of the powerful Visual Basic programming language, and is included with most Office applications. Although VBA gives you the ability to automate processes within and between Office applications, it is not necessary to know VBA code or computer programming if the Macro Recorder does what you want.

It is important to know that you when you record a macro, the Macro Recorder captures almost every move you make. So if you make a mistake in your sequence, for example, clicking a button that you did not intend to click, the Macro Recorder will record it. The resolution is to re-record the entire sequence, or modify the VBA code itself. This is why whenever you record something, it’s best to record a process with which you’re highly familiar. The more smoothly you record a sequence, the more efficiently the macro will run when you play it back.

Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it. For more information, see Show the Developer tab.

Record a macro

There are a few helpful things you should know about macros:

• When you record a macro for performing a set of tasks in a range in Excel, the macro will only run on the cells within the range. So if you added an extra row to the range, the macro will not run the process on the new row, but only the cells within the range.
• If you have planned a long process of tasks to record, plan to have smaller relevant macros instead of having one long macro.
• It is not necessary that only tasks in Excel can be recorded in a macro. Your macro process can extend to other Office applications, and any other applications that support Visual Basic Application (VBA). For example, you can record a macro where you first update a table in Excel and then open Outlook to email the table to an email address.

Follow these steps to record a macro.

1. On the Developer tab, in the Code group, click Record Macro.-OR-Press Alt+T+M+R .
2. In the Macro name box, enter a name for the macro. Make the name as descriptive as possible so you can quickly find it if you create more than one macro.Note: The first character of the macro name must be a letter. Subsequent characters can be letters, numbers, or underscore characters. Spaces cannot be used in a macro name; an underscore character works well as a word separator. If you use a macro name that is also a cell reference, you may get an error message that the macro name is not valid.
3. To assign a keyboard shortcut to run the macro, in the Shortcut key box, type any letter (both uppercase or lowercase will work) that you want to use. It is best to use Ctrl + Shift (uppercase) key combinations, because the macro shortcut key will override any equivalent default Excel shortcut key while the workbook that contains the macro is open. For instance, if you use Ctrl+Z (Undo), you will lose the ability to Undo in that Excel instance.
4. In the Store macro in list, select where you want to store the macro.In general, you’ll save your macro in the This Workbook location, but if you want a macro to be available whenever you use Excel, select Personal Macro Workbook . When you select Personal Macro Workbook, Excel creates a hidden personal macro workbook (Personal.xlsb) if it does not already exist, and saves the macro in this workbook.
5. In the Description box, optionally type a brief description of what the macro does.Although the description field is optional, it is recommended you enter one. Also, try to enter a meaningful description with any information that may be useful to you or other users who will be running the macro. If you create a lot of macros, the description can help you quickly identify which macro does what, otherwise you might have to guess.
6. Click OK to start recording.
7. Perform the actions that you want to record.
8. On the Developer tab, in the Code group, click Stop Recording .-OR-Press Alt+T+M+R .

Working with recorded macros in Excel

In the Developer tab, click Macros to view macros associated to a workbook. Or press Alt+ F8. This opens the Macro dialog box.

Caution: Macros cannot be undone. Before you run a recorded macro for the first time, make sure that you’ve either saved the workbook where you want to run the macro, or better yet work on a copy of the workbook to prevent unwanted changes. If you run a macro and it doesn’t do what you want, you can close the workbook without saving it.

Here’s further information on working with macros in Excel.

Task	Description
Change macro security settings in Excel	Read specific information on available security settings for macros, and their meaning.
Run a macro	There are several different ways you can run a macro, such as using a shortcut key, graphic object, Quick Access Toolbar, a button, or even when opening a workbook.
Edit a macro	You use the Visual Basic Editor to edit the macros attached to a workbook.
Copy a macro module to another workbook	If a workbook contains a VBA macro that you would like to use elsewhere, you can copy the module that contains that macro to another open workbook by using the Microsoft Visual Basic Editor.
Assign a macro to an object, shape or graphic	On a worksheet, right-click the object, graphic, shape, or the item to which you want to assign an existing macro, and then click Assign Macro.In the Assign Macro box, click the macro that you want to assign.
Assign a macro to a button	You can assign a macro to a graphic icon, and add it to Quick Access Toolbar, or the ribbon.
Assign a macro to a control on a worksheet	You can assign macros to forms and ActiveX controls in a worksheet.
Enable or disable macros in Office files	Learn how to enable or disable macros in Office files.
Open the Visual Basic Editor	Press Alt+F11
Find Help on using the Visual Basic Editor	Learn how to find help on Visual Basic elements.

Working with recorded code in the Visual Basic Editor (VBE)

You can work with recorded code in the Visual Basic Editor (VBE) and add your own variables, control structures, and other code that the Macro Recorder cannot record. Since the macro recorder captures almost every step you take while recording, you can also clean up any unnecessary recorded code that serves no purpose in the macro. Reviewing your recorded code is a great way to learn or sharpen your VBA programming skills.