How to Edit Macros in Excel (2 Ideal Methods)

Microsoft Excel’s VBA Macro is one of the tools that can perform any operation on any worksheet with ease. It can save a lot of time also. VBA Macro paved the way to analyze a large number of data efficiently. Editing a VBA Macro is an essential part of working with this application. In this tutorial, you will learn how to edit macros in Excel with 2 ideal methods and proper illustrations.

How to Unhide Macro in Personal Workbook

Your macros can be saved anywhere. If the personal workbook is hidden, you may have to unhide it first. The personal workbook is a specific file that saves entire macros. It’s saved on your computer and opens every time you open Excel. Nevertheless, this file is hidden by default. To see whether it is there or not, you have to unhide it. You must unhide the personal workbook to edit any macros saved in it.

First, click on the View tab. From the Window group, select Unhide button.

After that, you will see an Unhide dialog box.
Here, select the personal workbook and click on OK.

Finally, you will see the personal workbook with macros in it. Now, you can edit macros whenever you want.

Indeed it is quite easy to edit macros in Excel. If you have enabled macros, you can open the VBA editor anytime. Then you can edit that.
We are going to provide you with two ways to edit the macros. Let’s see them one by one.

1. Using Keyboard Shortcut to Edit Macros in Excel

If you are used to working with keyboard shortcuts and want to make your operation faster, this is going to be a go-to method for you. We are showing you two keyboard shortcuts to go to the VBA editor. You can choose any of them, it’s up to you.

1.1. Applying Keyboard Shortcut to Open Macros Dialog Box in Excel

This keyboard shortcut will open a Macro dialog box. From there, you can choose any macros and edit.

First, press Alt+F8 on your keyboard. A Macro dialog box will appear.

Now, select any macro from the Macro name list.

Then, click on Edit.

After that, it will take you to the VBA editor.

Finally, from the VBA editor, you can edit the macros according to your necessity.

1.2. Opening Excel VBA Editor Directly Using Keyboard Shortcut

If you got the workbook from other sources, it is wise to edit macros by following the previous method. But, if this is your own workbook, you can follow this simple method. You know all the modules’ and macros’ names in your workbook, right? So, you can go to any module anytime and edit the macros.

Firstly, press Alt+F11 to open the VBA editor.

Now, choose your module with a Double Click on it.

Finally, your VBA Editor is open and you can edit the macros easily.

Read More: How to Remove Macros from Excel

2. Editing VBA Codes with Macros Command in Excel

If you are a person who loves to work with user interfaces, this one is for you. There is a Macros button in Microsoft Excel’s View ribbon. From there, you can open the Macro dialog box and edit the macros. Let’s see step by step.

First, go to the View tab. You will see the Macros button to the right.

Otherwise, you will find the Macros option in the Developer tab

Then, click on Macros.
After that, the Macro dialog box will open.

Here, choose any macro from the Macro name list and then click on Edit.

As a result, you will get your selected macro. Now, you can start editing.

Debug and Run Excel VBA Macros after Editing

Up to this point, we have discussed how to open the VBA editor to edit macros in Excel. In this section, we will edit a macro and test that. We provided you with a practice workbook. From that workbook, we are going to edit the macro. Take a look at this dataset which shows the Marital Status’ of 7 persons in a group with their Names.

Here, we have some blank cells in the dataset. Our goal was to fill those empty cells with the word Unmarried.
For this, we provided the following VBA code.

Sub FindNext_empty_value()

Dim search_value As Variant
search_value = InputBox("Which Value Do You Want to Search ?", "Search Value")

If StrPtr(search_value) = 0 Then
  Exit Sub
End If

Application.DisplayAlerts = False
Dim search_range As Range
On Error Resume Next
Set search_range = Application.InputBox("Select Your Range of Cells", "Search Range", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True

If search_range Is Nothing Then
  Exit Sub
End If

Dim FindRng As Range
Set FindRng = search_range.Find(what:=search_value)

counter = 0

Do
  counter = counter + 1
  FindRng.value = "Unmarried"
  Set FindRng = search_range.FindNext(FindRng)
Loop While Not FindRng Is Nothing

MsgBox "Total " & counter & " empty cells were replaced with: Unmarried"

End Sub

After we implemented that code, our result was like the following screenshot.

Now, we are going to edit that. In this case, we will fill the blank cells with the word Single.

Firstly, press the Alt+F8 on your keyboard to open the Macro dialog box.
Here, select FindNext_empty_value.
Then, click on Edit.

After that, the VBA Editor will open.
Now, we are going to bring a simple change here. We removed the Unmarried word from the code.

Now, from Debug tab, select Compile VBAProject.

Afterward, it will show an error as we didn’t give any value.

Now, change it to Single like the following screenshot.

Again, from Debug menu bar, select Compile VBAProject. This time there will be no error.
Now, press Ctrl + S on your keyboard to save it and then close the VBA Editor.
Next, press again the Alt+F8 on your keyboard and select FindNext_empty_value.
Then, click on Run.

Following this, you will get the Search Value box.
Here, leave the field empty as we are searching for blank cells and click on OK.

Now, select the range of cells from the dataset in the Search Range dialogue box.

Lastly, click on OK.
As a result, we have successfully edited our code and this is working fine.

Things to Remember

If you do not get the macro-enabled Excel file from a trusted source, do not enable the macro content. There might be malicious codes.
Make a copy of the VBA Macro so that you can easily find the the codes in future whenever you need that.

Download Practice Workbook

Download this sample workbook to practice.

Edit Macros.xlsm

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge on how to edit macros in Excel with 2 ideal methods. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Get FREE Advanced Excel Exercises with Solutions!