How and Why to Write Microsoft Excel Macros

Excel macros are a powerful way to automate your work. In simple terms, Microsoft Excel macros are a way to record your actions within the program and play them back at the touch of a key. Macros can also be written to perform customized actions, creating what amounts to a miniature application within Excel.

Recording Excel Macros
The simplest way to learn Excel macros is to record one. Let's say that you are in charge of collecting a sales spreadsheet from your coworkers, gathering a few cells from each copy and compiling them into a single reporting spreadsheet. You could tediously cut and paste the information from each sheet, or you could record the steps in a macro. Once the macro was created, you could harvest the data from each sheet with a few key clicks. To record a macro, follow these steps:

• From the menu bar, click Tools, Macro, Record New Macro.
• In the Record Macro dialog box, enter the name of the macro. You can also assign a shortcut key to the macro. When you're finished, click OK.
• A small window will appear labeled Stop.
• Carry out the steps you'd like to record. Data entry, mouse clicks and menu selections are all recorded.
• When you're finished, click the blue square button in the Stop window. Your macro is saved for further use.

To access saved macros, click Tools, Macro, Macros. The macro dialog box lets you run, delete, edit or change the options of saved macros.

Under The Hood
Microsoft has created a more powerful macro creation tool by incorporating the Visual Basic Editor. Visual Basic is a full-blown programming language that allows you to create macros that are essentially fully functional programs. There are many resources to learn macro programming available on the Internet and at Microsoft's Office Web site. To create a programmable macro:

• From the menu bar, click Tools, Macro, Macros.
• In the Macro dialog box, enter the name of the new macro and click the Create button.
• Excel will launch the Visual Basic Editor where you can enter code to program the new macro.
• When you're done, exit the editor and your macro will be saved.