With the release of Office 97, Microsoft has officially unveiled Visual Basic for Applications 5.0, a version of Visual Basic designed to be embedded in a host product for use as a scripting language. Alongside the standard Office suite, Microsoft introduced a developer edition that packages Office 97 with a series of ancillary application development tools. These include a data replication manager, a source code control interface for Access 97, a wizard for creating setup programs, help creation tools, and ActiveX controls, plus complete printed documentation on using VBA with the Office products. (For more on Access 97, see Tom Spitzer's column in the March 1997 DBMS, page 81.)
With this packaging, Microsoft explicitly positions the Office suite as a platform for developing custom business solutions. The technical foundation for this position has four pillars: exposing the rich functionality in the applications through an object-like interface, supporting OLE automation for messaging transport within and between applications, providing access to many database and messaging sources by supporting MAPI and ODBC, and providing a common programming environment.
More than 40 other application vendors have signed up to VBA-enable their applications. Upon delivery, those VBA-enabled versions, like the products in the Office 97 suite, will incorporate the VBA development and runtime components. VBA 5.0 provides the complete Visual Basic 5.0 language engine, running within the address space of a host product that provides a collection of application-specific objects for VBA to automate. VBA represents the fulfillment of two Microsoft objectives: to provide a version of Visual Basic suited for any type of application development scenario, and to provide a general-purpose scripting language that can become a de facto standard for extending desktop applications to create custom solutions.
People seem to have the most confusion distinguishing between VBScript and VBA. VBScript is a stripped version of the Visual Basic language designed for execution within the Web browser context. Microsoft distributes the VBScript runtime engine as a component of Internet Explorer on the client and of Visual InterDev on the server. Its primary purpose is to enable Web site developers to script the events to which HTML and ActiveX objects executing within Internet Explorer will respond. The runtime engine is designed to interpret small code blocks downloaded from a Web server during browser sessions, and it lacks support for the VB features that rely on access to underlying operating system functionality.
VBA provides extensive development capabilities from within a desktop application environment. Adding VBA to an application converts it into a "construction site," where developers can create custom business solutions. VBA provides developers with a complete set of tools that help accelerate the process of writing, debugging, and distributing these solutions. VBA applications run within the process space of the application in which they are developed, giving them a significant performance advantage over external automation control programs.
Although Microsoft was the first vendor to deliver VBA-enabled applications, it is no longer alone. A number of the companies that announced VBA support are now delivering enabled products. Microsoft has pursued an interesting business strategy in running its licensing program; it has enrolled two companies that had already been licensing VB-compatible scripting engines -- Summit Software Co. and Mystic River Software Inc. -- as their agents. These two companies were able to make a business out of licensing VB-compatible scripting engines to software vendors before the general availability of VBA, but it certainly was not a wildly successful business. Now VBA makes this business very credible. In addition to software vendors licensing VBA, several Microsoft Solution Providers announced that they would license VBA and incorporate it in custom corporate solutions as a mechanism for users and developers in client organizations.
Developers of VBA-enabled applications need to integrate VBA into their products at the C++ source code level, either via COM interfaces that Microsoft provides or via interfaces that Mystic River and Summit offer. When programmers integrate VBA in this way, they bind it quite tightly into their products' object hierarchy. Application developers, like most of us reading and writing for this magazine, will use VBA as it is integrated into desktop applications to build custom solutions for our clients. VBA 5.0 integrates Visual Basic for Applications, the Microsoft Forms package, and the VB IDE, known as the Visual Basic Environment or VBE. The VBA language component contains the Basic language syntax and runtime functionality. The VBE encompasses all of the UI elements, including the editor, debugger, project explorer, and object browser. Forms, Microsoft's new standard forms package, provides host applications with a robust dialog design tool that produces dialogs that can be completely integrated into the host application.
Microsoft has worked hard to add intelligence to the VBA 5.0 editor, which can provide a great deal of information about available objects and their property, events, and methods (which I'll subsequently join Microsoft in referring to as "PEMs"). As you type the name of an object or PEM, a box appears that displays its arguments. When you complete the name of an object, a selection list appears displaying all of the properties and methods for that object, allowing you to select one. Selecting an object name and clicking on the right-hand mouse button brings up a menu of a series of commands that supply additional assistance. The "List properties/methods" command displays a list of the properties and methods applicable to the selected object and lets you select one from the list. The Parameter Info command displays the name and datatype of the parameters required for the selected item.
The best feature is the ability to provide this assistance not only for object names but also for object variables. As long as you declare object variables and indicate their type rather than simply declaring them as generic objects, the editor will treat subsequent use of the object variable identically to use of the object reference. Take the sequence:
The ability to create class modules is another new capability of VBA 5.0. A class module lets developers define reusable classes. Within a class module, developers can reference other objects, including custom forms and complete applications, and they can add public procedures that define custom methods and properties for those objects. When an instance of the class is created, developers can apply these custom methods and properties as they would for any object.
VBA 5.0 regards all of the forms, classes, modules, class modules, and docs with code behind them as project items, and it views the collection of items associated with a particular document as a project. A project encapsulates a collection of project items, their interactive behavior, and code, and it controls how the project items interact and communicate. Associating VBA code with specific objects and documents is a new concept. Any code used to automate any of the objects within the project is stored in the project. Only the code in the module associated with the object can see that object's events.
One requirement of host applications that integrate VBA is that they provide facilities for the storage and management of VBA projects and their objects in addition to their own documents. In addition to providing such storage, the host application must specify how VBA fits into its user interface. It must create VBA's container window as one of its document types, and it must notify the VBE when it becomes activated or deactivated. When VBA code is executing, the host application must pass on to the VBA engine any messages received by the host application's event or message loop.
VBA-enabled applications, including those in the Office 97 suite, are both automation controllers and servers because they expose their functionality in an object model and they can control other applications via Visual Basic. To automate a task, you must first obtain a reference to the object that contains the content and functionality you want to access, and then manipulate the properties and define actions for that object's methods. I created a very simple -- but for me very useful -- macro that illustrates this point. The macro moves values from a planning worksheet to an actuals worksheet after I incur expenses (I use these worksheets for household budgeting and have wanted to do this for years!).
I started by recording the keystrokes using the Office 97 macro recorder, which translates actions taken via the user interface into a sequence of VBA instructions. I then opened the VBE and modified the resulting code to make it more generic. In the resulting code, I called a function that returns a reference to the current cell. I then used the Cut method of the current selection object to move the contents of that cell to the clipboard, selected the corresponding cell in the actuals worksheet, pasted the value, and reselected my plan worksheet.
Applications that support COM will provide a file that contains an object library (also referred to as a type library). This library describes the application's object model, as well as the objects, PEMs, and built-in constants that it exposes. The object browser presents the information in the type library and lets developers and users browse the object model that it describes. (See Figure 2.) The object browser permits the developer to view the objects defined in a single type library or all type libraries referenced by the current project. To further confuse things, the object browser here refers to objects as classes in the caption on the object list. It does so because it is referring to the class as a type rather than to a specific instance, or object.
When a class is selected, the "Members of" box on the right side of the browser displays a list of all of that object's properties, events, and methods. Selecting one of those PEMs causes the browser to display syntax information, whether it's updateable at runtime, what object library it belongs to, and what data or object type it returns in the detail panel at the bottom of the dialog. From here you can also bring up the VBA help screen that describes the selected PEM. The ability to drag procedure and function templates from the detail panel into a module editor is quite an attractive feature.
Public Sub LoadAccounts()
Dim lOpened As Boolean, iRow As Integer
Dim rs As Recordset, shTgtSheet As Worksheet
' Set up table location and update state as constants
Const cDBFLoc = "c:\cfw"
Const CanUpdate = True
' Try to open the database
lOpened = lOpenDatabase("", "admin", "", dbUseJet, _
cDBFLoc, dbDriverNoPrompt, CanUpdate, "FoxPro
2.5")
Of course, it's a trivial matter to specify an RDBMS source connected via ODBC rather than a local FoxPro table. If you do, the DAO version 3.5 optimizations that I discussed in my March column become applicable; in particular, you can use ODBCDirect to bypass the JET engine entirely and improve the performance of most simple query operations. With the simplicity of this code, and the effectiveness of the development environment, it's now extremely feasible to use Excel and Word themselves as client development systems for applications where the ultimate objective of the application is to work with data in a spreadsheet or word-processing workspace.
The other product I looked at was the EdgeworX Web Development Framework from the Antares Alliance Group (AAG). EdgeworX aims to objectize Web site development and enable development of data-oriented business applications using the COM infrastructure; in fact, AAG has a patent pending on the HTMLDCOM object library it delivers with the tool. In the EdgeworX tool, VBA will run as a server-side scripting language, interacting with HTMLDCOM objects to create HTML that the server will return to the client. Each user connecting to an EdgeworX server will acquire an independent application workspace; the application will execute in its workspace as long as the user is connected, effectively creating a persistent application session for the user. Initially, EdgeworX applications will access data via DAO or ODBC. EdgeworX represents a departure from traditional uses of VBA to automate desktop applications. In EdgeworX, VBA serves as the scripting language for an object-based custom application server. The developers at AAG share my opinion that VBA is a fairly scalable tool.
I expect many more products that incorporate VBA to appear later this year. I recently spoke to a director of product strategy at Platinum Software Corp., a leading mid-market accounting software company, about the direction of that marketplace. He opined that a version of VB optimized for application scripting combined with a COM-based object model offered the best strategy for accounting product vendors who want to offer customizable solutions that leverage Microsoft's desktop products. After several years of talk, VBA finally appears poised to become a desktop standard. If it does, it will have a very positive impact on developer productivity and will serve as yet another piece of fortifications in the COM vs. CORBA struggle.
