Excel 2019 Power Programming with VBA. Michael Alexander

VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut menu of common commands.

      Toolbar

      The Standard toolbar, which is directly under the menu bar by default, is one of four VBE toolbars available. You can customize the toolbars, move them around, display other toolbars, and so on. If you're so inclined, use the View ➪ Toolbars command to work with VBE toolbars. Most people just leave them as they are.

      Project window

      The Project window displays a tree diagram that shows every workbook currently open in Excel (including add-ins and hidden workbooks). Double-click items to expand or contract them. You'll explore this window in more detail in the “Working with the Project Window” section later in this chapter.

      If the Project window is not visible, press Ctrl+R or use the View ➪ Project Explorer command. To hide the Project window, click the Close button in its title bar. Alternatively, right-click anywhere in the Project window and select Hide from the shortcut menu.

      Code window

      A Code window contains VBA code. Every object in a project has an associated Code window. To view an object's Code window, double-click the object in the Project window. For example, to view the Code window for the Sheet1 object, double-click Sheet1 in the Project window. Unless you've added some VBA code, the Code window will be empty.

You find out more about Code windows later in this chapter's “Working with a Code Window” section.

      Immediate window

      The Immediate window may or may not be visible. If it isn't visible, press Ctrl+G or use the View ➪ Immediate Window command. To close the Immediate window, click the Close button in its title bar (or right-click anywhere in the Immediate window and select Hide from the shortcut menu).

      The Immediate window is most useful for executing individual VBA statements and for debugging your code. If you're just starting out with VBA, this window won't be all that useful, so feel free to hide it and free up some screen space for other things.

      Working with the Project window

      When you're working in the VBE, each Excel workbook and add-in that's open 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.

Figure 2.11 shows a Project window with two projects listed: a workbook named Book1 and a workbook named Book2.

FIGURE 2.11 This Project window lists two projects. They are expanded to show their objects.

      Every project expands to show at least one node called Microsoft Excel Objects. This node expands to show 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 shows 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 will be 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 completely up to you. Some people prefer to keep all of their VBA code for an application in a single VBA module; others like to split up the code into several different modules. It's a personal choice, just like arranging furniture.

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

      1 Select the project's name in the Project window.

      2 Choose Insert ➪ Module.

      Or you can do the following:

      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 (see Figure 2.12). Any module that you create in a given workbook is placed in this Modules folder.

FIGURE 2.12 Code modules are visible in the Project window in a folder called Modules.

      Removing a VBA module

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

      1 Select the module's name in the Project window.

      2 Choose File ➪ Remove xxx, where xxx is the module name. Note that Excel will ask if you want to export the module before removing it. You can click Yes if you want to save the module for backup purposes or for importing into another workbook.

Or you can do the following:

      1 Right-click the module's name in the Project window.

      2 Choose Remove xxx from the shortcut menu.

      NOTE

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

      Working with a Code window

      As you become proficient with VBA, you spend lots of time working in Code windows. Macros that you record are stored in a module, and you can type VBA code directly into a VBA module.

      Minimizing and maximizing windows

      Code windows are much like workbook windows in Excel. You can minimize them, maximize them, resize them, hide them, rearrange them, and so on. Most people find it much easier to maximize the Code window on which they're working. Doing so lets you see more code and keeps you from getting distracted.

      To maximize a Code window, click the