Excel VBA Programming For Dummies. Dick KusleikaЧитать онлайн книгу.

the modules and keeps them neatly in folders that tell you what kind of module it is. There are four kinds of modules, each kind in its own folder:

Microsoft Excel Objects: These are modules that are linked to user interface elements like workbooks and worksheets. See Chapter 11 for more about these types of modules.

Modules: Excel calls these simply modules, but everyone else refers to them as standard modules or VBA modules. These contain the code that doesn’t go in any of the other three module types.

Forms: These are modules that have a form user-interface. Chapter 16 provides an introduction to UserForms.

Class Modules: These are modules where you create your own objects. Class modules are beyond the scope of this book.

You can expand a project by clicking the plus sign (+) at the left of the project’s name in the Project Explorer. Collapse a project by clicking the minus sign (–) to the left of a project’s name. Or you can double-click a project’s name to expand and collapse it.

If a project is password-protected, the VBE prompts for its password when you double-click the project’s name. If you don’t know the password, you can’t expand the project — which means that you can’t view or modify any part of the project.

Figure 3-2 shows a Project Explorer with four projects listed: an add-in named UIHelpers.xlam, a workbook named Book1, a workbook named NumbersToWords.xlsm, and the Personal Macro Workbook (which is always named PERSONAL.XLSB). Of the four, only the NumbersToWords.xlsm project is expanded to show all of its modules.

Snapshot of the Project Explorer lists projects that can be expanded to show modules.

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:

1 In the VBE, select the project’s name in the Project window.

2 Choose Insert ⇒ Module.

1 Right-click the project’s name.

2 Choose Insert ⇒ Module from the shortcut menu.

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

1 Choose File ⇒ Import File or press Ctrl+M.A dialog box appears that asks for a file.

2 Locate the file, and click Open.

You should import modules only if you know the person who exported them. Otherwise, you risk introducing macros that perform malicious actions. If you’re not sure, open it in a text editor to review it first.

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.

Snapshot of code pane overload isn’t a pretty sight.

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

Скачать книгу