How to: Use Microsoft Office Excel macros to speed up work

You can record repetitive tasks in Excel by turning on the macro recorder and do in the task. When you turn off the recorder, Office Excel converts every click and key press into a series of commands and functions that are stored as a module in a workbook or in Excel. When you need to perform the task again, run the macro, and the commands and functions are performed automatically in the order in which they were recorded.

Because Excel is running the commands, tasks are performed at the speed of a computer and without interruption. Manually, a task could take 10 or more seconds, but by using a macro, a range can be formatted in a fraction of a second. The macro not only saves you time, it saves you from having to remember a lot of tedious details. With a macro, the formatting is exactly the same every time you run it.

Think about the tasks that you need to automate when you work with certain types of worksheets. For example, you could create a macro that quickly hides a group of monthly columns so that only the summary data shows. You could create another macro to show all of the details again. You could even create a macro to format cells so that long headings wrap around the cells.

Create macros

To create macros, you need to first enable the Developer tab in Office Excel and change your security for macros. Then, you can record and run macros when you need them. To run a macro, a user does not necessarily need the Developer tab.

Enable macros

  1. If you do not see a Developer tab at the top of your Excel window, click the File tab, and then click Options.
  2. In the left navigation pane, click Customize Ribbon, and then tick Developer under Main Tabs.
  3. Click OK.
  4. Click the Developer tab, and in the code group, click Macro Security.

    You can modify the macro code in Visual Basic, but you are not required to do so.

  6. Select Enable all macros (not recommended; potentially dangerous code can run), and then click OK.


Tip: Enabling macros will not harm your computer, and neither will your macro. However, you should keep in mind that by enabling all macros to run in Excel, you are not only allowing your own macros to run but also potentially harmful macros. By setting macro security to Disable all macros with notification, a security warning will appear when you open an Excel workbook that contains a macro. To enable macros, click Options, and then select Enable this content. See macro security topics in Office Excel Help for more information.

Create the macro

  1. After deciding that you would like to create a macro to handle a repetitive task, start by running through the steps. For example, select a range, add a border, add a fill color, and change the font size. Also, think about how you will use the macro. In this example, you know that, in practice, you will run the macro after you make a range selection. So, when you create the macro, start recording after you have made a selection. Try to perform your task in as few steps as possible to save time.
  2. When you are ready to record your macro, on the Developer tab in the Code group, click Record Macro, and enter the following information:
    • Macro name. Type a name that begins with a letter.
    • Shortcut key. Type a letter. You can run your macro by pressing Ctrl and this shortcut key.
    • Store macro in. Choose where the macro is saved: the current workbook, a new workbook, or your Personal Macro Workbook. If you choose the latter, the macro will be available any time you use Excel.
    • Description. Type an optional description.
  3. Click OK, and the Macro Recorder starts.
  4. Run through your steps, and then click Stop Recording.
  5. To see what your macro looks like in Visual Basic code (optional), click Visual Basic.

Test your macro

  1. Select one or more cells on a worksheet.
  2. Press CTRL and the macro shortcut key, or on the Developer tab in the Code group, click Macros. Click the macro name, and then click Run. Office Excel performs the commands contained in the macro.

If you make a mistake while recording, you can delete the macro and start over, or if you understand the basics of programming with Visual Basic, you can modify the macro code. The following figure shows part of the macro code that was used to format the table above, open in the Microsoft Visual Basic Editor. The section of code on the right (that starts with Sub FormatSelection) adds a colour and border around a range of cells. Keep in mind that you are not required to work with macro code to record and use macros, because Office Excel creates all of the code for you.

Sourced from the Microsoft in Education website.