Use Microsoft Office Excel 2007 macros to speed up work

Learn how to use Microsoft Office Excel 2007 for creating macros that can help save you time for more important tasks.

Use Microsoft Office Excel 2007 macros to speed up work

Macros save you time, and we know how busy a teacher’s life can be. When you find yourself performing a task or series of tasks repeatedly, it is helpful to create a macro in an Office Excel 2007 worksheet. Watch the video

Because Excel 2007 is running the commands, tasks are performed at the speed of a computer and without interruption. For example, a macro was used to format the range in the following figure. Manually, the task could take 10 or more seconds, but by using the macro, the range was 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.

Start by turning on the macro recorder and running through the tasks. When you turn off the recorder, Office Excel 2007 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 2007. The next time you need to perform the tasks, run the macro, and the commands and functions are performed automatically in the order in which they were recorded.

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 2007 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 2007 window, click the Microsoft Office button, and then click Excel Options.
  2. In the left navigation pane, click Popular, and then under Top options for working with Excel, select the Show Developer tab in the Ribbon check box.
  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.

  5. 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 2007, 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 2007 workbook that contains a macro. To enable macros, click Options, and then select Enable this content. See macro security topics in Office Excel 2007 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:
    1. Macro name. Type a name that begins with a letter.
    2. Shortcut key. Type a letter. You can run your macro by pressing CTRL and this shortcut key.
    3. 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 2007.
    4. Description. Type an optional description, such as whether you plan to make your macro-enabled workbook available to your students.
  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.

Teacher Tips

   

  • Custom macros

    Macros are fast and forgiving. If the custom macro you create needs a slight correction, you can simply re-record the actions within the macro, and replace the previous one.

  • Macros can save you time in the long run, especially over an entire school year.

    You should first decide how repetitive and time-consuming the task you wish to automate truly is.

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 2007 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 color 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 2007 creates all of the code for you.

 

   
  • Microsoft Business Productivity Online Suite (BPOS)

    Microsoft Business Productivity Online Standard Suite is a set of online services designed to give your academic organization integrated communications with high availability, comprehensive security, and simplified IT management.