InformationWeek
Defining The Business Value Of Technology
November 10, 1997
Visual Basic App Edition Eases Code Reuse
VBA helps IT shops customize and extend existing applicationsBy Don Kiely
Microsoft has taken two steps to make Visual Basic, Application Edition, the standard application programming language for the Windows environment.
First, Microsoft uses the same underlying Visual Basic Application (VBA) programming engine in most of its major Office applications. Second, it has also begun widespread licensing of VBA in response to third-party developer demand, according to Microsoft officials. Some 100 third-party commercial developers have signed on to the program, which is turning VBA into the dominant application programming engine for Windows. This has some big benefits for users and developers-and a few pitfalls as well.
Developers and users can take advantage of applications that host VBA by customizing them to address user needs in three ways. First, the packaged appl
Be aware: Not all Office 97 applications support VBA. Outlook uses VBScript, a lightweight version of the lan-guage designed for Internet applications. VBScript leaves out virtually all design tools commonly associated with a development environment. Microsoft claims it used VBScript for some unspecified future benefit related to the Internet. Access 97 uses the VBA engine, but many of the design tools are very closely integrated with Access. Designing applications in Access is different enough to make switching be
tween it and other VBA applications-including Visual Basic-frustrating. One consolation: You don't have to learn two versions of the language.
VBA Tools
It's easy enough to export a VBA application for use in VB, although you may have to modify the code to fit its new environment. The host application-such as Word, Excel, Visio, or other products that license VBA-contains the VBA engine that runs the custom application. You have complete control over all of the objects in the host application, the same language constructs, and many of the same development tools as VB.
The VBE has four primary windows that imitate the features in VB 5.0. The Project Explorer lets you manage all the parts of your application, including forms, code modules, and the code itself. The Code Window is the same code editor included with VB, along with the IntelliSense features that provide developers with instant syntax reference and object model assistance. The Properties Window lets you view and modify any of the properties of the currently selected item, including objects from the VBA host application, such as an Excel worksheet. The Obj
ect Window is VBE's form-layout window where you design the forms in the application and add ActiveX controls such as text boxes, grid controls, and command buttons.
Code Generator
The one limitation is that the macros generated by the recorder are subroutines without arguments, although you can easily modify the procedure to use them.
How does VBA, both as a language
and a development environment, relate to the various other versions of Basic? The chart on p. 10A shows how it fits into the scheme based on each version's language, development tools, and need for a host application.
VBScript, at the low end, is severely crippled both in its list of supported language features and its complete lack of development tools. This isn't surprising since it was designed for use on the Internet, where bandwidth is more important than robustness. Besides, Web development wizards take pride in designing Web sites using only Windows Notepad.
VBA is the middle child, with the full language specification and a robust set of development tools. VB functions as a host application, providing the full VBA language specification and a complete set of development tools. VB, as a host application, can manifest itself as a separate executable, ActiveX component, or dynamic link library, in either interpreted form or compiled to native code. It can also take advantage of exposed ActiveX
objects within other Windows applications, including Word, Access, and Visio, manipulating them as needed.
VBA code and forms normally reside within a host application's document, such as a Word text document or Excel spreadsheet, but you can export code modules for use in other VBA applications or VB. This lets you design a class module as the definition of an object, then export it for use in other applications. This is a kind of poor man's code reuse, but since the source code is duplicated, any changes you make to code have to be made everywhere you copied the module. But at least you aren't locked into using the module only in Visio, for example, if that's where you originally designed the module.
If you have to create ActiveX objects for use with VBA applications and don't want to buy a copy of VB, you can use Microsoft's free Visual Basic, Custom Control Edition (VBCCE), a subset of the full VB product. The objects you create with VBCCE are the same as any other ActiveX control developed in any
other language but let any other application use the objects' features without encountering the code reuse problem.
Visual Basic is the only tool for which Microsoft charges. VBCCE is free, and VBA and VBScript are included with other applications.
Object Hierarchies
The only confusing part in using an application's object is that it varies depending
on where you access and manipulate the object. If you use an embedded VBA application within the same host application that exposes the object, it's a simple process because you know that the object is available and the object reference itself is simpler. Using the same object from an external application-whether VB or VBA hosted in another application-requires that you first start the object's host application if it isn't already running and create a reference to it. This isn't very easy.
Such a ubiquitous application programming language has substantial benefits both for programmers and corporate development departments. The most obvious is that you can learn VBA once and use the same skills as you move among more than 100 commercial applications. All you have to do is understand the host application and learn its object model. This isn't always simple, but it's far easier and quicker than learning a new language. You'll also be able to use those objects from other applications, since by definition the
y are ActiveX objects.
Because VBA is being licensed for use in so many products, there is a wealth of resources for learning VBA programming and plenty of places to turn to for help. VBA developers have skills that are widely in demand. One benefit for users is that custom VBA applications tend to look and work like the host application, reducing the need for training.
Trade-0ffs
VBA runs only on Windows, although a Mac version is perennially rumored. Some of Microsoft's partners may port it to other platforms, but it will never be "write once, run everywhere"-Microsoft doesn't claim that as a goal.
No matter how you feel about Microsoft, widespread support will make all of us VBA programmers
whether we want to be or not. This ubiquity will make development easier and cheaper, and programmers will have marketable skills in almost any industry.
In Part 2, we'll take a look at dealing with VBA class hierarchies.
Read Part 2 of this story, "
License To Integrate
."
Don Kiely is director of technology for SkyFire Group, a developer of Windows and Internet applications in Fairbanks, Alaska. He can be reached at
donkiely@computer.org
.
art 1 of 2
The underlying language in Visual Basic and VBA is the same, as they share a common engine. The only substantial difference is that a VBA application can run only within the host application. You can't create separate executable files that run outside of the host application.
The screen shot at right shows the VBA and VB development environments side by side. T
he two environments could actually be rearranged to nearly identical layouts, but I like to remember which environment I'm working in, and I tend to use different features more frequently in each. VBA's Visual Basic Editor (VBE), included in any application that hosts VBA, runs in a window separate from the host application but uses the same memory space, so that you can view code and the application simultaneously. This is a big help when you're debugging.
One of the biggest benefits of using VBA in a host application is that you can use a macro recorder to generate basic code for your application. This amounts to the first widely available and usable code generator in computing. It's a great way to learn both the language and an application's object hierarchy, as well as to save time when starting application development. You can use the recorder to write the code for the basic procedure, then customize the code and add features or learn how to manipulate the host application's objects. You can't do this in any standalone development environment, including Visual Basic.
Since the programming language doesn't change as you move between VBA host applications, all you need to learn for each new host application you program is the underlying object hierarchy. VBA forces host applications to use the Microsoft style of object hierarchies-not a bad thing, if only for consistency's sake. This common approach to object hierarchies means you will always be working with the same types of object collections and relationships between objects. Spend some time learning what the various drawing objects in Visio do, for example, and you can manipulate them in code as you would objects in any other VBA host application.
If you're looking for the perfect application-development solution, keep looking. All VBA programs within a host application are interpreted, which means that they run slower than binary code does. Even though Java has made interpreted code acceptable, there is always a speed penalty.
Featured Microsite
