Multithreaded Visual Basic for Applications

Duwamish Books, Phase 3.5: VBA

Ade Miller
Summit Software Company

December 1999

Summary: Provides an overview of the Microsoft® Visual Basic® for Applications (VBA) 6.0 multithreaded (MT) architecture and its application to Duwamish Books. (5 printed pages) Covers:

A brief overview of the multithreaded VBA architecture.
Creating an authoring environment for VBA MT Projects, the Duwamish Books Modifier.
Supporting the MT VBA Runtime on a thread, VBA enabling the BLL components.
Writing VBA code for MT Projects.

Introduction

Prior to VBA 6.0 it was only possible to support one instance of VBA running within a process. VBA 6.0 removes this restriction but takes a slightly different integration approach when supporting multithreading. This article contains an overview of the VBA 6.0 multithreaded architecture.

For further details of using the VBA MT features see the VBA 6.0 SDK.

VBA 6.0 ships with the Multithreaded Project Designer, a stand-alone project designer that allows VBA code to be written against a programmability or object model and published as an MT Project DLL. The VBA 6.0 SDK Application Programmability Component (APC) contains functionality for enabling MT Project support to an application hosting VBA. An additional component, the VBA 6.0 MT Runtime, allows these DLLs to be loaded and hooked up to the object model. Code within the DLL can then be executed using the Visual Basic 6.0 Runtime.

The MT VBA Architecture

When designing an application using multithreaded VBA 6.0 three different aspects must be considered. An MT VBA application must support these three elements, although not necessarily in the same executable:

A modifier client, which hosts VBA and provides support for publishing MT Project DLLs.
A host that supports threads running the VBA MT Runtime. Your application may manage its own pool of threads or use Microsoft Transaction Server (MTS) to host DLLs that in turn support the VBA MT Runtime.
The application must expose a programmability model to VBA. This must be available as a creatable component to the thread hosting the VBA MT Runtime and its type library must be available to the designer application.

Duwamish Books, Phase 3.5: VBA provides a designer application in the form of the Duwamish Books Modifier. The MT Project DLLs the Modifier creates are loaded into the Duwamish Books Business Logic Layer (BLL) MTS components. The programmability models exposed to VBA are expressed as separate Microsoft ActiveX® controls, allowing them to be used by both the Modifier (in type library form) and MTS components.

The Modifier Client

In order to create MT Project DLLs the application must provide an authoring environment—a VBA-enabled application that is aware of the MT Project types against which the VBA Programmer will write code. This could be the same application that runs the thread(s) hosting the VBA MT Runtime, or it could be a separate application. It is quite straightforward to implement the designer within a completely separate application and there are several possible reasons for doing this:

The thread running the MT Project might be in an environment that has limited access to the UI—an MTS component, for example.
Your application may be deployed in such a way that client users should not be able to modify the MT DLLs shipped with the product. An additional administration tool is required to do this.
You wish to minimize the footprint of the application that hosts the VBA-enabled threads by only supporting VBA on the threads rather than in the host process.

Duwamish Books, Phase 3.5: VBA takes this separate MT Project Designer approach, providing a completely separate application, the Modifier. It does so for all of the reasons just discussed. Such an approach allows Duwamish Books to limit the users who are able to modify the Business Logic Layer. Only the Duwamish Books administrator, ADMIN, is permitted to run the modifier. Duwamish Books also requires a separate Modifier application as the MT Project DLLs are loaded into MTS components, which have no user interface.

In order to create MT VBA DLLs a VBA host application must support one or more MT Project types. The VBA 6.0 SDK APC includes a method for notifying VBA of the MT Project types the host application supports. See the MT VBA documentation for details of the AddMTProjectType call required to notify APC that a particular MT Project is supported.

The user creates a new MT Project from the VBA integrated development environment (IDE) and writes code against the type library of a programmability object. The application object is a COM object, passed to AddMTProjectType, against which the VBA code in the MT project will make calls. The Multithreaded Designer then publishes this MT project as a DLL.

Notifying VBA that MT Projects are supported

The host application calls IApcHost::AddMTProjectType to inform VBA that it is supporting the MT Project Designer for a particular type of MT project. The host application should call AddMTProjectType before instantiating the APC host.

Multiple MT Project types can be supported within the same application, although that will lead to issues when the VBA programmer creates a new MT Project. If the application adds template code, there is no way for the application to query which type of MT Project the user created.

The Duwamish Books, Phase 3.5: VBA Modifier circumvents this problem by only having one active MT Project type at any one time. When the user switches between the Order and Buyer the APC host is shut down and restarted with the appropriate MT Project type.

Note This issue is fixed in the VBA 6.0 SDK version 6.2. Multiple MT Project types may be registered with APC and an active project type programmatically selected when creating new projects.

Publishing MT Projects

The Designer Project Name property of the MT Project is used to construct the ProgID of the published DLL. So, a project with the (default) name "MTProject1" will have a ProgID "MTProject1.Designer1." This can lead to conflicts if the VBA programmer publishes two projects with the same name for different MT VBA applications that use different application objects. One of the applications will end up trying to load an MT Project DLL for a different MT application. This causes the VBA MT Runtime to error.

Duwamish Books, Phase 3.5: VBA reimplements the MT Project persistence to provide the user with new menu items in both the application and VBA IDE. These programmatically modify the Project Name, and hence the ProgID, to be customization specific by automatically pre-pending a string to the ProgID.

See "Creating a VBA-Enabled Version of Duwamish Books" for further discussion on how the Modifier client publishes and on MT Projects.

Supporting MT Projects on a Thread

To execute the VBA code contained in the DLL, the thread running the VBA project creates an instance of the programmability model object; the VBA code will run against this. The thread then creates an instance of the VBA MT Runtime and initializes it, hooking it up to the programmability model object. Finally, the thread loads one or more Project DLLs and can run subroutines and functions or fire events into the MT VBA Project. MT DLLs can be selectively loaded so different DLLs can be used in different application scenarios—different types of users, for example.

MT VBA requires Single Threaded Apartments (STAs). Each instance of the MT Runtime must live in it's own STA; usually the VBA application object also lives in the same STA. Other configurations are possible, sharing an application object in a Multithreaded Apartment (MTA) between STAs, for example.

For the Duwamish BLL each instance of the BLL MTS component is loaded by MTS into a separate STA, where it can:

Create an instance of the programmability object.
Initialize the MT Runtime and pass the programmability object to it.
Load one or more MT Project DLLs.
Call macros or raise events on the MT DLL. The hosting thread can call public subroutines or functions in the Designer or events on the programmability object.

Calling a procedure or event

The VBA MT Runtime interfaces provide several mechanisms for calling procedures within the MT Project Designer Class:

IVbaMTDll::CallMacro() executes a particular macro in an MT DLL.
IVbaMTDll::get_DllObject returns the IDispatch interface

The Duwamish BLL uses events to execute code with the MT VBA Projects loaded on threads.

Note Not all code written in an MT Project is exposed directly to the hosting thread. Class Modules and Forms are exposed in the MT DLL type library but cannot be called directly. If you want to expose a VBA project's class modules and forms to the host thread the Designer Class must contain a method that returns a pointer to the class or form. The thread can then call this method to obtain the interface it requires and then call methods on the class. Code modules are not exposed in the MT Project DLL type library.

The "Multithreading Programmers Guide" in the VBA 6.0 SDK discusses this further.

The VBA Programmer and MT Projects

The VBA programmer creates a VBA MT Project and adds code to it that is then published as an MT DLL. This DLL is loaded onto a thread and code within it called using the IVbaMTDLL interface. This interface provides a mechanism for calling procedures within an MT DLL MT Designer Class. They are discussed in the section "Calling a procedure or event."

Designer Events

The MT Project Designer has four events fired in the following order:

Initialize—Fired during IVbaMt::LoadAllRegMTDlls.
OnConnection—Fired during IVbaMt::LoadAllRegMTDlls.
OnDisconnection—Fired during IVbaMTDlls::Release.
Terminate—Fired during IVbaMTDlls::Release.

Each DLL will see all four events during its lifetime. Why four? Designers are ostensibly class modules with some custom type information attached. The Initialize and Terminate events are fired by VBA and are essentially construction and destruction events on the module. The OnConnection and OnDisconnection take parameters specific to the MT Designer and are part of its custom type information. In most cases the Initialize and Terminate events are not useful to the VBA programmer and don't need to have handlers associated with them.

Connecting the Programmability Object

The application object exposes the programmability model to VBA. The application object is created on the thread and passed to the IMtVba::Init method and is made available to VBA as a parameter to the OnConnection event. The VBA code should make a copy of this in order to access the application object:

Option Explicit

'       Code to setup the application object
'
Dim WithEvents MyApplication As VbaOrderObjectModel.Application

Private Sub ApplicationInstance_OnConnection(ByVal Application As VbaOrderObjectModel.Application, _
                                             ByVal MTDllInst As Object, _
                                             Custom() As Variant)
    Set MyApplication = Application
End Sub

If this is not done and the VBA programmer creates a new application object:

Set MyApplication = New MtThread.ThreadApp

A new instance of the application object will be created—two copies will now exist, one in the VBA code and one as part of the hosting thread. Any additional work done by the hosting thread to set state within the application object passed into the OnConnection event will not be available in the new application object instance.

If your application object also exposes events to VBA it should be declared using WithEvents, as shown in the preceding Duwamish Books Order example code.

The Modifier client makes this as easy as possible for the VBA programmer by automatically adding boilerplate code to new projects. This code contains a declaration of an application object and an OnConnection event handler to make a local copy of the application object passed in by the event.

For more information on programmability model design see "Designing and Implementing a Programmability Model."