feed

How to Write a Simple Macro in Microsoft Excel

admin

While Excel is full of time-saving features like keyboard shortcuts and templates, you can save even more time by creating macros to complete repetitive tasks. This wikiHow teaches how to create simple macros for Excel spreadsheets.

[Edit]Steps

[Edit]Enabling The Developer Tab

  1. Open Excel. The process for showing the Developer tab is the same for many versions of Excel for Windows. There is a slight difference for Excel for Mac, which will be detailed below.
    Write a Simple Macro in Microsoft Excel Step 1 Version 3.jpg
  2. Click the tab. It's in the editing ribbon above your document space.
    Write a Simple Macro in Microsoft Excel Step 2 Version 4.jpg
    • In Excel for Mac, click the "Excel" menu at the top of your screen.
  3. Click . You might find this option at the bottom of the menu.
    Write a Simple Macro in Microsoft Excel Step 3 Version 4.jpg
    • In Excel for Mac, click the "Preferences" menu option.
  4. Click . It's in the panel on the left side of the window.
    Write a Simple Macro in Microsoft Excel Step 4 Version 4.jpg
    • In Excel for Mac, click "Ribbon & Toolbar" in the "Authoring" section.
  5. Check the box next to "Developer" to check it. You'll see this on the right side of the window under the header, "Main Tabs."
    Write a Simple Macro in Microsoft Excel Step 5 Version 4.jpg
    • In Excel for Mac, you'll see "Developer" in the "Tab or Group Title" list.
  6. Click . You'll see the Developer tab appear in your tab list.
    Write a Simple Macro in Microsoft Excel Step 6 Version 4.jpg

[Edit]Recording a Macro

  1. Click the tab.You should see this in the editing ribbon above your editing space. If you don't see it, you need to enable it again.
    Write a Simple Macro in Microsoft Excel Step 7 Version 4.jpg
  2. Click . You'll find this in the Code section of the Developer tab. You can also press to start a new macro (Windows only).
    Write a Simple Macro in Microsoft Excel Step 8 Version 4.jpg
  3. 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.
    Write a Simple Macro in Microsoft Excel Step 9 Version 4.jpg
    • You can also add a description to explain what the macro will accomplish.
  4. Click the field. You can assign a keyboard shortcut to the macro to easily run it. This is optional.
    Write a Simple Macro in Microsoft Excel Step 10 Version 4.jpg
  5. Press plus a letter. This will create a +letter keyboard combination to start the macro. If you don't press Shift, then you run the risk of overwriting any keyboard shortcuts that already exist. For instance, if you entered z in the box without pressing Shift, you'd overwrite the "Undo" shortcut (which is Ctrl + Z)
    Write a Simple Macro in Microsoft Excel Step 11 Version 3.jpg
    • On Mac, this will be a +letter combination.
  6. Click the drop-down. More options will drop down for you.
    Write a Simple Macro in Microsoft Excel Step 12 Version 4.jpg
  7. 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."
    Write a Simple Macro in Microsoft Excel Step 13 Version 4.jpg
  8. Click . Your macro will begin recording.
    Write a Simple Macro in Microsoft Excel Step 14 Version 4.jpg
  9. 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.
    Write a Simple Macro in Microsoft Excel Step 15 Version 4.jpg
    • 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.[1]
  10. Click when you're finished. This will end the macro recording and save it.
    Write a Simple Macro in Microsoft Excel Step 16 Version 4.jpg
  11. 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:
    Write a Simple Macro in Microsoft Excel Step 17 Version 4.jpg
    • Click the File menu and select Save.
    • Click the File Type menu underneath the file name field.
    • Click Excel Macro-Enabled Workbook.

[Edit]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.
    Write a Simple Macro in Microsoft Excel Step 18 Version 4.jpg
  2. Click . 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.
    Write a Simple Macro in Microsoft Excel Step 19 Version 2.jpg
  3. Press your macro shortcut (if you want to use a shortcut key). When you want to use your macro, you can quickly run it by pressing the shortcut you created for it.
    Write a Simple Macro in Microsoft Excel Step 20 Version 2.jpg
  4. Click the button in the Developer tab (if you want to use the menu). This will display all of the macros that are available in your current spreadsheet.
    Write a Simple Macro in Microsoft Excel Step 21 Version 2.jpg
  5. Click the macro you want to run.
    Write a Simple Macro in Microsoft Excel Step 22 Version 2.jpg
  6. Click the button (if you have custom buttons enabled). The macro will be run in your current cell or selection.
    Write a Simple Macro in Microsoft Excel Step 23 Version 2.jpg
    • If you don't have custom buttons enabled, you can go to Customize Ribbon and add it there.[2]
  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:
    Write a Simple Macro in Microsoft Excel Step 24 Version 2.jpg
    • 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.

[Edit]Related wikiHows

[Edit]References

[Edit]Quick Summary


Older Post Newer Post