Write a Simple Macro in Microsoft Excel

This article teaches how to create simple macros for Excel spreadsheets.

Steps

Enabling Macros

1. Open Excel. The process for enabling macros is the same for Excel 2010, 2013, and 2016. There is a slight difference for Excel for Mac, which will be detailed below.
2. Click the File tab.
   • In Excel for Mac, click the "Excel" menu.
3. Click Options.
   • In Excel for Mac, click the "Preferences" menu option.
4. Click the Customize Ribbon option.
   • In Excel for Mac, click "Ribbon & Toolbar" in the "Authoring" section.
5. Check the Developer box in the list on the right.
   • In Excel for Mac, you'll see "Developer" in the "Tab or Group Title" list.
6. Click OK. You'll see the Developer tab appear at the end of your tab list.

Recording a Macro

1. Practice your macro sequence. When you record a macro, anything you click or do will be recorded, so a single slip-up can ruin the whole thing. Run through the commands you'll be recording a couple times so that you can do them without hesitation and without misclicks.
2. Click the Developer tab.
3. Click Record Macro. You'll find this in the Code section of the ribbon. You can also press Alt+T+M+R to start a new macro (Windows only).
4. Give the macro a name. Make sure that you'll be able to easily identify it, especially if you're going to be creating multiple macros.
   • You can also add a description to explain what the macro will accomplish.
5. Click the Shortcut key field. You can assign a keyboard shortcut to the macro to easily run it. This is optional.
6. Press ⇧ Shift plus a letter. This will create a Ctrl+⇧ Shift+letter keyboard combination to start the macro.
   • On Mac, this will be a ⌥ Opt+⌘ Command+letter combination.
7. Click the Store macro in menu.
8. Click the location you want to save the macro. If you're only using the macro for your current spreadsheet, just leave it on "This Workbook." If you want the macro available for any spreadsheet you work on, select "Personal Macro Workbook."
9. Click OK. Your macro will begin recording.
10. Perform the commands you want to record. Pretty much anything you do will now be recorded and added to the macro. For example, if you run a sum formula of A2 and B2 in cell C7, running the macro in the future will always sum A2 and B2 and display the results in C7.
    • Macros can get very complex, and you can even use them to open other Office programs. When the macro is recording, virtually everything you do in Excel is added to the macro.
11. Click Stop Recording when you're finished. This will end the macro recording and save it.
12. Save your file in a macro-enabled format. In order to preserve your macros, you'll need to save your workbook as a special macro-enabled Excel format:
    • Click the File menu and select Save.
    • Click the File Type menu underneath the file name field.
    • Click Excel Macro-Enabled Workbook.

Using a Macro

1. Open your macro-enabled workbook file. If you have closed your file before running your macro, you'll be prompted to enable the content.
2. Click Enable Content. This appears at the top of the Excel spreadsheet in a Security Warning bar whenever a macro-enabled workbook is opened. Since it's your own file, you can trust it, but be very careful opening macro-enabled files from any other source.
3. Press your macro shortcut. When you want to use your macro, you can quickly run it by pressing the shortcut you created for it.
4. Click the Macros button in the Developer tab. This will display all of the macros that are available in your current spreadsheet.
5. Click the macro you want to run.
6. Click the Run button. The macro will be run in your current cell or selection.
7. View a macro's code. If you want to learn more about how macro coding works, you can open the code of any macro you've created and tinker with it:
   • Click the Macros button in the Developer tab.
   • Click the macro you want to view.
   • Click the Edit button.
   • View your macro code in the Visual Basic code editing window.

Related Articles

• Use Macros in Excel
• Create a User Defined Function in Microsoft Excel
• Create Pivot Tables in Excel
• Apply Conditional Formatting in Excel
• Use AutoFilter in MS Excel
• Zoom Pages of Worksheet in Kingsoft Office Spreadsheets 2013

See also

• Create a Sin and Cos Circle in Excel
• Add a Header or Footer in Excel 2007
• Calculate CAGR in Excel
• Use Excel for Your Finances
• Parse Data in Excel
• Create Custom Lists in Excel
• Remove Leading or Trailing Zeros in Excel
• Remove Spaces Between Characters and Numbers in Excel
• Use Vlookup With an Excel Spreadsheet
• Create a User Defined Function in Microsoft Excel

Read more ...

What links here

• Unshare an Excel Workbook
• Copy Formulas in Excel
• Create a Custom Macro Button in Excel
• Create a User Defined Function in Microsoft Excel
• Insert Hyperlinks in Microsoft Excel
• Separate First Names and Last Names Into Separate Fields in a Microsoft Excel List
• Use Macros in Excel
• Use the IF Function in Spreadsheets
• Write a Complicated Microsoft Office Macro
• Program in Visual Basic .NET (VB.NET)