Excel VBA Programming For Dummies. Dick Kusleika

Чтение книги онлайн.

      FIGURE 3-2: This Project Explorer lists projects that can be expanded to show modules.

      Every project expands to show at least one folder called Microsoft Excel Objects. This folder 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 the module lives in). If the project has any VBA modules, the project listing also shows a Modules folder. And as you’ll find out in Part 4, a project may also contain a folder called Forms, which contains UserForm objects (that hold custom dialog boxes).

      The concept of objects may be a bit fuzzy for you, but don’t worry. Things become much clearer in subsequent chapters. Don’t be too concerned if you don’t understand what’s going on at this point.

      Adding a new VBA module

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

Or

Working with a Code Pane

      A code pane is where you write and edit the code that lives in the module. As you become proficient with VBA, you spend an increasing amount of time working in code panes. To open a code pane and view what’s in a module, double-click the module’s name in the Project Explorer.

      Minimizing and maximizing windows

If you have several projects open, the VBE may have lots of code panes showing at any given time. Figure 3-3 shows an example of the VBE overloaded with code panes.

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

      For some tasks, you might want to have two or more code panes visible. For example, you might want to compare the code in two modules, or copy code from one module to another. You can arrange the panes manually, or choose Window ⇒ Tile Horizontally or Window ⇒ Tile Vertically to arrange them automatically.

      FIGURE 3-3: Code pane overload isn’t a pretty sight.

You can quickly switch among code panes by pressing Ctrl+Tab. If you repeat that key combination, you keep cycling through all the open code panes. Pressing Ctrl+Shift+Tab cycles through the panes in reverse order.

      

Minimizing a code pane gets it out of the way. You can also click the window’s Close button (which displays X) on a code pane’s title bar to close its window. (Closing a window just hides it; you won’t lose anything.) To reopen the code pane, double-click the appropriate module in the Project Explorer. By the way, working with these code panes sounds more difficult than it really is.

      Looking at the parts of a module

      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. Declarations are basically housekeeping statements. They aren’t actually executed.

       Sub procedures: A set of programming instructions that, when executed, performs some action.

       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. Well, there is a limit — about 64,000 characters per module. It’s unlikely you’ll even get close to reaching the 64,000-character limit. But if you did, the solution is simple: Just insert a new module.

How you organize a VBA module is completely up to you. Some people prefer to keep all their VBA code for an application in a single VBA module; others like to split the code into several modules. The best practice is to store related procedures in their own modules. For example, you might keep all the procedures that deal with importing and exporting data in a module called ImportExport, all the procedures that deal with formatting cells in a module called Formatting, and all the procedures that deal with printing in a module called Printing. Organizing your code in this manner makes it easier for you and others to understand and, if necessary, modify the code.

      Getting VBA code into a module

      An empty VBA module is like the fake food you see in the windows of some restaurants; it looks good, but it doesn’t really do much for you. Before you can do anything meaningful, you must have some VBA code in the VBA module. You can get VBA code into a VBA module in three ways:

       Enter the code directly.

       Use the Excel macro recorder to record your actions and convert those actions to VBA code (see Chapter 6).

       Copy the code from one module and paste it into another.

      Entering code directly

      Sometimes, the best route is the most direct one. Entering code directly involves … well, typing the code using your keyboard. Entering and editing text in a VBA module works as you might expect. You can select, copy, cut, paste, and drag-and-drop.

      СКАЧАТЬ