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.

Read More: [Solve:] Cannot Edit a Macro on a Hidden Workbook (2 Easy Solutions)

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.

Read More: How to Edit Name Box in Excel (Edit, Change Range and Delete)

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: 22 Macro Examples in Excel VBA

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.

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

Similar Readings

• VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)
• How to Edit Cell in Excel with Keyboard (4 Handy Methods)
• 7 Solutions for Greyed Out Edit Links or Change Source Option in Excel
• How to Edit a Cell in Excel without Double Clicking (3 Easy Ways)

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.

Read More: How to Edit Named Range in Excel

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.

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. Don’t forget to check our website ExcelDemy for various Excel-related problems and solutions.

Related Articles

• About Macro Security in Excel
• How to Edit a Macro Button in Excel (5 Easy Methods)
• A Macro example created using VBA
• How to Edit Drop-Down List in Excel (4 Basic Approaches)
• Enable Editing in Excel Protected View (5 Methods)
• Necessary Aspects About Macro Security in Excel
• How to Edit Defined Names in Excel (Step-by-Step Guideline)