VBA[1] Visual Basic Application 可视Basic应用 Invention Is the Mother of Necessity[2] With the re

VBA^[1]Visual Basic Application

可视Basic应用

Invention Is the Mother of Necessity^[2]

With the release of Office 2000, Visual Basic for Applications has now been on the macro programming stage for about 5 years. That's not all that long, so it's more than a little remarkable^[3]that VBA has gone from programming upstart to application stalwart in that short time. After all, do you know anyone who still uses the old Excel macro language or WordBasic？ Probably not and for good reason. Trying to pull together even a modest program using those clunker languages was a nasty and long process. On the other hand, VBA, with its superb programming environment and easy-to-learn syntax, can handle even the most ambitious application programming chores.

That's not to say, however, that VBA is only good for major projects. As a writer, I use Word constantly, and over the years, I've developed dozens of small macros, functions, and forms that streamline or automate repetitive chores. Most of these routines consist of only a few lines of code, and each one saves me only about 30 seconds to a minute, depending on the task. But I use these routines 50 or 100 times a day, so I end up saving myself anywhere from 30 to 90 minutes a day! That's pretty remarkable, but the proof is in the pudding^[4]: I can now write far more pages in a day than I used to.

Whether your concern is ease-of-use or personal productivity, there's little doubt that VBA is the Office programming language of choice. So now all you have to do is to learn how to use it, and that's where this article comes in. I take you through all the basic programming constructs, including variables, loops, and the other tools that are part of every coders arsenal. I also run through every feature found in the VBA Editor and discuss the all-important topic of objects, particularly those objects that form the heart of Word, Excel, PowerPoint, and Access. So even if you've never programmed before, this article will have you acquainted in no time flat.^[5]Experienced programmers will appreciate the article's intermediate-to-advanced sections that cover user interfaces, database programming, Internet programming, debugging, and much more.

What's New in VBA 6.0

The version of VBA that ships with Office 2000 is 6. 0. This new version isn't radically different from the VBA 5.0 that was featured in Office 97. However, there are hundreds of changes, large and small, so there should he something for everyone. Here's a summary of a few of the major changes that VBA 6.0 brings to the programming table:

Support for new document types: You can use VBA 6.0 to create the new document types supported by Office 2000 (such as the new email message document type used by Word 2000).

New Web Options settings. One of the hallmarks of Office 2000 is the ease with which documents can be moved from the desktop to the Web and back. Certain important aspects of this are controlled by a series of "Web Options," and VBA can control these options programmatically.

Publishing to HTML^[6]: Excel 2000 and PowerPoint 2000 enable you to take a subset of a document (such as a range or a series of slides) and publish it in HTML format. You can do this via VBA, as well.

Access gets the Visual Basic Editor= Access developers will be pleased to hear that the Visual Basic Editor is now fully integrated into the Access interface.

Outlook supports VBA: Outlook 2000 now offers full VBA support, including the Visual Basic Editor. This means you can now use the program as a base for projects.

Script Editor for VBScript: If you want to create VBScript (as subset of VBA) routines for Web pages, Office 2000 now includes a Script Editor application for creating, running, and debugging scripts.

Digitally sign your projects: To ensure the integrity of the VBA projects you distribute, you can now use a certificate to digitally sign your code.

Support for OLE^[7]DB and ActiveX Data Objects (ADO)^[8]. The future of programmable database access is ADO, and a new object library enables VBA developers to work with ADO.

New Application object events: Like Excel, the Application objects for Word and PowerPoint now support various events. And, like Excel, you need to use class modules to trap these events.

New Menu Feature: The CommandBar object has been modified to work with the new Office 2000 features, including adaptive menus and the ability to place hyper links on menus and toolbars.

Well Begun Is Half Done^[9]

This chapter gets your VBA education off to a rousing start by introducing you to procedures, functions, and the Visual Basic Editor. I'll begin by showing you how to use VBA to record simple macros that help automate routine tasks. To get the most out of VBA, however, you need to do some full-fledged programming. To that end, this chapter introduces you to the basics of writing simple procedures and functions, as well as how to get around in VBA's integrated development environment. This will set the stage for the next few chapters when I take a closer look at the specifics of the VBA language.

What Is a Macro？

A macro is a small program that contains a list of instructions that you want a program to perform. Like DOS batch files, macros combine several operations into a single procedure that you can invoke from a menu, toolbar button, or keystroke. This list of instructions is composed mostly of macro statements. Some of these statements perform specific macro-related tasks, but most correspond to the underlying application's menu commands and dialog box options^[10].

How Does VBA Fit in？

VBA is a programming environment designed specifically for application macros.

As I mentioned in the Introduction, VBA is now the standard language in the Office suite, and a number of other companies have incorporated VBA into their applications. The ubiquity of VBA has an obvious advantage: a standard language means that you have to learn only one set of statements and techniques for all the programs that support VBA. It also means that applications will get along better than they ever have, because VBA "knows" the functions and commands used by every problem.

The power of VBA is clear, but perhaps its biggest advantage is that it's just plain easier to use than most programming languages (including the old macro languages used in Word, Excel, and Access). If you don't want to do any programming, VBA enables you to record macros and attach them to buttons either inside a document or on a menu or toolbar. You also can create dialog boxes by simple drawing the appropriate controls onto a document or onto a separate "user form. " Other visual tools enable you to customize menus and toolbars as well, so you have everything you need to create simple applications without writing a line of code.

Of course, if you want to truly unleash VBA's capabilities, you will need to augment your interface with programming code. Unlike the old WordBasic or Excel 4.0 macro languages, VBA 6.0 is a full-blown programming environment that includes most high- level programming constructs as well as access to just about every feature in the application. Add the powerful debugging tools and the ability to create a Help system into the mix, and you have everything you need to create professional-level Office applications.

The Three Types of Procedures

In VBA, a procedure is, broadly speaking, a collection of related statements that form a unit and perform some kind of task. VBA procedures come in three flavors, command macros, used-defined functions, and property procedures. Here's a summary of the differences:

1. Command macros are the most common type of procedure; they usually contain statements that are equivalent of menu options and other program commands. The distinguishing feature of a command macro is that, like regular application commands, they have an effect on their surroundings. Whether it's formatting some text, printing a document or creating custom menus, command macros change things. (I show you how to create command macros in the section in this chapter titled "Writing Your Own Command Macro. ")

2. Used-defined functions (also called Function procedures) work just like a program's built-in functions. Their distinguishing characteristic is that they accept arguments and then manipulate those arguments and return a result. A properly designed function has no effort on the current environment. (I show you how to create these functions in the section of this chapter titled "Creating Used-Defined Functions with VBA. ")

3. Property procedures are used to return or set a property value for an object you've defined. If you define an object, you create property procedures to handle the object's properties. For example, suppose you've defined a car object. If someone using this object wants to change the car's color to red, you would set up a property procedure that does actual painting.

Recording a VBA Macro

By far the easier way to create a command macro is to use the macro recorder. With this method, you just run through the task you want to automate (including selecting text, running menu commands, and choosing dialog box options), and the Recorder translates everything into the appropriate VBA statements. These are copied to a separate area called a module, where you can then replay the entire procedure any time you like. This section shows you how to record a command macro in Word, Excel, or PowerPoint.

To see your macro, select Tools, Macro, Macros (or press Alt+F8) to display the Macro dialog box. In the Macro name list, highlight the macro you just recorded and then click the Edit button. The application opens the Visual Basic Editor window and then opens the module and displays the macro. The application (Excel, in this case) translates your actions into VBA code and combines everything into a single macro.

Notes

[1] Visual Basic Application: 可视Basic应用程序。

[2] Invention is the mother of necessity. 发明是需要之母。

[3] more than a little remarkable:让人吃惊的是。

[4]该句源自The proof of the pudding is in the eating.(谚)要验布丁，就要吃一吃(或空谈不如实验)。此句大意是：正在验证中的布丁，正在尝试中。

[5] this book will have you acquainted in no time flat：此书让您立刻深谙此道。flat副词，表示“整”，如20 seconds flat 20秒整。

[6] HTML(Hypertext Markup Language)：超文本标记语言。

[7] OLE(Object Linking and Embedding)：对象连(链)接与嵌入客户系统。

[8] ActiveX Data Objects (ADO)：Active X数据对象。

[9] Well begun is half done.(谚)好的开端是成功的一半。

[10] dialog box options：对话框中的选项。

Choose the best answer: