Excel 2019 Power Programming with VBA. Michael Alexander
Чтение книги онлайн.
Читать онлайн книгу Excel 2019 Power Programming with VBA - Michael Alexander страница 27
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 maximize button in its title bar (right next to the X). Or, just double-click its title bar to maximize it. To restore a Code window to its original size, click the Restore button. When a window is maximized, its title bar isn't really visible, so you'll find the Restore button to the right of the Type a Question for Help box.
Sometimes, you may want to have two or more Code windows visible. For example, you may want to compare the code in two modules or copy code