Working with the Project Explorer

When you're working in the VBE, each Excel workbook and add-in that's currently open is considered a project. You can think of a project as a collection of objects arranged as an expandable tree. You can expand a project by clicking the plus sign (+) at the left of the project's name in the Project Explorer window. You contract a project by clicking the minus sign ( “) to the left of a project's name . If you try to expand a project that's protected with a password, you are prompted to enter the password.

Note  

The top of the Project Explorer window contains three icons. The third icon, named Toggle Folder , controls whether the objects in a project are displayed in a hierarchy or are shown in a single non-hierarchical list.

Figure 7-3 shows a Project Explorer window with three projects listed (one add-in and two workbooks).

Figure 7-3: A Project Explorer window with three projects listed.

Caution  

When you activate the VBE, you cannot assume that the code module that's displayed corresponds to the highlighted object in the Project Explorer window. To make sure that you're working in the correct code module, always double-click the object in the Project Explorer window.

If you have many workbooks and add-ins loaded, the Project Explorer window can be a bit overwhelming. Unfortunately, you can't hide projects in the Project Explorer window. However, you probably want to keep the project outlines contracted if you're not working on them.

When viewing the Project Explorer in folder view, every project expands to show at least one node called Microsoft Excel Objects. This node expands to show an item for each worksheet and chart sheet in the workbook (each sheet is considered an object) and another object called ThisWorkbook (which represents the Workbook object). If the project has any VBA modules, the project listing also shows a Modules node, and the modules are listed there. A project can also contain a node called Forms that contains UserForm objects (also known as custom dialog boxes). If your project has any class modules, it displays another node called Class Modules. Similarly, if your project has any references, you see another node called References. The References node is a bit misleading because references can't contain any VBA code.

Adding a new VBA module

To add a new VBA module to a project, select the project's name in the Project Explorer window and choose Insert Module. Or you can just right-click the project's name and choose Insert Module from the shortcut menu.

When you record a macro, Excel automatically inserts a VBA module to hold the recorded code.

Removing a VBA module

If you need to remove a VBA module or a class module from a project, select the module's name in the Project Explorer window and choose File Remove xxx (where xxx is the name of the module). Or you can right-click the module's name and choose Remove xxx from the shortcut menu. You are asked whether you want to export the module before removing it. See the next section for details. You cannot remove code modules associated with the workbook (the ThisWorkbook code module) or with a sheet (for example, the Sheet1 code module).

Exporting and importing objects

Except for those listed under the References node, every object in a project can be saved to a separate file. Saving an individual object in a project is called exporting. And it stands to reason that you can also import objects into a project. Exporting and importing objects might be useful if you want to use a particular object (such as a VBA module or a UserForm) in a different project.

To export an object, select it in the Project Explorer window and choose File Export File (or press Ctrl+E). You get a dialog box that asks for a filename. Note that the object remains in the project (only a copy of it is exported). If you export a UserForm object, any code associated with the UserForm is also exported.

To import a file into a project, select the project's name in the Project Explorer window and choose File Import File. You get a dialog box that asks for a file. You can import only a file that has been exported by choosing the File Export File command.

Tip  

If you would like to copy a module or UserForm object to another project, it's not really necessary to export and then import the object. Make sure that both projects are open; then simply activate the Project Explorer and drag the object from one project to the other.