How to Edit Macros in Excel (2 Ideal Methods)

Get FREE Advanced Excel Exercises with Solutions!

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 Edit Macros in Excel


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.

Selecting Unhide Command from View Tab

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

Choosing Workbook Name in Unhide Box

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

Opening Macro Dialog Box

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

Selecting Macro Name

  • Then, click on Edit.

Choosing Edit Option in Macro Window

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

Editing Code in 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.

Opening VBA Editor Window

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

Choosing Module to Edit Macro in Excel

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

Selecting Macros from View Tab

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

Selecting Macros from Developer Tab

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

Opening Macro Dialog Box to Edit Macos in Excel

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

How to Edit Macros in Excel

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

Editing Macros in VBA Editor

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


Similar Readings


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.

Dataset to Run Macros

  • 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

Initial VBA Macro Code

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

Output after Running Code

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.

Selecting Macro Name with Edit Option

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

Edit Macros in VBA Editor

  • Now, from Debug tab, select Compile VBAProject.

Debugging Code

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

Debugging Code

  • Now, change it to Single like the following screenshot.

Editing Macros

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

Running Selected Macro

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

Keeping Search Value Blank

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

Providing Cell Range in Search Range Dialog Box

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

Final Output after Editing Macros in Excel

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

A.N.M. Mohaimen Shanto
A.N.M. Mohaimen 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo