I like to think of editors as a tool that enhances your work and offers guidance. This holds true whether the editor is a real person or a built-in tool like the Excel macro editor. One benefit of Excel’s editor is that it’s also a teaching tool. Once you start playing around and creating macros, you’ll get a better understanding of Visual Basic for Applications (VBA). That’s the underlying language for macros.
Why Use the Excel Macro Editor?
Apart from offering guidance, the editor also allows you to:
- provide additional comments
- modify macros
- combine modules
- move or copy macros
Where are My Macros?
People are usually elated after recording and running their first macro. 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 location of macros
[C] The description of the highlighted macro if provided.
[D] Actions you can take on the highlighted macro
In the screenshot above, I have one macro. 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”. Macros in your personal workbook can be used 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].
Viewing the Macro Contents
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.
When you open the editor, you’ll see several panels based on your settings. The one we’ll start with is on the right. It shows the VBA code for the macro. VBA stands for Visual Basic Application. The code you’re seeing here was automatically generated by Excel’s Macro Recorder. I didn’t enter any of the 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 an
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 if you changed the Keyboard Shortcut to something else, it wouldn’t work.
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 includes references to actions, properties, and objects. This is the section that 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 that there is a slight shading on the 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 selecting Project Explorer.
How to Change 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 have no effect on 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. From here you can change the Shortcut Key [A] or Description [B]
Use the Editor as a Learning Tool
One of the features I appreciate about the editor is how I can use it to 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 a macro that puts in column headings. Then see how the editor recorded these entries. 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 s, IntelliSense will filter the list to show valid entries starting with that letter. It also uses the desired camelCase formating.
Once you get more acquainted with how things work, you can try adding more complex code. This article is meant to be a high-level look at the editor. However, if you wish to learn more about macros and VBA, I’d suggest the free 3-part video series from Excel Campus.