VBA Unleashed - Introduction

Visual Basic for Applications Unleashed

Introduction

Invention is the mother of necessity.
—Thorstein Veblen

When Excel 5 was released in 1994, developers and power users were thrilled that it included the first incarnation of Microsoft's long-awaited new macro language: Visual Basic for Applications (VBA). Many people discarded the creaky old Excel 4 macro language without a second thought and waited eagerly for Microsoft to fulfill its promise to make VBA the common macro language for all its applications.

Other Office products were slowly brought into the VBA fold, but it's only with the release of Office 97 that Microsoft has finally achieved its goal. Now all of the Big Four—Word, Excel, Access, and PowerPoint—have a VBA development environment at their core. Not only that, but just about everything in the Office 97 package is programmable: Outlook, Office Binder, even the Office Assistant.

But unprecedented programmatic control over Office objects is only the beginning. Microsoft is now licensing VBA as a separate product—called VBA 5.0—that other companies can incorporate into their own applications. A number of developers have leaped willingly onto the VBA 5.0 bandwagon, including such heavyweights as Adobe, Autodesk, Micrografx, and Visio. And as if that weren't enough, Microsoft has also created a version of VBA—called VBScript—that programmers can use as a scripting tool for Web pages.

There is little doubt, then, that VBA plays a huge role in Microsoft's future plans. Anyone interested in truly unleashing the power of the Office applications, other programs, and Web pages will need to learn the VBA language. The good news is that VBA combines both power and ease of use. So even if you've never programmed before, you won't find it hard to create useful procedures that let your applications perform as they never have before.

VBA 5.0: Something New for Everyone

VBA 5.0 is no mere incremental upgrade. Since the previous version was released with Office 95, Microsoft has spent its time revamping the interface and cramming the VBA tool chest with countless new programming gadgets and gewgaws. (In case you're wondering, the "5.0" designation doesn't mean all that much. It just synchronizes the version numbers of VBA and the latest incarnation of Visual Basic.) I'll be showing you how to take advantage of these new features throughout this book, but let's begin with a sneak preview so you'll know what to expect:

VBA is now Office-wide: As I mentioned earlier, VBA is now the common macro language for the entire Microsoft Office suite. This means that you can leverage your existing knowledge of VBA syntax, statements, and functions and put it to immediate use with the objects exposed by Word and PowerPoint. But that's not all. Thanks to the wonders of OLE Automation, other Office tools expose their objects to VBA. This means, for example, that you can compose and send e-mail messages programmatically by manipulating the appropriate objects in Outlook.

A new integrated development environment: Word, Excel, and PowerPoint now share a new integrated development environment (IDE). This is a separate VBA window that gives you a "big-picture" view of the current VBA project. As you can see in Figure I.1, this view includes a Project Editor that lists the application objects (documents) in the current file and a Properties window that lists the available properties for the current object. You can use this IDE to add new modules and forms to the project, write code, and debug your procedures.

The new VBA IDE
Figure I.1.
You now work with VBA in a separate IDE.

Internet/intranet support: The Internet and intranet features built into Office 97 are fully supported in VBA. This means that your procedures can create and work with hyperlinks, incorporate Internet Explorer's browser objects, send e-mail, and more. Also, you can use VBScript—a subset of the VBA language—to validate Web page form input and establish lines of communication between Web page objects.

Support for ActiveX controls: VBA can work with any of the ActiveX controls (formerly known as OLE controls) that are installed on your system. This lets you set up dynamic forms and dialog boxes with richer content.

Improved code editor: The editor you use to write VBA code has been beefed up with some welcome new features. In addition to existing features such as on-the-fly syntax checking and color-coded keywords, the new editor also includes the IntelliSense feature, which provides syntax help on demand. In Figure I.2, for example, you can see that the editor displays a pop-up menu that shows you a list of the properties and methods you can use to complete a line of code. The editor also displays the appropriate arguments when you enter a function or statement.

The new Object Browser
Figure I.2.
The new IntelliSense feature provides syntax help on demand.

A common forms-building tool: For interactive applications, Word, Excel, and PowerPoint share a common forms-building tool that you can use to create feedback forms and dialog boxes. The objects you use to build these forms have a number of properties, methods, and events, so you can control any aspect of a form programmatically.

The new and improved Object Browser: Objects are at the heart of VBA, and the vast majority of your VBA procedures will manipulate one or more objects in some way. However, Office exposes well over 500 objects and untold thousands of properties, methods, and events. To help you keep everything straight, the Object Browser has been greatly improved (see Figure I.3). It now groups each object's properties, methods, and events (with separate icons for each type), lets you search for objects and members, provides hypertext links to related objects, and lets you view the associated Help topic for the current item.

Figure I.3. The new Object Browser.

Improvements to Data Access Objects: The Data Access Objects (DAO) model has been enhanced to improve performance, support database replication, and provide better support in multiuser environments. In addition, you can use a new client/server connection mode called ODBCDirect to establish a direct connection to an ODBC database without loading the Jet database engine.

Command bars: Working with menu bars, toolbars, and shortcut menus has been streamlined in VBA 5.0. A new command bars object model encapsulates each of these objects into a single structure with common properties and methods.

Class modules: VBA now lets you use class modules to set up your own objects. Procedures and functions defined within a class module become the methods and properties of the user-defined object.

Improved security: To prevent users from accessing (and possibly modifying) your code, you can now set up VBA projects with password protection. Because VBA modules and forms are separate from the document objects (for example, Excel no longer has module sheets), securing your project in no way restricts the user from working with the underlying document.

Conditional compilation: Your VBA procedures can now use conditional compilation to control which statements get compiled. For example, if you use the Windows API, you'll need to differentiate between 16-bit calls and 32-bit calls. Similarly, you might want to include debugging "flags" in your code and use conditional compilation to turn certain debugging features on when you're testing and off when you distribute the application.

Office 97 Developer Edition: Microsoft has put together a separate version of Office aimed squarely at VBA developers. This "Developer Edition" includes not only Microsoft Office Professional, but also a Setup Wizard for distributing your applications, a Replication Manager for viewing and managing replicated databases on a network, and Visual SourceSafe for managing team development efforts.\

What You Should Know Before Reading This Book

My goal in writing this book was to give you complete coverage of the VBA language, as well as numerous examples for putting the language to good use. Note, however, that this book isn't a programming tutorial per se. So although I cover the entire VBA language, many relatively low-level topics are presented quickly so that we can get to meatier topics. Therefore, although you don't need programming experience to read this book, knowledge of some programming basics would be helpful.

I've tried to keep the chapters focused on the topic at hand and unburdened with long-winded theoretical discussions. For the most part, each chapter gets right down to brass tacks without much fuss and bother. To keep the chapters uncluttered, I've made a few assumptions about what you know and don't know:

I assume you have knowledge of rudimentary computer concepts such as files and folders.
I assume you're familiar with Windows and that you know how to launch applications and use accessories such as Control Panel.
I assume you're comfortable with the basic Windows 95 interface. This book doesn't tell you how to work with tools such as menus, dialog boxes, and the Help system.
I assume you can operate peripherals attached to your computer, such as the keyboard, mouse, printer, and modem.
I assume you've installed VBA 5.0 (via Office 97 or some other VBA-enabled application) and are ready to dive in at a moment's notice.
Most of this book's examples involve Office 97. Therefore, I assume you've used the Office programs for a while and are comfortable working with these programs.
I assume you have a brain and are willing to use it.

How This Book Is Organized

To help you find the information you need, this book is divided into eight parts that group related tasks. The next few sections offer a summary of each part.

Part I: Introducing VBA

The five chapters in Part I provide intermediate-to-advanced coverage of the fundamentals of VBA programming. Chapter 1 familiarizes you with the layout of the new VBA Editor, and subsequent chapters teach you about variables, operators, expressions, objects, and procedure control.

Part II: Unleashing Microsoft Office Objects

Part II takes an in-depth look at programming all the major Office 97 applications. I'll discuss the objects, properties, methods, and events that are unique to Word, Excel, PowerPoint, and Access.

Part III: Unleashing VBA User Interface Design

The look and feel of your VBA applications is the subject of the three chapters in Part III. We'll begin with some basic methods for interacting with the user, and then I'll show you how to build forms and control them programmatically, how to assign your procedures to menus and toolbars, and how to use VBA to build menus and toolbars via code.

Part IV: Unleashing Application Integration

These days, it's a rare VBA application that operates in splendid isolation. Most applications will require at least a little interaction with software other than the underlying VBA program. Integrating multiple applications is the topic of the four chapters in Part IV. You'll learn how to control other programs directly, how to use Dynamic Data Exchange, how to program OLE and ActiveX objects, how to control programs via OLE Automation, and how to work with class modules.

Part V: VBA Database Programming

Working with information stored in databases and tables is a crucial topic in this era of client/server setups and intranet-based organizations. From simple Excel list maintenance chores, we'll progress to programming the powerful Data Access Objects model, working with ODBC, dealing with database security, and lots more.

Part VI: Unleashing VBA Internet and Intranet Programming

Part VI shows you how to program VBA's Internet- and intranet-related features. Topics include programming hyperlinks, building a custom Web browser, taking advantage of Outlook's built-in groupware features, building Web pages with the ActiveX Control Pad, and programming Web pages with VBScript.

Part VII: Unleashing Advanced VBA Programming

This part of the book presents a hodgepodge of advanced VBA techniques. You'll learn how to trap errors, how to debug your code, how to use the Windows API, and how to work with low-level file I/O and compiler directives.

Part VIII: Unleashing VBA Applications

Fine words butter no parsnips, as they say, so I've crammed this book full of useful, real-world examples. Most of these examples highlight a specific feature, so they tend to be short. Here in Part VIII, however, I offer up some longer examples that show you how to put together full-fledged VBA applications.

The Appendixes

I've also tacked on a few extra goodies at the end of this book. The appendixes include a complete listing of VBA's statements and functions (Appendixes A and B), the Windows ANSI character set (Appendix C), an HTML primer (Appendix D), and some material related to the CD that comes with this book (Appendix E).

About the CD

This book comes with a CD-ROM that contains the following:

All the VBA code used as examples throughout the book.
Miscellaneous files from examples used in the book.
ActiveX controls.
Third-party programs that will help you create VBA applications.

This Book's Special Features

Visual Basic for Applications Unleashed is designed to give you the information you need without making you wade through ponderous explanations and interminable technical background. To make your life easier, this book includes various features and conventions that help you get the most out of the book and VBA itself.

Steps: Throughout this book, each VBA task is summarized in step-by-step procedures.

Things you type: Whenever I suggest that you type something, what you type appears in a monospace font.

Commands: I use the following style for application menu commands: File | Open. This means that you pull down the File menu and select the Open command.

Visual Basic keywords: Keywords reserved in the Visual Basic for Applications language appear in monospace type.

Function arguments: Throughout this book, I'll be providing you with the proper syntax for VBA's many functions. These syntax constructions contain arguments that fall into two categories: required (arguments that you must include when calling the function) and optional (arguments that you can skip if you don't need them). To help you differentiate between these types, I'll show required arguments in a bold, italic, monospace font, and I'll show optional arguments in an italic, monospace font.

The code continuation character ([ic:ccc]): When a line of code is too long to fit on one line of this book, it is broken at a convenient place, and the code continuation character appears at the beginning of the next line.

The VBA 5.0 icon: This icon highlights features that are new to VBA 5.0.

The CD icon: This icon tells you that the file being discussed is available on the CD that comes with this book.

This book also uses the following boxes to draw your attention to important (or merely interesting) information.

NOTE
The Note box presents asides that give you more information about the topic under discussion. These tidbits provide extra insights that give you a better understanding of the task at hand. In many cases, they refer you to other sections of the book for more information.

TIP
The Tip box tells you about VBA methods that are easier, faster, or more efficient than the standard methods.

CAUTION
The Caution box tells you about potential accidents waiting to happen. There are always ways to mess things up when you're working with computers. These boxes help you avoid at least some of the pitfalls.

The Acknowledgments Department

Robert Pirsig, in Zen and the Art of Motorcycle Maintenance, wrote that "a person who sees Quality and feels it as he works, is a person who cares." If this book is a quality product (and I immodestly think that it is), it's because the people at Sams editorial cared enough to make it so.

I would therefore like to thank all of the people who helped make this book a reality. I'd like to begin by thanking Acquisitions Editor Dean Miller for, well, acquiring me and for pulling the project together. Many thanks go to Development Editor Brian-Kent Proffitt for doing a great job and for showing compassion to a sore-wristed author. As always, I reserve some special thank-yous for Production Editor Gayle Johnson who, more than anyone I know in publishing, sees Quality and feels it as she works. I'd also like to thank Copy Editor Bart Reed for his uncanny ability to find my dumb mistakes, and Technical Editor John Charlesworth for making sure my instructions and code cut the technical mustard. Finally, a big thank-you goes out to Software Development Specialist Patty Brooks for putting together the excellent CD that appears in the back of the book.

Online Resources for This Book

In this age of global communications and speed-of-light conversations, the notion that the relationship between a writer and his readers will go no further than the book you now hold is rather quaint, if not downright antiquated. This is particularly true when the book's subject matter is programming, because learning how to control these unruly electronic beasts through code is a lifelong process. To that end, I've assembled several online resources that you can use to further your VBA education and commune with like-minded souls.

For starters, if you have any comments about this book, or if you want to register a complaint or a compliment (I prefer the latter), please don't hesitate to send an e-mail missive to the following address:

paul@mcfedries.com

Better yet, feel free to drop by my Web site, have a look around, and sign the Guest Book:

http://www.mcfedries.com/

Note that I have a home page for Visual Basic for Applications Unleashed at this site. Here you'll find book excerpts and info, code samples, links to VBA information, programs and utilities written by other readers, and lots more. To go straight there, dial the following address into your Web browser:

http://www.mcfedries.com/books/VBAUnleashed/

About the Author

Paul McFedries is a computer consultant, programmer, and freelance writer. He has worked with computers in one form or another since 1975, he has a degree in mathematics, and he can swap out a hard drive in seconds flat—yet still, inexplicably, he has a life. McFedries is the author or coauthor of more than two dozen computer books that have sold nearly 1 million copies worldwide. His titles include Paul McFedries' Windows 95 Unleashed, Microsoft Office 97 Unleashed, and Navigating the Internet, all from Sams Publishing.

Other hats worn by McFedries on occasion include video editor, animator, bread maker, Webmaster, brewmaster, cruciverbalist, and neologist. He has no cats, and his favorite hobbies are shooting pool, taking naps, riding his motorcycle, and talking about himself in the third person.

Back to the VBA Unleashed home page