Dick Kusleika - Excel VBA Programming For Dummies

You find out more about Code panes later in this chapter’s “ Working with a Code Pane” section.

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

Beginning with Excel 2013, all VBA help information is on the internet and is displayed in your web browser. In other words, you must be connected to the internet to access the Help system. You can, however, download your very own copy of the Help system from Microsoft’s site. Do a web search for download excel VBA documentation , and you’ll find it. Pick the link at microsoft.com and don’t worry if the version they have is a few versions older than yours — they don’t update it all that often.

The Immediate window is most useful for executing VBA statements directly 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.

Chapter 13covers the Immediate window in detail. It may just become your good friend!

In the VBE, workbooks are called projects . Every workbook contains exactly one project, and every project is associated with exactly one workbook. A project holds all 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 11for 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 16provides 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.

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.

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.

Or

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.

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.

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.