Navigating the VBA Editor | Getting Acquainted with Visual Basic for Applications

Problem

You want to start exploring the Visual Basic for Applications (VBA) language and don't know where to begin.

Solution

Open Excel and select Tools images/U2192.jpg border=0> Macro images/U2192.jpg border=0> Visual Basic Editor from the main menu bar to open the VBA integrated development environment (IDE ). Start exploring the IDE as described in the following discussion.

Discussion

The VBA IDE is basically the VBA editor. It's where to write VBA code, manage code modules , and debug your code. The editor allows you to perform all these development tasks in a single, integrated application. You can access the IDE using the menu as mentioned earlier or you can use the shortcut Alt-F11. You can also use the Visual Basic toolbar, shown in Figure 2-1.

Figure 2-1. Visual Basic toolbar

The IDE looks very much like any other Windows application, with a menu at the top, toolbars, and child windows in the client area of the window, as shown in Figure 2-2.

The VBA IDE is a multiple-document interface style of window. The area below the toolbars and menu is called the client area, which acts as a container for all the other (child) windows to be displayed within the IDE.

Within the client area of the VBA IDE shown in Figure 2-2, there are three panels or windows. The panel in the upper left is called the project panel, or project explorer window. It shows an Explorer-like view of all the currently open VBA projects.

Each Excel workbook has a corresponding VBA project with a default name of the form VBAProject (spreadsheet name). You can change this name by editing the Name property in the properties panel (more on that panel in a moment). Each project consists of multiple objects and code modules. Every sheet in a workbook has an associated object shown in the project window, as does the workbook itself. I'll come back to these Excel objects in Recipe 2.13.

For the most part, we'll add our own code modules to a project and put our custom code in these modules. This has the advantage of giving our custom code global scope so that our custom functions will be accessible by any sheet in the workbook.

Figure 2-2. VBA IDE

To add a module, select Insert Module from the main menu bar (make sure your project is selected first in the project panel). Upon doing so, youll see a module object appear under your selected project in the project panel. Double-click the module in the project panel to open its code window if it isn't already opened. Figure 2-2 shows an example project with an open code window containing some VBA code.

When you select any object or module in the project panel, its properties are displayed in the properties panel.

In object-oriented programming lingo, an object is a construct that contains properties and methods . Properties define certain attributes of the object, while methods define the object's functionality. For example, let's say we write a code object to represent your car. We'd give it properties such as make, model, color, engine type, and seat covering type. These properties describe what the object looks like (i.e., its attributes, or characteristics). Further, we could give it methods such as move forward, reverse, and stop. These are actions (i.e., things the car can do when we invoke these methods). A more realistic programming example would be an object that represents, say, a text file. The properties could be things like size and number of words, while methods would be things like save and spellcheck.

For the example module shown in Figure 2-2, the only property available is its name. You can change the module's name by editing the Name field in the properties panel.

Unless I'm doing work with Excel objects themselves or writing my own custom objects, I usually close the properties panel to clean up the editor a bit. Press F4 or select View Properties Window to show it when you need it. (Well come back to Excel objects in Recipe 2.13.)

Throughout this book, we'll spend most of our time in the VBA editor working with module code windows. Take another look at the code window shown in Figure 2-2. At the top of the window you should see two drop-down listboxes. The listbox on the left is the object list and the one on the right is the procedure list. The object list shows a list of objects in the selected module, while the procedure list shows a list of procedures associated with the selected object.

Procedures in VBA context refer to functions and subroutines. We can write both functions and subroutines to perform actions and calculations for us, but there's a distinct difference between the two as discussed in Recipe 2.2.

For Excel objects, the object list may contain several entries; however, for the code modules that we'll work with the most, the object list will show only General, while the procedure list will show a list of all our custom procedures. You can jump to any procedure in the code window by selecting it from the procedure list. This is a handy way to get around your code window when it fills up with a lot of code.

You may have also noticed two little icons in the lower-left corner of the code window shown in Figure 2-2. (These icons consist of a few horizontal lines.) The icon on the left with fewer lines puts the code window in Procedure View when clicked, while the one on the right puts the code window in Full Module View. Procedure View shows only one procedure at a time in the code window, which is convenient if you find your code window too cluttered. In Procedure View, you have to use the procedure list to navigate from procedure to procedure. On the other hand, Full Module View shows the entire contents of the module. I usually leave the window in full view and navigate using both normal window scrolling and the procedure list. I have no particular reason for doing so other than that's how all my other development tools work and it's how I'm used to working with code.

You write VBA statements (instructions) in code windows, as illustrated in Figure 2-2. As you can see, the code illustrated in Figure 2-2 is indented in blocks. This is not a VBA requirement, so you're free to space and indent your code as you desire. However, it's considered good practice to indent logical blocks of code, making the code more readable. You'll see this practice throughout all the code presented in this book.

I'll talk more about the VBA language and statements throughout the rest of this chapter, and the remainder of this book for that matter, so I won't get into code details in this recipe. I do want to mention that when you're typing code in a code window, VBA will automatically complete some code for you. For example, if you type a function declaration, VBA will automatically complete an End Function statement for you. VBA has other code completion features that you'll encounter as you use it. These are designed to save you time and help reduce errors.

I should also mention that it's a good idea to save your work often while working in the code window. Simply press the Save button on the VBA IDE toolbar, select File images/U2192.jpg border=0> Save..., or press Ctrl-S. Saving in either the VBA IDE or Excel saves both your VBA project and its associated workbook.

To return to Excel from the VBA IDE, press the Excel icon in the VBA IDE toolbar. Or you can switch to Excel via the normal application switching methods in Windows; e.g., press the Excel button on the Windows Start bar at the bottom of the screen, click directly on the Excel window if it's visible, or use Alt-Tab.