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.

unhide option to unhide macros in excel

After that, you will see an Unhide dialog box.

unhide macros to edit in excel

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)


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.

macros dialog box in excel

Now, select any macro from the Macro name list.

select any of these macros to edit

Click on Edit.

click on edit in macros

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

VBA editor to edit macros in excel

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

VBA editor to edit macros in excel

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

double click on any module

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

Read More: 22 Macro Examples in Excel VBA

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.

macros button in view tab in excel

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

macros dialog box in excel

Choose any Macro from the Macro name list.

select any of these macros to edit

Click on Edit.

click on edit in macros

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

VBA editor to edit macros in excel

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


Similar Readings


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:

dataset to edit macros in excel

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:

output after implementing the macro

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.

macros dialog box in excel

 Select FindNext_empty_value

select a macros to edit

Then, click on Edit.

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.

remove the unmarried word

Now, from Debug menu bar, select Compile VBAProject.

select compile VBAProject from debug menu bar

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

error in the VBA code

Now, change it to “Single” like the following screenshot.

change the value to single

  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

select a macros to edit

  Then, click on Run.

run the VBA code after editing

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

leave the field empty in search box

  Select the range of cells of your data.

select the range of cells to imply VBA macro

  Then, click on OK.

Edit macros in excel result

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


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

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo