Managing Updates to Shared VBA Macro Code for Multiple Users

  • 7021399
  • 12-Apr-2012
  • 24-Aug-2018

Environment

Reflection Desktop
Reflection 2014
Reflection for IBM 2014
Reflection for UNIX and OpenVMS 2014
Reflection for IBM 2011 R2 or higher
Reflection for UNIX and OpenVMS 2011 R2 or higher
Reflection Standard Suite 2011 R2 or higher

Situation

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.

Resolution

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

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

Additional Information

Legacy KB ID

This article was originally published as Attachmate Technical Note 2621.

Feedback service temporarily unavailable. For content questions or problems, please contact Support.