Steve Tranchida
Omicron Consulting
September 1999
Summary: This article presents simple steps for integrating Microsoft® Visual Basic® for Applications (VBA) into your line of business applications. Start by running the VBA Integration Wizard, a new Visual Basic® add-in that ships with VBA 6.0 SDK 6.1. Next, examine code the wizard generates to show you the key integration tasks. Finally, explore advanced VBA features that can help put corporate development back on the fast track. (7 printed pages)
Many corporations have difficulty maintaining application standards across departments and divisions. It is critical to build corporate systems that can communicate throughout an organization. To address this issue, companies may attempt to push development up to a central information technology (IT) group that can understand the full set of corporate applications. Unfortunately, the central IT group often lacks the business expertise to build such solutions. In addition, central IT can quickly become backlogged with feature requests coming from various departments.
Microsoft® Visual Basic® for Applications (VBA) can help corporations avoid these problems by offering a top-down development approach that helps decentralize the development process. The central IT department can concentrate on building core infrastructure with COM-based applications. Then, by integrating VBA into these applications, the system can be passed to department developers with business expertise. As Neil Charney explains in his article "Microsoft Visual Basic for Applications for Corporate Developers," this approach can go a long way towards reducing backlog in an IT group.
VBA runs in process with a hosting application, providing seamless integration of a powerful development environment. In addition to automating repetitive tasks and customizing an application, VBA can serve as the glue that ties business processes together.
This article will walk you through the simple steps required to integrate VBA into your line of business applications. We will start by running the VBA Integration Wizard, a new Visual Basic® add-in that ships with VBA 6.0 SDK 6.1. After running the wizard, your application will immediately demonstrate core VBA features, such as the ability to show the VBA integrated development environment. Next, we will walk through some of the code the wizard generates to give you an understanding of key integration tasks. We conclude with a brief mention of advanced VBA features that can help put corporate development back on the fast track.
With the release of VBA 6.0 SDK 6.1, Microsoft made the integration process a snap. A CD containing the full VBA SDK has been packaged with this issue of Visual Basic Programmer's Journal (VBPJ). After installing the SDK, Visual Basic developers will see a new item appear on the Add-Ins menu, labeled "VBA Integration Wizard." Simply load the project targeted for VBA integration, and then launch the wizard. See Figure 1 for the steps in using the wizard.
Figure 1. VBA Integration Wizard
At this point, your application should demonstrate some fundamental VBA features, such as the ability to show and hide the VBA IDE. This section will review the code the wizard has generated so you can build upon these core features. The first step is to understand the COM component that is being used for integration.
VBA integration is accomplished using Microsoft's Application Programmability Component (APC). The APC library is a COM component that greatly simplifies the integration process. See the diagram in Figure 2. APC wraps the raw VBA application programming interface (API) into a set of objects that represent the various pieces of VBA.
Figure 2. Application Programmability Component (APC)
In order to avoid scattering APC calls all over your code, the wizard creates a new class module in your application called APCIntegration. This class wraps most of the integration logic into a set of intuitively named functions. The APCIntegration class is created in your main form, along with your global object.
Public m_apcInt As APCIntegration
Private Sub Form_Load()
' global object
Dim appObj As CApplication
Set appObj = New CApplication
Set m_apcInt = New APCIntegration
m_apcInt.Initialize appObj, _ Me.hwnd
End Sub
Within the APCIntegration class, the top-level APC object is created in the Class_Initialize() method. This object is named Apc (lowercase p and c), and it is used to initialize VBA via some property settings.
Private WithEvents m_apcHost As MSAPC.Apc
Private Sub Class_Initialize()
Set m_apcHost = New Apc
End Sub
Public Sub Initialize(appObj As CApplication, hwnd As Long)
m_apcHost.hwnd = hwnd
m_apcHost.ApplicationObject = appObj
m_apcHost.HostName = App.Title
m_apcHost.LicenseKey = m_strKey;
m_apcHost.RegistryKey = "Common"
m_apcHost.Locale = 1033 ' English
m_apcHost.FileFilter = m_strFilter
End Sub
Once VBA is initialized, you can easily show and hide the VBA Integrated Development Environment by setting the Visible property of APC's Ide object.
Public Sub showVBE()
m_apcHost.Ide.Visible = True
End Sub
VBA uses projects to organize code modules, forms, and classes. A host application must implement the ability to create, save, and load projects in order to make VBA useful to the end user. Assuming that your application is file-based, the wizard generates most of the project support code for you. If your application is not file-based, then you can use APC to implement your own storage solution.
The technology underlying VBA project storage is compound files. Structured storage is an implementation of the compound file specification. Structured storage allows you to create a file system within a file by using objects called Storages and Streams. A storage object is conceptually similar to a directory, and a stream object is similar to a file within a directory. APC contains prebuilt objects called Storage and Stream that make using structured storage intuitive and simple. To obtain a storage or stream object, simply call ApcGlobal.CreateStorage or ApcGlobal.CreateStream.
The set of open VBA projects is managed through the APC Projects collection. To create a new VBA project, simply call Apc.Projects.Add and store the result in a variable of type MSAPC.Project. The VBA Integration Wizard places this code in the APCIntegration class module.
Private WithEvents m_ApcProject _
As MSAPC.Project
Set m_ApcProject = _
m_apcHost.Projects.Add(axProjectNormal, _
"VBAProject")
Saving projects can be accomplished using the Save, SaveAs, and SaveCopyAs methods of APC's Project object. The host application can determine whether a project needs to be saved by examining the Dirty property of the Project object. The APC ProjectItems collection can be used to enumerate through the forms, code modules, class modules, and references contained in a project.
In order to use the APC methods to save VBA projects, you first need to create an APC storage object by calling ApcGlobal.CreateStorage with a valid filename. Next, you pass this Storage object to the Project.SaveAs method. After a project has been saved once using SaveAs, the Project.Save method is used to commit subsequent changes.
To load existing VBA projects, you need to call a similar set of APC methods. First, pass a valid filename to ApcGlobal.OpenStorage to obtain a storage object. Then call Projects.Open, and pass the Storage object as a parameter.
Once your application provides the ability to create and manage projects, developers can begin building some powerful VBA solutions.
The discussion in this article thus far is only the beginning of what you can do with VBA. There are some very advanced features, such as the ability to create multithreaded VBA DLLs for use in a Microsoft Windows® DNA application. VBA's Multi-Instance Host Items allow central IT to build a core set of objects and have department developers dynamically add methods and properties to those objects using VBA. The new extended objects can be instantiated as many times as needed in your business logic.
The VBA SDK includes a comprehensive set of documentation and samples to help you understand these features and the overall integration process. You are encouraged to explore the CD, read the help files, and delve into the samples. You can expect to discover many ways that VBA can help your company.
To build a customizable line of business applications with VBA using the methods described in this article, you will need:
Steve Tranchida is a Systems Architect for Omicron Consulting in Philadelphia, PA. His primary responsibility is architecting distributed systems using the Windows DNA framework. In addition, Steve has worked on VBA integration projects for both ISV and corporate applications. Reach him through e-mail at stranchida@omicron.com.