The Visual Basic Editor

To create VBA code, or to examine existing code, you will need to use the Visual Basic Editor. To access the Visual Basic Editor, choose Macro from the Tools menu and then Visual Basic Editor from the submenu.

The Visual Basic Editor screen usually contains three important windows: the Project Explorer window, the Properties window and the Code window, as shown in Figure 1-1. (What you see may not look exactly like this.)

The Code window displays the active module sheet; each module sheet can contain one or several VBA procedures. If the workbook you are using does not


H M' J

at- ertW Èan lools feW-IrK Ktndow be*! T^imuWmFürlUlp -.SX

- » y & jf 'W 1

Ip |ji VBAPioject (Bookl)

ffl] Sheet! C9M 1 ) ! j I-® 5heet2 (5tioet2) «G 5heet3 (9*st3) M 0 ThiiWarkbcok y <- \ MUMS


[indijfl MtxMe «ptiatieti: [ Cjts-JMM ( ffWfflMudutal

j(l-,siifr.ill ^J |ll)ccUiJtiunjj -J


i i.r

Figure 1-1. The Visual Basic Editor window.

Figure 1-1. The Visual Basic Editor window.

contain any module sheets, the Code window will be empty. To insert a module sheet, choose Module from the Insert menu. A folder icon labeled Modules will be inserted; if you click on this icon, the module sheet Modulel will bedisplayed. Excel gives these module sheets the default names Modulel, Module2 and so on.

Use the Project window to select a particular code module from all the available modules in open workbooks. These are displayed in the Project window (Figure 1-2), which is usually located on the left side of the screen. If the Project window is not visible, choose Project Explorer from the View menu, or click on the Project Explorer toolbutton igi to display it. The Project

Explorer toolbutton is the fifth button from the right in the VBA toolbar.

In the Project Explorer window you will see a hierarchy tree with a node for each open workbook. In the example illustrated in Figure 1-2, a new workbook, Bookl, has been opened. The node for Bookl has a node (a folder icon) labeled Microsoft Excel Objects; click on the folder icon to display the nodes it contains— an icon for each sheet in the workbook and an additional one labeled ThisWorkbook. If you double-click on any one of these nodes you will display the code sheet for it. These code sheets are for special types of procedures called automatic procedures or event-handler procedures, which are not covered in this mmssmmm

CHEM.xls (CHEM.xIa) Funcres (FUNCRES.XLA) VBAProject (Boolcl)

Jgg Microsoft Excel Objects ' • fflf] Sheet 1 (Sheet 1)

• ® Sheet2 (5heet2) ! ® 5heet3 (Sheet3) This Workbook Modules


Figure 1-2. The VBE Project Explorer window.

book. Do not use any of these sheets to create the VBA procedures described in this book. The hierarchy tree in Figure 1-2 also shows a Modules folder, containing one module sheet, Modulel.

The Properties window will be discussed later. Right now, you can press the Close button to get rid of it if you wish.

Properties - Sheetl

¡Sheet! Worksheet Alphabetic j Categorized |














Enables election

0 - xlNo Restrict ions



Scroll Area

Standard Width



-1 - xlSheet Visible

1 1

Figure 1-3. The Properties window.

Figure 1-3. The Properties window.

0 0

Post a comment