Managing Updates to Shared VBA Macro Code for Multiple Users

The steps in this technical note apply to both IBM and VT terminals, however the example code refers to an IBM terminal. The event names are slightly different for VT terminals.

Reflection VBA macro code is stored in Reflection settings files (.rd3x, .rd5x, .rdox), along with other necessary configuration details for each host session. If you provide a group of users with a settings file that includes custom macro code, then it later becomes necessary to update this macro code, you could simply provide updated settings files to everyone, however, any personal settings that have been saved by individual users would be lost.

To avoid the problem of losing personal settings, and to simplify administration of Reflection VBA macro code among groups of users, Microsoft Visual Basic for Applications Extensibility can be used to enable everyone to receive updates automatically on a regular basis; for example, each time a session is opened. Code can be added to the IbmTerminal.BeforeConnect event in VBA that will update the local VBA project with new code from a central location each time a settings file is opened.

How It Works
Create and test the VBA macro code that will be distributed to end users. Place all code that needs to be centrally managed in a single VBA module. Name the module something other than Module1. The example in this technical note uses SharedMacroCode.
2621_0.gif
When custom code for this module is complete, export it to a separate text file by clicking File > Export in the VBA Editor. Accept the default file name SharedMacroCode.bas.
View Full Size
2621_1.gif
Copy SharedMacroCode.bas to a central location accessible to all users who will need it, for example, Z:\remote\location\SharedMacroCode.bas.
Create a new settings file (.rd3x, .rd5x, .rdox) for distribution to end users that will include the VBA Extensibility code necessary to dynamically import this remotely located macro code. Open the Visual Basic Editor, click Tools > References, and select the Microsoft Visual Basic for Applications Extensibility check box:
View Full Size
2621_3.gif
In the Project pane of the Visual Basic Editor, under Reflection Objects, double-click ThisIbmTerminal (or ThisTerminal for VT sessions) to open the associated module for the Terminal object.
Select the IbmTerminal object from the drop-down object list above the Editor pane, then select its BeforeConnect event from the drop-down event list. Visual Basic will automatically add the stub of an event-handler subroutine, as shown:
View Full Size
2621_4.gif
Add code to the event-handler subroutine created in step 6 to import code from SharedMacroCode.bas into the local VBA project. Follow this example:

Private Sub IbmTerminal_BeforeConnect(ByVal sender As Variant)
'handle errors in-line...
On Error Resume Next

'include reference to "Microsoft Visual Basic for Applications Extensibility"
Dim vbproj As VBProject
Dim vbc As VBComponent
Set vbproj = ThisIbmTerminal.VBProject

'Error will occur if component with this name is not in the project
Set vbc = vbproj.VBComponents.Item("SharedMacroCode")
If Err.Number <> 0 Then
    Err.Clear
    'so add it...
    vbproj.VBComponents.Import "Z:\remote\location\SharedMacroCode.bas"
    If Err.Number <> 0 Then
       MsgBox "Could not import new VBA code: Z:\remote\location\SharedMacroCode.bas", , _
        "IbmTerminal_BeforeConnect event"
    End If
Else
    'no error - vbc should be valid object
    'remove existing version first before adding new version
    vbproj.VBComponents.Remove vbc
    vbproj.VBComponents.Import "Z:\remote\location\SharedMacroCode.bas"
    If Err.Number <> 0 Then
        MsgBox "Could not update VBA code from Z:\remote\location\SharedMacroCode.bas", , _
        "IbmTerminal_BeforeConnect"
    End If
End If    

End Sub

The settings file containing this code can now be distributed to end users, and each time it is opened, this BeforeConnect event will automatically update the local VBA project’s SharedMacroCode module with a new version retrieved from Z:\remote\location\SharedMacroCode.bas.
Note the following:
All users who need to access dynamically updated VBA code in this manner will need to have the same drive mapped to the same folder, as in the example, Z:\remote\location....
Exported modules from VBA (.bas files) are plain text. You can make edits to the file without re-importing it into Visual Basic.
Event names are slightly different for VT terminals than the IBM terminal example in this technical note. For VT sessions, the “Terminal_Connected” event is recommended.
The example shows how to dynamically import a single standard module into a VBA project, however, it is also possible to import user forms and VBA classes as well. For more information about working with the VBA Extensibility library, search the Microsoft Visual Basic Reference Help index for "VBProject object."