Introducing the Visual Basic Editor


All your VBA work is done in the Visual Basic Editor (VBE). The VBE is a separate application that works seamlessly with Excel. By seamlessly, I mean that Excel takes care of the details of opening the VBE when you need it. You can't run VBE separately; Excel must be running in order for the VBE to run.

Note  

VBA modules are stored in workbook files. However, the VBA modules aren't visible unless you activate the VBE.

Displaying Excel's Developer tab

The Excel 2007 Ribbon does not display the Developer tab by default. If you're going to be working with VBA, it's essential that you turn on the Developer tab:

  1. Choose Office image from book Excel Options.

  2. In the Excel Options dialog box, click the Popular tab.

  3. Place a checkmark next to Show Developer Tab in the Ribbon.

After you perform these steps, Excel displays a new tab, as shown in Figure 7-1.

image from book
Figure 7-1: By default, the Developer tab is not displayed.

Activating the VBE

When you're working in Excel, you can switch to the VBE by using either of the following techniques:

  • Press Alt+F11.

  • Choose Developer image from book Code image from book Visual Basic.

In addition, you can access two special modules as follows . (These special VBA modules are used for event handler procedures, which I describe in Chapter 19.)

image from book
What's New in the VBE?

Excel 2007 has dozens of significant new features, including a brand-spanking-new user interface. If you're expecting new things in the VBE, you're out of luck. The Excel 2007 VBE is exactly like the Excel 2003 VBE.

image from book
 
  • Right-click a sheet tab and choose View Code (this takes you to the code module for the sheet).

  • Right-click a workbook's title bar and choose View Code (this takes you to the code module for the workbook). If the workbook window is maximized in Excel, the title bar is not visible.

Figure 7-2 shows the VBE. Chances are that your VBE window won't look exactly like the window shown in the figure. This window is highly customizable - you can hide windows , change their sizes, dock them, rearrange them, and so on.

image from book
Figure 7-2: The Visual Basic Editor window.

The VBE windows

The VBE has a number of parts . I briefly describe some of the key components in the sections that follow.

VBE MENU BAR

The VBE menu bar works like every other menu bar that you've encountered . It contains commands that you use to work with the various components in the VBE. Also, you'll find that many of the menu commands have shortcut keys associated with them. For example, the View image from book Immediate Window command has a shortcut key of Ctrl+G.

Tip  

The VBE also features shortcut menus . As you'll discover, you can right-click virtually anything in a VBE window to get a shortcut menu of common commands.

VBE TOOLBARS

The Standard toolbar, which is directly under the menu bar by default, is one of six VBE toolbars available (the menu bar is also considered a toolbar). You can customize toolbars, move them around, display other toolbars, and so forth. Choose View image from book Toolbars image from book Customize to work with VBE toolbars.

PROJECT EXPLORER WINDOW

The Project Explorer window displays a tree diagram that consists of every workbook that is currently open in Excel (including add-ins and hidden workbooks). Each workbook is known as a project. I discuss the Project Explorer window in more detail in the next section ("Working with the Project Explorer").

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

CODE WINDOW

A Code window (sometimes known as a Module window) contains VBA code. Every item in a project's tree has an associated code window. To view a code window for an object, double-click the object in the Project Explorer window. For example, to view the code window for the Sheet1 object, double-click Sheet1 in the Project Explorer window. Unless you've added some VBA code, the Code window is empty.

Another way to view the Code window for an object is to select the object in the Project Explorer window and then click the View Code button in the toolbar at the top of the Project Explorer window.

I discuss Code windows later on in this chapter (see "Working with Code Windows").

IMMEDIATE WINDOW

The Immediate window is most useful for executing VBA statements directly, testing statements, and debugging your code. This window might or might not be visible. If the Immediate window isn't visible, press Ctrl+G. 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).




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net