How to Edit Macros in Excel (2 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 suitable examples and proper illustrations.

Download Practice Workbook

Download this practice workbook.

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.

📌 Steps

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

② After that, you will see an Unhide dialog box.

③ 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.

2 Ways to Edit Macros in Excel

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. Keyboard Shortcut to Edit VBA 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 Keyboard Shortcut to Open Macros Dialog Box

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

📌 Steps

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

② Now, select any macro from the Macro name list.

③ 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 keyboard Shortcut to Open VBA Editor Directly

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 of your workbook, right? So, you can go to any module anytime and edit the macros.

📌 Steps

① Firstly, press Alt+F11.

② It will open the VBA editor. Now, choose your module. Double Click on it.

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

2. Use of the Macros Command in Excel to Edit VBA Codes

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.

📌 Steps

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

② Click on Macros. After that, the Macro dialog box will open.

③ Choose any Macro from the Macro name list.

④ Click on Edit.

⑤  Here it is, your selected macro. Now, you can start editing.

Read more: How to Assign a Macro to a Button in Excel

Test the Macros after Editing: Debug and Run

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:

Here, we have some blank cells in the dataset. Our goal was to fill those empty cells with the word “Unmarried”.

This was our 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”

📌 Steps

① Firstly, press the Alt+F8 on your keyboard. A macro dialog box will appear.

②  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.

⑤ Now, from Debug menu bar, select Compile VBAProject.

⑥ After that, 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, save the file.

⑨  Again, press the Alt+F8 on your keyboard. Select FindNext_empty_value

⑩  Then, click on Run.

⑪  In the Search Value box, leave the field empty as we are searching for blank cells.

⑫  Select the range of cells of your data.

⑬  Then, click on OK.

As you can, see 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.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to edit macros in  Excel. 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. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Related Articles

• A Macro example created using VBA 
• VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)
• About Macro Security in Excel