2

Programming in the VBE

At least as far back as Borland's early Turbo Pascal editor for DOS, modern programmers have had the luxury of Integrated Development Environments (IDEs). An IDE is an editor like Notepad or MS-Word that is customized specifically for writing programming language code. The Visual Basic Editor (VBE) is an IDE that ships with every application supporting VBA, including MS-Excel. Thus, if you know how to use one VBE and VBA then you know how to use them all. All that remains is to learn the object model for each Office application you want to write code for, fortunately the Help documentation will answer many of your questions here.

This is a programming book, so it will be helpful for you to become familiar with the VBE. If you are an experienced VBA programmer and have used the VBE before, then just browse the sections of this chapter to see if there are any new techniques that you weren't previously aware of, or you can skip to Chapter 4. If you are a new VBA programmer, then reading this chapter will help you optimize your code writing and debugging experience.

Writing Code

Computers don't care about words or grammar. A computer only cares about bits and bytes. Even low-level languages that have a very simple grammar, like assembler, aren't treated any differently than the more high-level languages. Only the compiler and people care about programming languages and grammar. Since the compiler's job is to convert the programming language into bits and bytes so that the code may be executed, the grammar must be correct for the compiler to do its job. And, the easier code is for a person to read, the easier it will be to maintain. Consequently, we must master the grammar for the compiler's benefit, and we must adapt a readable style for the human reader's benefit. All of this is best accomplished in the editor.

Programming for People

The compiler is an exacting taskmaster; the code will have to be grammatically correct to run. There is little we can do about writing code for its benefit. However, we can write for the human reader by thinking about a few simple concepts and responding accordingly.

People aren't especially good at reading abbreviations, so use whole words instead. In addition, nouns and verbs make up whole sentences in the English language, so pick good nouns for class names and good verbs for method names. Furthermore, the compiler wouldn't care if we were to write one whole monolithic procedure to solve a problem as long as the grammar is correct. People, on the other hand, would likely find such code indecipherable. People are not especially good at grappling with more than a few ideas at once and are exceptionally good at focusing on one thing at a time. So make your methods and properties singular and short as opposed to plural and monolithic—that is, make sure your procedures and properties do one thing and make sure that thing is what the procedure name purports it to be.

Code will be much more difficult to comprehend if you have more than a few lines of code per procedure. If you must write many lines of code in a procedure, or the code is arguably ambiguous in its purpose, then write a comment. Comments aren't always necessary. The compiler doesn't use them, so add comments if they clarify an algorithm and not when they redundantly state what it is obvious the algorithm does. Providing many brief procedures makes each larger procedure easier to debug, and permits us to reorganize existing procedures to solve new problems. Often, monolithic procedures can only be used in one context.

People also find visual cues and organization helpful. That's why you should use indentation for subordinate ideas and remain consistent about the amount of whitespace used. The code is the programmer's work-product and should be organized.

Be explicit and precise to the greatest extent possible. Use Option Explicit at the top of each module and declare all variables explicitly with the most appropriate data type. If a value is immutable then use constants. Constants can't change, so if they are initialized with an appropriate value then that value will always be the correct one and as a result always reliable.

Finally, test code in small doses. Each procedure or property should be as independent as possible. We can accomplish this by limiting dependency on arguments outside the scope of the procedure itself. When you code a procedure (including property procedures), test each procedure to ensure that the inputs and outputs yield the desired result. The Immediate window is a good way to quickly test a new procedure. (Refer to Chapters 7 and 8 for techniques on writing bulletproof code, testing, and debugging.)

If you follow the preceding guidelines your code will be orderly, coherent, and as a result, easier to comprehend. Consequently, when you do run into trouble other programmers will be more inclined and able to provide assistance.

Writing Code

Reading and writing a lot of code is the best way to get good at it. You can import existing code, which we will discuss in later in this chapter (see “Importing and Exporting Visual Basic Code”), but most, if not all, of your programming will be done in the VBE. Let's start there.

The VBE is a separate program. It is associated with the particular Office tool you are using, and you can start up the VBE in Excel (and other MS-Office applications) by pressing Alt+F11 or by selecting Tools Macro Visual Basic Editor. Having started the VBE, simply think of it as a special word-processing tool that is aware of the VBA grammar. The text you type will have meaning to the VBE, in that the VBE can detect special keywords, and will respond to grammatical constructs. For example, the VBE will automatically convert procedure names to Pascal-cased names.

Keep in mind that all programmers have some opinion or bias about what code should look like because coding styles is a completely subjective concept. A good rule of thumb here is that one is more likely to be successful by emulating those that are demonstrably successful or that have good reason for using one technique over another.

Where Does My Code Go?

All code is written in a module, class module, or UserForm. These are all special word-processing windows that the VBE can interact with in an effort to catch grammar errors, and through the compiler, programming errors. Think of the module as a blank sheet of paper and your keyboard as a typewriter. If you were writing a short story then you might jump right in. Most programming requires some planning though.

There are several ways to approach writing code. Experienced programmers might start with a design tool like Rational's Unified Modeling Language (UML) modeling tool. This tool promotes a methodical approach to problem solving by permitting one to figure out the solution with graphic symbols. However, it takes a considerable amount of time to master UML as it is a language in its own right. The benefit of using a modeling tool is that pictures convey more meaning and are easier to produce and change than code. A more rudimentary form of the modeling tool is a flowcharting tool, like Visio. (Visio also supports UML now.) A flowcharting tool enables users to draw pictures that describe a workflow. Again, a flowcharting tool is less desirable than a UML modeling tool and takes some time to learn. There is also the low-tech concept of simply writing your ideas on a piece of paper as plain English algorithms. If you attended computer science courses in the last 20 years, you might have been taught this technique. Ultimately, a special kind of programmer, called an architect, should know all of these tools, but they can be an unsuitable starting point for a beginner for one reason or another. Possibly the best way for a beginner to start is by writing code. However, you should write code in a directed way. Let's clarify what we mean here.

If you need to add a few basic macros then you can record them first. This approach is fast and easy. If you need to make changes, then switch to the VBE and modify the recorded macros. If you are writing an Excel-based application then you may combine recorded macros with customized procedures and UserForms. Then, mentally group each category of problem and create a separate module, class module, or UserForm for each group of problem. Finally, begin adding the fields, properties, methods, and events that solve each group of problem. This approach will work reasonably well for modest complexity problems. For very large problems however, you will likely discover that a just write it approach will ultimately be too hard to understand. If you get to this point then a technique referred to as refactoring can help unravel complex code. You can read about refactoring in Martin Fowler's Refactoring: Improving the Design of Existing Code (Addison-Wesley, 1999). Unfortunately, refactoring is a complex subject beyond the scope of this book.

As a final word of advice: if you know from the inception that you are writing a mission-critical enterprise application in Excel and have never done so successfully before, then get help early in the process. A mentor, and architect, or an experienced cohort will help you prevent problems that may later be intractable. Refactoring is a great aid in factoring out, reusing, and simplifying code, but even refactoring has its limitations. Mission-critical enterprise applications need to be guided by an experienced architect or a person that has successfully implemented a similarly complex application in the past. It's best to know your own limitations and address these accordingly.

Managing a Project

VBA applications are project-centric. The code you write in modules comprises a bigger project, which includes the workbook and worksheets. From within the VBE you can determine the modules that are in your project by opening the Project Explorer. You can open the Project Explorer by selecting View Project Explorer in the VBE or by pressing Ctrl+R. When you create a new workbook the Project Explorer should look like the one in Figure 2-1.

Figure 2-1

Sheet1 is a class module that represents Worksheet1. Sheet2 represents Worksheet2, and Sheet3 represents Worksheet3. ThisWorkbook is the class module that represents the currently opened workbook. These modules are VBA's view of the workbook and worksheets in that workbook. Thus, if we add a new worksheet to the default group of three worksheets then we will see a new class module Sheet4 in the Project Explorer.

While a typical user may never know or care that there are class modules representing each worksheet and the workbook, we need to know. The Project Explorer is the means by which we organize all of the modules and forms in our application. As you need to you can add additional modules, class modules, and UserForms to the project from the VBE's Insert menu of the Project Explorer's context menu. (Context menus are activated by right-clicking over the context item: in this case by right-clicking over the Project Explorer itself.)

Managing Control Layout

When you add a UserForm to a project, a new form module will be added and shown (see Figure 2-2). In addition, the Toolbox will be opened too. Adding controls to a form is a lot like using a paint program—click the control in the Toolbox and click a location on the form where you want that control to be painted. You may also use the left-mouse button to drag an outline around the region that the control should fill, as shown in Figure 2-2.

Figure 2-2

The dots that appear on the form while you're designing it enable you to easily align the controls visually to aid in creating a uniform presentation. In addition, the VBE's Format menu contains several menu items for aligning, sizing, centering, ordering, and managing the horizontal and vertical spacing of controls. These menu items operate on a group of controls. To select multiple controls, left click and drag a dashed-line around the controls to format. (Alternatively, you can use Shift+Left-click to add controls to a group.) For example, to align all Label controls on the left, select the desired Label controls and choose Format Align Left from the menu. These menu items explain their operation for the most part and with a modest amount of practice you will find them easy and convenient to use.

Adding Classes

A class module is a special kind of module that actually describes a Component Object Model (COM) class. When we talk about object-oriented programming or object-based programming in VBA, we are referring to the code written in class modules and the code we write that interacts with existing classes, such as Workbooks, Worksheets, and Ranges.

If you define a class module, then you will need to declare variables of that class's type using the name of the module, and use the Set and New operators to create and initialize an instance of the class. A plain vanilla module does not require you to use Set or New. Members of plain modules are technically static members—that is, a procedure in a plain module is considered to be a member of a single instance of a class, which is the module containing it. Let's digress for a moment here.

In C++, static members are members that are accessible without an object instance; these members are also shared by all instances of the class. For a language like C++, the programmer has to write everything explicitly, and using static members in C++ requires more effort on the part of the programmer. Contrarily, languages like VBA are designed to be a bit easier to use but less powerful. For example, a module in VBA is really a static class—not in the VBA sense, but in the C++ sense—and all members can be accessed with the module name but without an instance of the module. To add to the confusion, VBA has its own use for the word static.

VBA uses the word static to mean that procedure variables are not created on the CPU's stack memory; instead static in VBA means that variables are created in the data memory. The reason procedure variables normally have procedure scope is that the memory for these variables is created in the CPUs stack space, which grows and shrinks each time a procedure is started and finished. The stack memory is reused each time one procedure finishes and another starts, overwriting what was there previously, including the previous procedures variables. Static in VBA also means that a procedure's variable is stored in the data memory and is consequently not overwritten by the accordion-like expanding and contracting of the stack memory. For this reason, VBA static variables maintain their value between successive calls to their containing procedure.

If you've ever had to suffer from a C++ programmer scoffing at VBA, then you know it's important to understand that in reality the amount of knowledge required to program in C++ (or Delphi, VB .NET, or C#) is significantly greater than that required to program in VBA (which occasionally may swell an immature ego). However, it is equally important to note that there are many problems that can more quickly and easily be solved by VBA, and good and bad programmers can be found writing code in all languages. If you really want to know about things like pointers, addresses, operator overloading, static members, interfaces, inheritance, multiple inheritance, multithreading, stack frames, polymorphism, abstract classes, templates, and generics, then learn C++ and assembler. If you need to learn how to crunch numbers and data, then you are already in the right place.

Modifying Properties

You will recall from Chapter 1 that classes and modules contain properties. This applies to new and existing classes. As part of the concept of a visual design tool, a special category of class called controls can be modified in the VBE. These control classes can be selected and their state changed at design time via the Properties window.

The Properties window can be opened in the VBE by pressing F4 or clicking View Properties window from the menu. In the Properties window you will find a drop-down list of controls with the current control selected and an alphabetized list of the public properties, which can be modified for that control. Change the property and the value will be reflected in the control at design time. For example, to change the caption of UserForml from the last section to My Form, click the UserForm, find the Caption property and modify the text in the editable region to the right.

Some properties are easy to modify like the Caption property, and others are more complicated. Other properties like Fonts have several subproperties and an editor is provided. For instance, to change the Fonts for a UserForm, select the UserForm, open the Properties window, find the Font property and click the ellipse in the edit region (see Figure 2-3).

Figure 2-3

Figure 2-4

Clicking the ellipse will invoke the Property Editor for that property (see the Font Editor in Figure 2-4). Click OK when you are finished modifying the property and all changes will be updated in the Properties window and reflected in the control.

Importing and Exporting Visual Basic Code

VBA code and Visual Basic Code are almost identical. This means that when you are inclined to share code or are hunting for code, you can search code explicitly written for Microsoft Office and code written for VB6. This means that there are several million other VB programmers in the world with which you can pool your resources. (For example, Paul Kimmel writes a free newsletter for codeguru.com on VB called Visual Basic Today and much of the code in those articles can be used almost directly in your Excel VBA.)

A good rule of thumb is to share as much code as possible before writing any new code. Existing code has likely been read and debugged a couple of times, and may ultimately aid in solving a problem much more expediently. If nothing else, reuse your own code. You can import and export existing VB code from the File menu or the Project Explorer's context menu. For instance, select Sheet1 in the Project Explorer click File Export File to save Sheet1 as Sheeti.cls. (The .cls extension indicates that Sheet1 is a class module.)

Keep in mind that different tools and environments have differing defaults. For example, objects readily accessible as a Workbook project, like Workbook and Worksheets, will not automatically be referenced in a tool like Visual Basic 6. In this case, you would need to add a Reference to MS-Excel in the VB6 project. Sharing Excel VBA code among Excel users is relatively straightforward; sharing code between other versions of Visual Basic will require a bit more legwork on your part. (See Chapter 14 for more information on using VB6 code in Excel.)

Editing

The editing capabilities of the VBE contain features found in word processors and additional features pertinent to programming language editors. The Edit menu contains the features for copying, cutting, and pasting, as well as text search and replace and undo features. You will also find the Edit Indent and Edit Outdent menu items for block indenting and outdenting code. Consistent with other word processors, the Edit menu contains a Bookmark item for tagging locations in code. This feature will permit you to quickly move back and forth between locations of interest in code. Check out the Edit Bookmarks menu item for available options.

Another feature unique to code editors is the Complete Word—Edit Complete Word, or Ctrl+Space—feature; this menu item will display a drop-down list of choices, but does not apply to keywords. For example, if you are looking for a list of members of a Worksheet, simply type the Worksheet object's name, followed by a period, and press Ctrl+Space. A list of all of the members of the Worksheet class will be displayed.

Modern object models are usually way too vast to become extemporaneous knowledge. The best programmers will master the grammar, common idioms, and powerful design patterns and let the Help documentation and intelligent editors remember the nuts and bolts of parameter type, order, and count.

Managing Editor Options

We seldom monkey about with the VBE options. The most significant change we like to make is to Tab Width 2 characters instead of four (see Figure 2-5). To explore or modify editor options select Tools Options from the VBE main menu.

Figure 2-5

In our experience, the most common reason for changing an item is to increase the font size when making a presentation—the default font size of 10 pixels doesn't seem big enough when projected on an overhead. You won't find significant productivity gains to be had in the editor Options dialog box, but it is worth knowing about.

Running and Debugging Code

Becoming adroit with the running and debugging options in the VBE can lead to good productivity gains. The Run menu contains the Run Sub/UserForm (F5), Break, Reset, and Design Mode menu items, and the Debug menu contains Compile VBAProject, Step Into, Step Over, Step Out, Run To Cursor, Add Watch, Edit Watch, Quick Watch, Toggle Breakpoint, Clear All Breakpoints, Set Next Statement, and Show Next Statement menu items. All of these menu items are useful, but typically we find the Run (F5), Step Into (F8), Quick Watch (Shift+F9), and Toggle Breakpoint (F9) menu items the most useful.

VBA is an interpreted language that can also be compiled to PE-code (or portable executable code). The Run and Step Into menu options begin the debugging process. The Quick Watch (Shift+F9) menu item will open a dialog box displaying the value of the name under the cursor. The Breakpoint (F9) inserts a low-level interrupt 3 and a visible red dot—think Stop sign—and highlights the line containing the breakpoint in a dark red color. Interrupt 3 is a lower level debug interrupt added by your PCs BIOS. Using breakpoints permits you to run a long process breaking exactly before a point where you want to begin evaluating the state of your application. (Refer to Chapters 7 and 8 for more information on debugging and testing.)

Using Watches

Watches, Locals, and Quick Watch are all useful for evaluating the state of your application. Watch windows can be activated from the View and Debug menus. The View menu contains the Immediate window, Locals window, Watch window, and Call Stack, and the Debug menu contains Add Watch, Edit Watch, and Quick Watch.

The Immediate window permits a programmer to enter code directly in this simplified editor and test the code with immediate results one statement at a time. The Locals window (see Figure 2-6) displays the variables that are visible in the local or, procedural, scope. For example, inside of a class like the Worksheet class, the Locals window will always display at least the internal reference to itself, the Me object. Me is an object's pointer to itself.

Figure 2-6

The Watch window behaves just like the Locals window except that the programmer places the objects to watch in the Watch window. The benefit of the Watch window is that an object's state will automatically update as your program runs, permitting you to evaluate the evolving state of your application. In contrast, the Quick Watch will only display one watch at a time and is displayed in a model dialog box; thus the program execution is suspended when a Quick Watch is displayed. Each of the Locals, Watch, and Quick Watch windows will permit you to drill down into an object's state.

The Call Stack window portrays the list of program execution branches in reverse order with the most recent branch listed first followed by the previous branch. One can use the Call Stack to see the exact order in which methods are called and quickly navigate in the editor between branched locations. The Call Stack Window can be invaluable tool for debugging code.

The Add and Edit Watch windows are model dialog boxes used to add and modify values in the watch window. Watch values can be simple variables, complex objects, or valid expressions. For example, A= 10 is a valid watch value that will yield a Boolean True or False depending on the value of A. (Chapters 7 and 8 contain practical examples for debugging and testing your code.)

Using the Object Browser

There are probably dozens of object models for each language and framework in existence. We program very well in C++, C, C#, Delphi, Visual Basic, VBA, and reasonably well in languages like assembly, Jscript, VBScript, Java, Clipper, Fox Pro, and can adequately survive with languages like Cobol. The key to programming in any language is to master the grammar. Fortunately, most grammars have similarities, so the trick is to become comfortable with a particular language's object model or frameworks and the language's incumbent idioms and constructs.

Delphi has its own framework, the Visual Control Library or VCL. C# and Visual Basic .NET have the .NET Framework. Microsoft's C++ has the Microsoft Foundation Classes, and Borland's C++ (prior to C++ Builder) had the Object Windows Library, and there are frameworks for other languages like Java, as well as third party frameworks for language vendors. (Any C++ programmers remember the Zinc library for early versions of C++?)

It is worth mentioning all of these languages and frameworks, so that mastery of any particular framework is kept in perspective. With 1000 programming languages, many variants of each language, potential tens of thousands of classes, methods, properties, fields, events, and interfaces in each framework, it is almost impossible to memorize even modest-sized chunks of any one framework. A good strategy is to master the keywords and grammar of each language you intend to use. Become familiar with the common idioms and constructs, and then rely heavily on the Help documentation. Toward this end the VBE—and many other tools—support an Object Browser. By selecting View Object Browser from the VBE's menu the Object Browser dialog box will be displayed. This dialog box provides a hierarchical, summarized list of classes and members. From this list you can use the Help documentation to obtain further details. In this way you will eventually gain some extemporaneous knowledge of the parts of the framework you frequently use.

A good rule of thumb for new developers is to spend some time checking to see if the VBA object model already supports a feature before you build a solution from scratch. If a VBA object model and Help documentation search doesn't bear fruit then check the Windows API (see Chapter 16). Further still, before you build it from scratch, check the Internet and existing third party solutions, again, before building a custom solution. Finally, build a solution only if you must. Even a couple of hours searching for an existing code will be time more productively spent than building all but the most trivial algorithms from scratch.

Summary

Programming is a sport that requires practice. Like all mental and physical activities, there seems to be a certain amount of learning that occurs in the body and mind. This chapter provided you with a quick tour of some key features in the VBE that we will use throughout the book. We took the time to point these features out because we were recently surprised to work with a group of developers that had been programming for a year and didn't know about the Quick Watch window. Now you know.

The VBE is a powerful word processing tool designed to function with the Visual Basic for Applications programming language. We encourage you to spend some of your own time exploring each main menu item and the context menus and toolbars for each part of VBA. You will likely find some features that you haven't used before, and these will enable you to work more productively with this book and as a programmer.