Edit Macros in Excel

If you record a macro and later want to make changes without having to re-record it, it can be edited in the Visual Basic window. This lesson introduces you to the Visual Basic (also called VB or VBA) programming language—the code Excel uses to record macros. Using the Visual Basic language and the Visual Basic editor, you can make minor changes to your macros once you have recorded them.

The best way to learn about Visual Basic is to view existing code. In this lesson we’ll look at how to view and edit the code for an existing macro.

Unhide the Personal Workbook

Before you start editing a macro, you need to think about where it’s saved. If it’s saved in the personal workbook, you’ll need to unhide it first. The personal workbook is a special file that stores universal macros. It’s saved on your computer and opens every time you open Excel. However, this file is hidden by default, so you wouldn’t know it’s there unless you unhide it. The personal workbook must be unhidden if you want to edit any macros saved in it.

1. Click the View tab.
2. Click the Unhide Window button.
3. Ensure PERSONAL is selected and click OK.
   

The personal workbook opens in another window. In order to edit the macros in it, it just has to be visible. You can edit the macros in any open file.

Once you’re done editing the personal workbook, you’ll want to hide it once again.

Edit a Macro

1. Click the Developer tab.
2. Click the Macros button.

   The Macro dialog box appears. Here you can see the macros that you have recorded.

3. Select a macro to edit.
4. Click the Edit button.
   

   The Microsoft Visual Basic for Applications program appears. What may look like jumbled text is actually Visual Basic code—the language that was used by Excel to record the macro you created.

   You don’t have to learn Visual Basic to be proficient at Excel however, knowing the basics can be helpful if you ever want to modify an existing macro. If you take a close look at the code for your macro, some of the procedures should make a little sense to you. For example, if your macro selects some cells in a workbook, you may see the text “Select” or “Selection”.

   You can delete sections of code to delete certain actions from the macro or edit the code to change the macro’s actions.

5. Edit the macro’s code as desired.
6. Click the Save button.
7. Close the Visual Basic for Applications program window.
   

The Visual Basic Editor window closes and you return to the main Excel window.

Delete a Macro

1. Click the Developer tab.
2. Click the Macros button.

   The Macro dialog box appears. Here you can see the macros that you have recorded.

3. Select a macro.
4. Click the Delete button.

The macro is deleted.

Test an Edited Macro

After you’ve edited the Visual Basic code, it’s a good idea to run the macro again to make sure it performs how you want it to.

1. With the sheet where you want to run the edited macro selected, click the Macros button on the Developer tab.
2. Select the edited macro.
3. Click the Run button.
   

The macro runs and any changes made to the VBA code are reflected in the worksheet.