Managing Updates to Shared VBA Macro Code for Multiple Users

This technical note describes how to use Microsoft Visual Basic for Applications Extensibility to update Reflection VBA code that is shared by a group of users.

Note: 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

1. 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.

2. 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

3. Copy SharedMacroCode.bas to a central location accessible to all users who will need it, for example, Z:\remote\location\SharedMacroCode.bas.
4. 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

5. 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.
6. 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

7. 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:

8. 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."

Legacy KB ID