SUBSCRIBE to Windows IT Pro Magazine & SAVE 30%     Register today for your FREE 'To The Point' SharePoint eNewsletter
     
     

Automating your work with Excel macros

Automating your work with Excel macros introduction

You can record a macro to automate repetitive tasks. Macros are used to automate repetitive tasks. This section will discuss macros and how they can be created and managed in Microsoft Excel.



Understanding macros

From time to time, you will find yourself performing a sequence of menu commands over and over. You can automate that sequence by creating a macro, which is simply a recording of those commands. Much like a tape-recorded message, you record the sequence of commands once. In the future, you simply “press play” and the commands are executed exactly as they were recorded. You can add macros to keystrokes, menus, or toolbars to make them more accessible.

Note that macros are not used as frequently as they were in older programs. Many of the tasks that were achieved by recording macros, particularly in older versions of Lotus, are now features included in Excel. BEFORE RECORDING A MACRO, MAKE SURE THERE ISN’T AN EASY-TO-USE FEATURE OF EXCEL THAT ALREADY DOES WHAT YOU’RE TRYING TO ACCOMPLISH!

Macros have become useful for computer programmers and developers, who can write (not record) macros in Visual Basic, a programming language. These complex macros can perform extraordinary tasks for customization and automation of office processes.

As a user, you will not generally program Visual Basic macros. You may record a macro from time to time, however. For example, you might record a macro that automatically selects and prints a range of your workbook. Or you might record a macro that sets the printer to the fax software, faxes your worksheet, then sets the printer back to your normal printer. Notice these are strings of simple commands. Such are candidates for a macro.



Record a macro
  1. Choose Tools → Macro ? Record New Macro.
  2. The Record Macro dialog box appears.
  3. Enter a name for the macro.
  4. Generally, you should store your macros in the Personal Macro Workbook. Choose Personal Macro Workbook from the Store Macro In drop-down list.
  5. In the Macro Options dialog box, you can assign the macro to a shortcut key.
  6. Click OK.
  7. Do the sequence of commands that you want recorded
  8. Click the Stop button that appears in the macro toolbar.

The real art of recording a macro is ensuring that you only record the steps required to automate the task--nothing more, nothing less--and to replay that task in future, perhaps more generic situations.



Use a macro

To run a previously recorded macro, do one of the following:

  • Use the tool or keystroke you assigned

or

  1. Choose Tools → Macro.
  2. Select the macro you desire and click Run.


Create a toolbar button to run a macro

You can create a button that, when clicked, executes a macro.

  1. Right-click a toolbar and choose Customize.
  2. Click the Commands tab.
  3. Click Macros in the Categories list.
  4. Drag Custom Button to the toolbar to create a button.
  5. Right-click the button and choose Assign Macro. A dialog box will appear in which you can select the macro that the button should execute.

Notes & tips. While in the Customize mode (right-click a toolbar and choose Customize) you can:

  • Right-click the button and enter the button name in the Name box.
  • Right-click the button and you can choose to show the image of the button only (Default Style), Text Only, or Image and Text.
  • Right-click the button and you can select the button image (Change Button Image) or edit the button image (Edit).