How to Modify Macros in Excel

Once you record a Macro, it captures each and every action you perform in Excel. If you have accidentally entered a different name in the cell and erased them, these commands are recorded too. Or, maybe it could be an unintended mouse click in your spreadsheet. Nonetheless, these incidents are pretty common when recording Macros at first.

Fortunately, you can modify Macros and correct the mistakes you’ve made. This is because Excel saves all recorded Macros in the form of a VBA code. So, if you have little to basic knowledge of VBA language, you can change the code as required.

Step 1: Unhide Personal Workbook

If you have saved Macros in Personal Workbooks, there’s a preliminary step you should take. Since Excel hides personal workbooks by default, you would have to unhide the workbook in order to edit Macros.

1. Open Excel File.
2. Navigate to View Tab. From the Window group, click on Unhide.
   
3. On Unhide window, select your Hidden workbook and hit OK.
   

Step 2: Modify Macros

To quickly bring up the Microsoft Visual Basic for Applications window, you can press Alt + F11 shortcut key. In this window, double-click on the Module you wish to edit the Macro. You should see a VBA code in the Module window. Edit the code as required.

Example: Let us suppose we have recorded the Macro to find and highlight the lowest number from the data using Conditional Formatting. Here’s our original code. 

But, we now wish to highlight the three numbers that are the lowest in the value. For this, we will locate the rank and change the number to 3. When done, hit the Run button or close the Window.

Alternatively, you could also edit the codes from the Excel Ribbon. You can go to either the Macros menu or Visual Basics. 

From Macros Menu

1. On your workbook, go to Developer Tab.
2. Navigate to Code section and select Macros.
   
3. On Macro window, select the Macro Name you want to modify. Then, click Edit.
   
4. You should see Microsoft Visual Basic for Applications window. Double-click on the Module to modify the VBA code.
   
5. Change the code as you wish. When done, close the window.

From Visual Basics Editor

1. From Developer Tab, click Visual Basic in the Code section.
   
2. On Microsoft Visual Basic for Applications window, hover over the Modules menu on the left panel. Click on the Module twice.
   
3. Now, make changes in the VBA code as required. 

After you modify the VBA code in Macro, we suggest you run the code. For this press F5 or click on the Run button. If there is even the smallest mistake in the VBA code, it can lead to different types of Run-Time Errors. 

Why Can’t I Edit a Macro in Excel?

You won’t be able to edit a Macro in Excel if the workbook is hidden. Or, when you’ve disabled the Macros. During such instances, Excel might as well send you the “Cannot edit a macro on a hidden workbook” error.

To fix this, you can unhide the workbook from the View tab and enable Macros from Excel Options. For a more detailed guide, check out our other article on Cannot Edit a Macro on a Hidden Workbook article.