The Project Window in Visual Basic Editor and Excel Macros

By Michael Alexander

When you’re working in VBE, each open Excel workbook is a project. You can think of a project as a collection of objects arranged as an outline.

You can expand a project by clicking the plus sign (+) at the left of the project’s name in the project window. Contract a project by clicking the minus sign (−) to the left of a project’s name. Or you can double-click the items to expand and contract them.

This figure shows a project window with two projects: a workbook named Book1 and a workbook named Book2, expanded to display their objects.

This project window lists two projects

This project window lists two projects

Every project expands to show at least the Microsoft Excel Objects node. You can expand this node to display an item for each 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 displays a Modules node.

Adding a new VBA module

When you record a macro, Excel automatically inserts a VBA module to hold the recorded code. The workbook that holds the module for the recorded macro depends on where you chose to store the recorded macro, just before you started recording.

In general, a VBA module can hold three types of code:

  • Declarations: One or more information statements that you provide to VBA. For example, you can declare the data type for variables you plan to use or set some other module-wide options.

  • Sub procedures: A set of programming instructions that performs some action. All recorded macros are Sub procedures.

  • Function procedures: A set of programming instructions that returns a single value (similar in concept to a worksheet function, such as Sum).

A single VBA module can store any number of Sub procedures, Function procedures, and declarations. How you organize a VBA module is up to you. Some people prefer to keep all their VBA code for an application in a single VBA module; others like to split up the code into several modules. It’s a ­personal choice, like arranging furniture.

Follow these steps to manually add a new VBA module to a project:

  1. In the project window, select the project’s name.

  2. Choose Insert→Module.

Or you can

  1. Right-click the project’s name.

  2. Choose Insert→Module from the shortcut menu.

The new module is added to a Modules folder in the project window. Any modules you create in a given workbook are placed in this Modules folder.

Code modules are visible in the Modules folder in the project window.

Code modules are visible in the Modules folder in the project window.

Removing a VBA module

You may want to remove a code module that is no longer needed. To do so, follow these steps:

  1. In the project window, select the module’s name.

  2. Choose File→Remove xxx, where xxx is the module name.

Or

  1. Right-click the module’s name.

  2. Choose Remove xxx from the shortcut menu.

You can remove VBA modules, but there is no way to remove the other code modules, those for Sheet objects or ThisWorkbook.