Use Macros in Excel

Sometimes a spreadsheet has a process that has to be repeated with some regularity. You can set up a macro to repeat that process easily without requiring you to go through all the steps. Understand how to use macros in Excel to save time.

Steps

Use Macros with Excel (Except for Excel 2008 for Mac)

  1. Verify that your Excel preferences allow you to run macros. This setting is in the "Security" section, in most versions of Excel.
  2. Open the workbook that contains or will contain the macro.
  3. Verify that macros are enabled in the specific workbook, which will let the macro be run.
  4. Consider how you want to use the macro. Macros can be run from a menu, connected to a button in the workbook, set to run automatically upon opening a worksheet, or tied to a keyboard command.
  5. Record or write the macro at this time, to repeat the particular process that you want automated, if your workbook doesn't already have a macro installed that you want to use.
    • To record the macro, find the "Record New Macro�" command. This can be in the "Macros" section of your "Tools" drop-down menu or ribbon, inside the macro window (again, usually found in "Tools"), or on a toolbar, depending on your version of Excel, your computer system, and your specific Excel setup.
  6. Open the macro menu, usually in the "Tools" section of Excel.
  7. Test-run the macro you want to use, making sure it works properly.
  8. Assign the macro to whatever button, key command, or worksheet auto-run you want to use with it.
  9. Run the macro as needed.

Use Macros with Excel 2008 for Mac

  1. Open the workbook where you need the macro.
  2. Open "Script Utility," found in the "AppleScript" folder, inside your global "Applications" folder. (The global "Applications" folder is the one in your computer, before you open your main user account folder.)
  3. Hit the "record" button and follow the process you want in the macro.
  4. Test that the recorded macro works as intended. (Not all actions are scriptable, meaning you cannot record them for an AppleScript.)
  5. Save the recorded AppleScript.
  6. Run the AppleScript as needed.

Tips

  • If you make your own macro, it's easiest to troubleshoot if you record each piece in small sections. Test each section independently, and you can re-record a section as necessary. Once it all works, compile the macro into a single program.
  • If the macro is created on a computer system other than the one it is used on, the macro may require a bit of tweaking to run properly. Visual Basic for Applications (VBA, the programming language for Excel macros) differs a bit between the Windows and Mac versions of Excel.
  • If you want the macros to be automatically enabled, some versions of Excel allow you to "Save As�" an XLSM file, a "macro-enabled workbook" and to set preferences for particular folders where macros will always be accepted.

Warnings

  • Macros can also damage Excel files, by accident or on purpose. Make sure to back up your spreadsheet before you start playing with a macro.
  • Computer viruses can hide as macros on Excel workbooks. If a workbook has a macro without anyone giving an alert, verify that the macro was intentionally added by someone you trust.

Related Articles

Sources and Citations

Retrieved from "https://kipkis.com/index.php?title=Use_Macros_in_Excel&oldid=276485"