I like to think of an editor as a tool that enhances your work and offers guidance. This holds whether the editor is a real person or a built-in tool like the Excel macro editor. One benefit of Microsoft’s editor is that it is a teaching tool. In this tutorial, I’ll show how to edit a macro in Excel and other editor features.
Why Use the Excel Macro Editor?
Apart from offering guidance, the editor also allows you to:
- add comments to your macro
- modify macros
- combine modules
- move or copy macros
Where are Excel Macros Located?
People are usually elated after learning how to record and run an Excel macro for the first time. But then the question comes up as to where the macros are stored. They are neatly hidden behind a toolbar button.
- Click the View tab.
- To the far right, click the small triangle
below the Macros button label.
- From the button menu, select View Macros.
- A Macro dialog box appears with 4 areas.
[A] A listing of your macros.
[B] A drop-down menu showing the location of macros.
[C] The description of the highlighted macro if provided.
[D] Actions you can take on the highlighted macro.
I have one macro in the screenshot above (Fig. 2 – [A]). It’s the one I created from the previous tutorial. The first part of the listing shows that this macro is attached to PERSONAL.XSLB or my “personal workbook.” You can use macros in your personal workbook in any of your Excel spreadsheets.
In contrast, if I created a macro called Set_Address_Columns that was attached to a specific spreadsheet, it would only show when I was using that macro-enabled spreadsheet. These macros do not have a location identifier in front.
If you don’t see your macro, try changing your location as shown in Fig. 2 [B].
How to View Macros in Excel
To view the macro contents, you need to click the Edit button even if you don’t plan to make any changes. The macro editor can be a bit intimidating at first until you get the basics.
You’ll see several panels based on your settings when you open the editor. The one we’ll start with is on the right. It shows the VBA code for the macro. VBA stands for Visual Basic Application. Excel’s Macro Recorder automatically generated the code you see here. I didn’t enter any of the macro code you see on the right.
If you look at the right side, you may notice some patterns.
[A] Each macro starts with a Sub reference followed by the macro name we assigned. This stands for a Subroutine or subprocedure. You’ll also note the macro ends with a End Sub command. Both these items display in blue.
[B] Next, we have a series of lines in green, starting with an apostrophe. These are comments and are used to explain the macro. You could delete them, and the macro would still run. They don’t impact the macro, so it wouldn’t work if you changed the Keyboard Shortcut to something else.
You can add your own comments by starting a line with an apostrophe ‘. This can be useful when making changes to a macro or documenting it for others.
[C] The lines are in black are the macro’s code and include references to actions, properties, and objects. This section is great for teaching as you can see how the steps you performed with the macro recorder got translated. You may recall from my earlier tutorial; I was deleting specific columns I no longer needed, such as A, D, J, K, L, and N.
[D] This is the Explorer. This panel can also be a bit intimidating. The key things to remember are that your macro code shows in the items marked as Module. You’ll also note a slight shading on Module1 under my (PERSONAL.XLSB) section. This shading indicates that is the code I’m viewing on the right.
You can double-click other Modules to see their code.
If you don’t see the Project panel, you can reveal it from the View and select Project Explorer.
How to Change Macro Shortcut Key or Description
Some of the earliest changes people like to do are to change either the shortcut key or macro description. Even though there is a reference to the shortcut in Figure 4, it’s a comment. That means if you changed the reference here, it wouldn’t work. Comments do not affect your code.
If you go back to the initial Macro dialog, you’ll see a button on the bottom labeled Options…. Clicking that button will open the Macro Options dialog. You can change the Shortcut Key [A] or Description [B] from here.
Use the Editor as a Learning Tool
One of the features I appreciate about the editor is how I can learn more about how macros are constructed and VBA. Using the macro recorder, you can record simple macros and see how the code displays in the editor.
A simple macro you might try is to set up is one that puts in column headings. Then see how the editor recorded these entries. Then, for simple changes, you can edit the code directly.
The code editor also includes Microsoft’s IntelliSense, which offers code completion, suggestions, and formatting when you type code. For example, if I type a period . followed by my first letter s, IntelliSense will filter the list to show valid entries starting with that letter. It also uses the desired camelCase formatting.
Next Steps
Once you get acquainted with the macro editor, you can try adding more complex code. This article is meant to be a high-level look at the editor. However, if you want to learn more about macros and VBA, I suggest the free 3-part video series from Excel Campus.