Skip to content

Latest commit

 

History

History
96 lines (77 loc) · 7.96 KB

README.md

File metadata and controls

96 lines (77 loc) · 7.96 KB

Component Management Services for Excel VB-Projects

  • Exports any Component the code has changed along with each Workbook save.
  • Updates any outdated Common Component.

Enabling a Workbook/VBProject for being serviced by CompMan

Services are provided with an absolute minimum intervention in the serviced Workbook:

  1. Download mCompManClient.bas from GitHub which is the Standard Module to be imported as interface to the services provided by the CompMan.xlsb Workbook
  2. One code line in the Workbook_Open event procedure for the update service
  3. One code line in the WorkBook_After_Save event procedure initiates the service provided the required conditions are meet (see Serviced or not serviced Workbooks).> The services only require one component installed/imported, a single code line for each service, preventing that productive Workbooks are bothered by a configured service (see serviced or not serviced).

The services

At a glance

CompMan's services first and foremost intend to keep Common Components up-to-date in all VB-Projects using them. To achieve this the Export Service saves the Export-File of a modified used or hosted Common Component to a Common-Components folder thereby maintaining properties about the origin. Subsequently the Update outdated Common Components service (with the Workbook_Open event) checks for any outdated used or hosted Common Component and offers an update in a dedicated dialog which allows to check the code difference by means of WinMerge (WinMerge English, WinMerge German.

Export Changed Components service

Performed by the Workbook_AfterSave event. Exports all VB-Components of which the code differs from the recent export's Export-File to the configured Export Folder.

The Export Files not only function as a code backup in case Excel ends leaving a destroyed/unreadable VB-Project behind. In combination with a synchronization service (e.g. sync.com) it also substantially supports versioning.

Update outdated Common Components service

Performed by the Workbook_Open event used or hosted Common Component outdated are proposed to be updated. The dialog supports a code difference displayed (by means of (WinMerge English, WinMerge German, etc.) and allows to update, or skip the update. The update uses the Export File in the Common-Components folder.

The Update service is performed only when the Workbook is opened from within the configured CompManServiced folder and only when all the preconditions) are meet.

Providing CompMan as a Workbook/VBProject servicing instance

  1. Download the CompMan.xlsb Workbook
  2. Move the downloaded Workbook into a folder you will regard as the serviced root folder and open the Workbook.1
  3. When WinMerge is not available/installed a corresponding message is displayed. The provided link may be used to download and install it. When continued without having installed it the message will be re-displayed whenever the CompMan.xlsb Workbook is opened.
  4. Confirm CompMan's self-setup. It will setup the default files and folder environment.

CompMan's services are now ready for being used by Workbooks which have the service(s) enabled (see below).

Usage

A Workbook will only be serviced by CompMan provided:

  1. A servicing CompMan instance (see how to provide) is open
  2. The to-be-serviced Workbook has the services enabled (see below)
  3. The to-be-serviced Workbook is opened from within a sub-folder of the configured CompManServiced folder.
  4. The to-be-serviced Workbook is the only Workbook in its parent folder (the parent folder may have sub-folders with Workbooks however)
  5. WinMerge (WinMerge English, WinMerge German or any other language version is installed to display the difference for any components when about to be updated by the Update service

As a consequence from the above it may not be appropriate to use a Workbook productively from within the CompManServiced folder but have it copied elsewhere outside for using it. The Workbook may be copied back at any time for developing/maintaining its VBProject. By the way: When a Workbook is opened outside the CompManServiced folder the execution of enabled/prepared services will be denied without notice.

Enabling the Export service

The Export service is performed whenever the Workbook is saved from within the configured _CompMan serviced root folder and all the preconditions are met.

  1. Install: From the Common Components folder import the mCompManClient.bas (available after CompMan has been setup) which serves as the link to the CompMan services.
  2. Prepare: Into the Workbook module copy the following:
Option Explicit
Private Const COMMON_COMPONENTS_HOSTED = vbNullstring

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   mCompManClient.CompManService mCompManClient.SRVC_EXPORT_CHANGED, COMMON_COMPONENTS_HOSTED
End Sub

Enabling the Update service

  1. Install: From the Common Components folder import the mCompManClient.bas (available after CompMan has been setup) which serves as the link to the CompMan services
  2. Prepare: Into the Workbook module copy the following:
Option Explicit
Private Const COMMON_COMPONENTS_HOSTED = vbNullstring

Private Sub Workbook_Open()
    mCompManClient.CompManService mCompManClient.SRVC_UPDATE_OUTDATED, COMMON_COMPONENTS_HOSTED
End Sub

Despite the import of the mCompManClient this is the only required modification in a VB-Project for this service.

  1. Hosted Common Components: In case the Workbook hosts one or more Common Components, copy into the Workbook module:
Private Const COMMON_COMPONENTS_HOSTED = <component-name>[,<component-name]...

Other

Download from public GitHub repo

It may appear a bit strange when downloading first from a public GitHub repo but its quite straight forward as the below image shows.

Contribution

Contribution of any kind is welcome, raising issues specifically.

Footnotes

  1. When opened, an explicit activation of the macros may be required. My proposal is to sign the VBProject and adjust the Macro security correspondingly (unconditional enabling is not recommended by Microsoft) ↩