You want to start exploring the Visual Basic for Applications (VBA) language and don't know where to begin.
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.
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.
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.
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.
This recipe is merely an overview of the VBA IDE. Granted, it isn't that complicated an application, but there are other aspects to its interface that I have not addressed. The VBA help guide contains a complete reference entitled "Visual Basic User Interface Help " that you may consult for more details on the interface. I can't say it's the most captivating reading, but it's there if you need it. Press F1 in the VBA IDE to open the VBA help document and then press the Contents tab. The first topic is the user interface reference. As I mentioned in Chapter 1, I think the best way to learn the VBA IDE is to explore it and try things out. You'll also learn more as you go through other recipes in this book.
Getting Acquainted with Visual Basic for Applications
Collecting and Cleaning Up Data
Time Series Analysis
Curve Fitting and Regression
Numerical Integration and Differentiation
Solving Ordinary Differential Equations
Solving Partial Differential Equations
Performing Optimization Analyses in Excel
Introduction to Financial Calculations