The Visual Basic Editor is a module that exists inside of the Microsoft Office host applications, such as Word, Excel, PowerPoint, etc. But for each application it is a separate module, so you could have it open in Word and again in Excel at the same time.
You can open the editor in one of two ways:
- Go to the Developer tab, click on Macros, select a Macro, then click Edit to open the code for that Macro. You can edit that code directly in the Edit window, or just inspect it to see how it works. Alternatively, press Alt+F8 to open the Macros dialog and select the macro you want to look at.
- Press Alt+F11 to open the editor directly. Each of the macros you have is a module, and you can pick a module to edit or inspect.
When you have done so, you will have this window in Microsoft Excel 2016 (Note that other applications may be slightly different):
This window has 3 main components:
- The Project Explorer, which is in the upper left.
- The Properties window , which is in the lower left.
- The Code window, which is on the right.
The Object Model
It is worth a short digression here to consider the Object Model which underlies Microsoft Office (or, for that matter, LibreOffice). Objects are the basis of how these programs work, and in some ways are like Russian dolls which contain other dolls, except you can contain multiple objects at each level. There are a few key characteristics to keep in mind when working with objects:
- Objects are really everything in Office. You pretty much cannot work with anything that is not an object, even if it is not obvious that you are working with objects.
- Objects can contain other objects. In Word, for instance, a Document is an object, while in Excel a Workbook is an object. A Word Document then contains objects like Titles and Paragraphs, while an Excel Workbook contains objects like sheets. And each sheet contains columns and rows, and they contain cells, and cells contain some content, and so on. It is really quite like those nested Russian dolls.
- Objects have Properties. What those Properties are in each case will depend on the object. If the object were a paragraph in Microsoft Word, the properties would include the font, the indent, the size of the letters, and so on. In Excel the contents of the cell could have many of the same properties, but if you are looking at a Chart object you would see more properties. (And a chart is several levels of objects containing other objects as well.) As a rule, any time you right-click on an object in Office you will get a pop-up menu of properties appropriate to that object. That is something you should practice as you get used to the Object model.
- Object Properties have Inheritance. The properties of any object will generally be inherited by all of the objects it contains. You can override the inherited properties in most cases, but by default the inheritance is there. Many times people get frustrated trying to understand what is happening in an Office file because they don’t know where the property is coming from.
The Project Explorer
In Office, you have VBA Projects, which are the top level of the VBA Object Model. Then you have other objects that are contained within the project, such as the Default template, and if you have a Document or Workbook open you have that as an object as well. Then for each of those you have two groups of objects. For example, using Excel, you would have Microsoft Excel Objects, and Modules. Then within Microsoft Excel Objects, you would have the Workbook, and each of the Sheets. And in the Modules section you have Module 1, Module 2, etc. If you click on one of Microsoft Excel Objects, such as a Sheet, you will get a display of the Properties associated with that object in the Properties window underneath the Project Explorer. And if you double-click on a Module you will get a display of the code in the right-hand Code window.
As you start developing more complicated Macros, the list of objects will grow, and may include things like User forms, procedures, functions, references to library files such as DLLs, etc..
If you look at the top of the Project Explorer window you will see three buttons. They are:
- View Code – This opens the Code window and displays any code attached to this particular object.
- View Object – This displays the actual object if there is an object that can be displayed, like a Form or a File. If is a module (purely code) the View Object button will be grayed-out as there is nothing to display.
- Toggle Folders – This switches between a hierarchical folders view (objects are collected in folders which can be expanded or contracted), or to a view that simply lists everything. This can be handy if you are looking for a particular object in a large Project and may not remember which folder it is in.
Note that the Object model is apparent here, in that you will see ThisWorkbook, and Sheet1, Sheet2, etc. They all have properties you can view, and if you select one and click on View Object it will just show you the Excel window in the usual view.
One thing that can get confusing in the Project Explorer is that individual Macros may only be shown as Module 1, Module 2, etc. Not very easy to work with there, but the name of any object is just another one of the Properties, so select a module in the Project explorer, then go down to the Properties window and change it. Look for the field “Name”, and next to it you will see the obscure name VBA assigned. But you can click on it and edit it to something that makes more sense. This even applies to the VBA Project itself, which you can give a usable name to.
When you select an object in the Project Explorer, the Properties for that object appear in the Properties window underneath the Project Explorer. Every Property has a name on the left, and then a value on the right, and those values are editable either as a selection field or a free-text field you can type in. If you take a look you may see properties like Font, Foreground Color, Background Color, etc. (Note: This photo displays the Properties for a Form, which is a way of obtaining user input, so these properties make sense here. Remember that the available properties are always appropriate to the object, and change as the object changes.)
The Object Browser
The Object Browser can be accessed by pressing F2 or by clicking a button on the right-hand side of the Standard toolbar. It brings up a window like this:
In this window, objects are listed on the left-hand side, and when you select an object all of its properties appear in the right-hand window. It is interesting to see just how many properties some of these objects have.
The Object Browser is displayed in the space normally used by the Code Window, which is the right-hand window of the Visual Basic Editor. If you are done using the Object Browser, just go to an object in the Project Explorer on the left, double-click it, and you will get the Code window back
The Code Window
This is where you will spend most of your time working with VBA. This is where you can inspect the code for a module, edit it, or write the code for it. This window also displays certain objects that can have code attached such as Forms. But to get the most out of this window you will want to turn on the Edit toolbar. This can be found by going to View–>Toolbars, and putting a check-mark in Edit. It will first appear as a floating toolbar, but I find it easier to work with when I dock it at the top of the screen, though you can dock it on any edge you like. It has some useful features to help you when working on code:
- List Properties/Methods
- List Constants
- Quick Info
- Parameter Info
- Complete Word
- Toggle Breakpoint
- Comment Block
- Uncomment Block
- Toggle Bookmark
- Next Bookmark
- Previous Bookmark
- Clear All Bookmarks
Many of these are pretty clear, others may require more explanation which we can get to later. But for now, the message is that you will want this toolbar open and available when you are working on code.
The Visual Basic Editor by Kevin O'Brien is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.