Do you start recording a macro from scratch if you need to modify a small step? Stop doing that and save time by learning how to edit macros in Excel.

Excel macros help you automate repetitive tasks in Excel. You can create a macro by recording your steps on an Excel worksheet or writing an Excel VBA script. Sometimes, a pre-recorded Macro may not work for a new scenario in your worksheet until you make small changes.

Mostly, you’ll start creating a new macro for a new data transformation, analysis, or visualization task. However, you can access the Excel VBA script of the existing Macro and make small changes to make the VBA code fit the new work scenario on Excel.

Now, you might ask how to modify a macro you recorded by performing actions on your Excel worksheet. If you’ve been working on Excel macros at the code level, then you might know the answer already.

If you don’t, not to worry. Keep reading this Excel tutorial until the end to learn different techniques to edit a Macro in Excel so you can reuse previously recorded macros conveniently.

Why Do You Need to Edit Macros in Excel?

The primary reason to edit a macro is to make it fit for reuse in similar Excel projects. Also, when you record a macro containing hundreds of steps, if you make any incorrect actions, you can edit the underlying macro code to rectify the problem. Thus, you can avoid redoing the whole macro from the beginning.

There are other major reasons to edit a macro and these are as outlined below:

1. You received a workbook containing macros from the work or the client. You need to use the macros but also must verify that these macros aren’t going to run any malicious codes on your Excel workbook. Here, you can use your macro editing skills.
2. Recorded macros add redundant codes to the Excel VBA script of the said macro. After recording a macro, you can access its backend code and edit it to remove redundancies. This makes your macro scripts smaller and more agile.
3. You’re still learning Excel VBA and unable to code a whole macro using VBA language. You can use the macro recorder of Excel to create a macro. Then access its codebase to understand what functions the macro is using and their corresponding VBA codes.
4. You can also use your macro editing skills to transform a basic macro into an advanced one. If you know VBA scripting on Excel, edit the macro and start adding logical statements, third-party integration codes, etc., to develop advanced automation in Excel.
5. Sometimes, the Excel macro recorder is unable to add many UI actions you make while recording a macro. To add those in the final VBA script, you must know how to edit macros in Excel.
6. To speed up lengthy macros, you can edit their VBA codes to remove unnecessary actions like ScreenUpdating, scrolling effects, cursor movements, etc.

Now that you’ve learned the rationales behind editing a macro in Excel, find below how to get started with the process.

Preparing Your Excel Workbook to Start Editing Macros

Before you can edit a macro on your Excel workbook, you must make some changes to it. Usually, macros aren’t readily visible. You can find macros in the Developer tab of the Excel desktop app. If you don’t see the Developer tab on your Excel installation, you can’t edit macros.

So, follow the steps mentioned below to configure your Excel workbook for macro editing.

Add Developer Tab in Excel

The Developer tab in Excel is for advanced-level Excel users. So, if you just installed the Excel app on your PC or never edited macros after installing the app, the Developer tab might be absent in the software.

Read this excellent article to learn the ways to enable the developer tab in your Excel app. Once done, proceed with the following steps.

Unhide the Personal Macro Workbook

In Excel, the Personal workbook refers to a hidden workbook called PERSONAL.XLSB or PERSONAL.XLSM.

This hidden workbook is a special file that serves as a global macro storage for Excel. Any macros or custom functions stored in the Personal workbook are available to all other workbooks opened on the same machine.

Therefore, this workbook becomes a place to store commonly used macros or functions that you want to access from any Excel file.

By default, you won’t see the Personal workbook when open a Excel worksheet on your PC. However, you need to visualize this file when editing macros in your Excel workbook.

Here’s how you can access the Personal workbook for macros in your Excel desktop app:

1. Click the View tab on the Excel ribbon menu.
2. Go to the Window commands block.
3. If there is a hidden Personal workbook, the Unhide button will be active.
4. Click Unhide to show the Personal macro workbook.
5. Once you’re done editing the macros, hide the Personal workbook by clicking the Hide button.

Modify Macro Security Settings

Macro security settings in Excel are a set of options that control how Excel handles macros and VBA (Visual Basic for Applications) code in workbooks. There are four macro security settings.

These settings stop hackers from running malicious codes on your Excel workbook and thereby on your PC by sending you macros in random workbooks.

By default, the Disable VBA macros with notifications settings protect you from harmful VBA codes. However, if you must run or edit macros on your workbook, you need to enable VBA macros first.

When you trust the sender of an Excel workbook or create macros yourself, you can enable macros without worrying much. Here’s how to alter the macro security settings:

1. Navigate to the Developer tab.
2. Click the Macro Security button inside the Code commands block.
3. Select the fourth setting: Enable VBA macros.
4. Click OK to save and close the Trust Center dialog box.

If you rarely use macros or edit macros, you should change the Trust Center > Macro Settings to Disable VBA macros without notification setting.

Edit a Macro in Excel From the Developer Tab

The standard way to edit a macro in Excel is by accessing the Macro dialog box from the Developer tab. Here are the steps you can follow along:

1. Go to the Developer tab in Excel.
2. Click the Macros button within the Code block.
3. The Macro dialog box should pop up.
4. This window should show all the saved macros in your Excel workbook.
5. Select the macro you want to edit.
6. Click the Edit button on the right side.

The underlying VBA codebase for the selected macro will open in the Excel VBA Editor.

Now, you can edit several functions and steps of the macro. Once done editing the macro, click the Save button on the Excel VBA Editor toolbar.

Edit Macros in Excel Using Keyboard Shortcuts

If you like to use the keyboard to work fast on your Excel project, this method is especially for you. Find below various hotkeys and keystrokes to edit a macro without using the mouse:

1. On the Excel workbook that has macros, press Alt + F8 to bring up the Macro dialog box.
2. Use the Up Arrow and the Down Arrow keys to select the macro you’d like to edit.
3. Press the Tab key twice to switch the selection to the right-side menu of the Macro dialog box.
4. The first selection should be Run.
5. Press the Down Arrow key to go to the Edit button.
6. Hit Enter to execute.

Now you can start editing the macro by following these instructions:

1. The VBA script for the selected macro will open in the VBA Editor interface.
2. The cursor should already be in the code area.
3. Make the changes you want and press Ctrl + S to save the module.
4. Press the Alt + Q shortcut key combination to close the Excel VBA Editor.

Edit a Macro in Excel Using the Excel VBA Editor

Suppose, you opened the Excel VBA Editor to edit a macro. Suddenly, it strikes your mind that another macro also needs editing. Do you close the Editor and start the process from the beginning? Of course not!

The Excel VBA Editor tool has access to all the macros in all the open workbooks. From here, you can even access those macros that you saved in the Personal macro workbook.

Find below the effortless steps to edit all macros in your Excel software from the VBA Editor interface:

1. Press Ctrl + R keys to open the Project Explorer sidebar on the Excel VBA Editor.
2. You should see a tree of objects.
3. Under the Modules folder, all the macros should be listed.
4. The one you’re already in should be highlighted in grey.
5. Double-click the other module (macro) you want to edit.

Here’s how you can further edit the script and save it for future use:

1. Its VBA script will open on the right-side code window.
2. Make the changes you need and hit Ctrl + S to save the changes.
3. Click the close button to close the Excel VBA Editor tool.

Edit a Module of a Macro in Excel

Whenever you create a new module, Excel names it as Module 1, Module 2, etc. This isn’t an efficient way of organizing all the modules of an Excel workbook.

You should rename the modules to reflect the name of the macro it’s linked to. It helps you and any other users of these macros to know what automation these modules accomplish.

Find below the quick steps to rename Excel VBA modules:

1. Open the Excel VBA Editor on your Excel workbook by pressing Alt + F11 keys together.
2. Select the module you want to rename in the left-side panel of the VBA Editor.
3. Hit the F4 key to call the Module Properties panel.
4. Click the Alphabetic tab.
5. Double-click the stock module name to start editing.
6. Type a name of your choice.

That’s it! Excel VBA Editor will rename the module with the custom name instantly. The same should show up on the object tree in the left-side panel of VBA Editor.

Editing a Macro in the Form Control

If the macro you’re using is embedded into a Form Control, here’s how you can edit it:

1. Right-click the Form Control button.
2. Click Assign Macro from the context menu.

Assign Macro takes you to the Macro dialog box. There, you can click the Edit button directly to customize the VBA code behind the macro. Here, you don’t need to select any macro manually. Assign Macro automatically highlights the macro linked to the Form Control.

Conclusions

Editing macros to customize their code and make it work for your Excel projects is an indispensable skill if you wish to call yourself an expert-level Excel user. So, try out all the above methods in your Excel workbook to learn several ways to edit a macro.

This is also a great way to learn macro functions in the VBA language if you’re learning Excel VBA.

Don’t forget to share your insights, challenges, or experiences of editing macros to achieve advanced automation by leaving a comment in the comment box. Let’s keep the conversation going and learn Excel VBA together!