Editing the Macro in the Visual Basic Editor

Follow the steps below to open a macro for editing in the Visual Basic Editor:

1. On the View tab, click the Macros command button then the View Macros option.
   This opens the Macro dialog box showing the names of the macros that you've created in the workbook, and in your Personal Macro Workbook.
   
2. In the Macro Name list box, select the macro name that you want to edit and then click the Edit button.

Excel will display the macro in the Visual Basic Editor.

The Code window shows the code instructions for the macro. This is where you would edit the macro. The Project Explorer enables you to navigate to macros saved in different modules or in different workbooks that are currently open.

In the Code window , the macro code is between a starting keyword and an ending keyword. The beginning of the macro has the keyword Sub MyMacro1 (), w here MyMacro1 is the name you gave to your macro when you created it. The end of the macro is denoted by the keyword End Sub . To make changes to the macro, ensure you keep your edits within this area.

The code below was generated from the macro we created earlier in this chapter.

Sub MyMacro1()

'

' MyMacro1 Macro

'

ActiveCell.Select

ActiveCell.FormulaR1C1 = "Microsoft Excel 2019"

With Selection.Font

.Name = "Calibri"

.Size = 14

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontMinor

End With

Selection.Font.Bold = True

Selection.Columns.AutoFit

End Sub

After making your changes, save the changes by clicking the Save button on the Visual Basic Editor toolbar (the blue disk icon).

To close the Visual Basic Editor, simply click on the Close button on the top right of the window (x icon). You can also close the window by clicking on File > Close and Return to Microsoft Excel .

Tip : Another way to open the Visual Basic Editor is from the Developer tab on the Ribbon. On the Developer tab, in the Code group, click the Visual Basic command button. The Developer tab is not one of the default tabs on the Excel Ribbon so if you don't have this tab, follow the steps detailed in a previous section of this chapter for how to add the Developer tab to the Excel Ribbon.