Editing Macros in Visual Basic Editor

If you run a macro and it does not perform the desired task as you intended it to, you do not need to re-record the macro. Instead, you can modify the macro's recorded commands in Visual Basic Editor or delete commands you inadvertently recorded. To open a macro and edit it in the Visual Basic Editor, follow these steps:

  1. If you saved the macro you want to edit in a particular workbook, open the workbook file in which you created and saved the macro.

  2. Open the Tools menu, point to Macro , and choose Macros . The Macro dialog box appears, as shown in Figure 13.8.

    Figure 13.8. You can edit any recorded macro.

    graphics/13fig08.gif

  3. Click the name of the macro you want to edit.

  4. Click the Edit button. Visual Basic Editor appears and displays the commands that compose the selected macro, as shown in Figure 13.9.

    Figure 13.9. Use Visual Basic Editor to edit your macro.

    graphics/13fig09.gif

  5. To delete a command or any text, drag over the command or text to highlight it and then press the Delete key.

  6. To add a command or text, type the command or text where you want it to "play" in the macro.

  7. To save your macro, open the File menu and choose Save , or press Ctrl+S .

Caution

graphics/cman.gif

If you try to edit a macro stored in the Personal Macro Workbook (Personal.xls), Excel displays a warning box indicating that you cannot edit a macro stored in a hidden workbook file. Click OK to close the warning box, and then click the Macro dialog box's Cancel button to close it. In Excel, open the Window menu and choose Unhide . Click PERSONAL.XLS and click OK . Repeat steps 13 to open the macro in Visual Basic Editor.


These steps make it sound as though typing macro commands is easy. You soon realize, however, that you must know the command to type it. When you start using Visual Basic Editor, use it to delete superfluous commands and edit any text entries that are part of the macro. For example, if you have a macro that inserts your company's logo into a cell , the macro contains the location of the logo's graphic file, as shown in the fourth line of the following example:

 Sub InsertLogo() InsertLogo Macro Macro recorded 11/21/2003 by Sally Rogers ActiveSheet.Pictures.Insert ("C:\AcmeInc\logo.gif").Select End Sub 

If you move the graphic file, you can open the macro in Visual Basic Editor and edit the path to the file rather than record a new macro.

Note

graphics/nman.gif

When you're ready to do more with Visual Basic Editor, purchase a book that's devoted to it, such as The Complete Idiot's Guide to Visual Basic 6 .




Absolute Beginner's Guide to Microsoft Office Excel 2003
Absolute Beginners Guide to Microsoft Office Excel 2003
ISBN: 0789729415
EAN: 2147483647
Year: 2002
Pages: 189

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net