Component Management Services focusing on Excel VB-Projects

• Exports any Component the code has changed along with each Workbook save.
• Updates any outdated Used/Hosted Common Component).
• Synchronizes the VB-Project of two Workbooks.

All services only require one component installed/imported plus a single code line for each service, still guaranteeing that a productive Workbook is not bothered by any of the configured services at all.

Disambiguation

Term	Meaning
Component	Generic term for a VB-Project's VBComponent (Class Module, Data Module, Standard Module, or UserForm)
Common Component	A Component providing services for a certain subject, dedicated for being used in any VB-Project
Procedure	Generic term for any VB-Component's (public or private) Property, Sub, or Function
Service	Generic term for any Public Property, Public Sub, or Public Function of a Component
Servicing Workbook	The service providing Workbook, either the CompMan.xlsb Workbook (when it is open) or the CompMan Add-in when it is set up and open.
Serviced Workbook	The Workbook prepared for being serviced.
VB‑Project	Used synonymous with Workbook
Sync‑Source‑Workbook	A Workbook/VP-Project temporarily copied to the CompMan's serviced root folder_ for being modified - and finally synchronized with its origin Workbook.
Sync‑Target‑Workbook	A productive Workbook/VP-Project temporarily moved to the configured Serviced Synchronization Target Folder for being synchronized with its corresponding Sync-Source-Workbook when opened.
Workbook parent folder	A folder dedicated to a Workbook/VB-Project. Note that an enabled Workbook is only serviced when it is exclusive in its parent folder. Other Workbooks may be located in sub-folders however.

Services

Export Changed Components

Used with the Workbook_BeforeSave event. Exports all VB-Components of which the code has changed (i.e. differs from the recent export's Export-File), to the configured Export Folder of which the name defaults to source. These Export Files not only function as a code backup in case Excel ends up with a destroyed VB-Project, which may happen every now and then - but only functions as a versioning means (e.g. when GitHub is used for instance). When a used or hosted Common Component has been modified and exported, a Pending Release is registered See also which are handled specifically.

Update Outdated Common Components

Used with the Workbook_Open event all Used Common Components are checked whether they are outdated. In case, a dialog is displayed which allows to display the code difference (by means of (WinMerge English, WinMerge German, etc.) perform the update or skip it. The update uses the Export File of the Raw Common Component in the Common Components folder.

Synchronize VB-Project

The service allows a productive Workbook to remain in use while the VB-Project is developed, modified, maintained, etc., in a copy of it. When all changes had been done the VB-Project of the productive Workbook is synchronized. The benefit: A significant shorter downtime for the productive Workbook.

As with the Export and Update service, this service has no user interface! The service is invoked when the Sync-Target-Workbook (i.e. the temporarily moved productive Workbook) is opened from within the configured _ Serviced Synchronization Target Folder_. Provided the Sync-Source-Workbook (i.e the copy of the productive Workbook) resides in the CompManServiced folder.

Installation

Provision of CompMan as a servicing Workbook instance

When CompMan.xlsb is downloaded to whichever location and opened it will setup its default files and folder structure at the download location (don't worry, it may be moved afterwards). The setup completes with saving the Workbook to its dedicated parent folder and the downloaded Workbook is removed. The setup environment, i.e. the CompManServiced folder may subsequently be moved to any location and even renamed.

1. Download and open the CompMan.xlsb Workbook

2. When opened an explicit activation of the macros will be required, except when macros are unconditionally enabled - though not recommended by Microsoft

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 it installed the message will be re-displayed whenever the CompMan.xlsb Workbook is opened.

4. Confirm CompMan's self default environment setup at the location the Workbook is opened (see below).

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

Usage

Enabling the services (serviced or not serviced)

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 Workbook has one or more of the below services enabled (see below)
3. The to-be-serviced Workbook is opened from within a sub-folder of the configured CompManServiced folder, Note: In case of the Synchronization service from within a sub-folder of the configured Sync-Target-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

Note: As a consequence from the above, a productive Workbook must not be used from within the configured CompManServiced folder. When a Workbook with any enabled/prepared service is opened when located elsewhere the user will not be bothered by any means, i.e. will not even recognize CompMan at all - even when open/available.

Note: Even when a Workbook has one or more services enabled, the service is denied without notice when the above (pre)conditions are not met.

Enabling the Export service

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

1. 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. Into the Workbook module copy the following:

Private Const HOSTED_RAWS = vbNullstring
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    mCompManClient.CompManService mCompManClient.SRVC_EXPORT_CHANGED, HOSTED_RAWS
End Sub

3. See Common Components in case the Workbook hosts one/some

Enabling the Update service

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

1. Make sure CompMan is provided and open
2. From the Common-Components folder import the mCompManClient.bas (available after CompMan has been setup) which serves as the interface to the CompMan services.
3. Into the Workbook module copy the following:

Private Const HOSTED_RAWS = vbNullstring
Private Sub Workbook_Open()
    mCompManClient.CompManService mCompManClient.SRVC_UPDATE_OUTDATED, HOSTED_RAWS
End Sub

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

3. In case the Workbook hosts one or more [Common Components](#common-components] copy into the Workbook module:
   Private Const HOSTED_RAWS = <component-name>[,<component-name]...

This will only be the case when a Common Component (in the sense of CompMan) is hosted, i.e. developed and maintained in a - preferably dedicated - Workbook. However, any Workbook may declare one of its components as a hosted Common Component (watch-out conflicts!). When declared and modified the Export-File will be copied to the Common Components Folder and the Revision Number will be increased.

Enabling the Synchronization service

The Synchronize VB-Project service is performed when the Workbook is opened from within the configured Serviced Synchronization Target Folder and all the preconditions are meet

1. Make sure CompMan is provided and open
2. From the Common Components folder import the mCompManClient.bas which serves as the interface for the CompMan services.
3. In the Workbook module copy the following:

Private Sub Workbook_Open()
    '~~ The statement is only required for a Workbook which may be synchronized.
    '~~ However, the statement has no effect unless the Workbook is opened from within the configured 'Serviced Synchronization Target Folder'.
    mCompManClient.CompManService mCompManClient.SRVC_SYNCHRONIZE
End Sub

Using the Synchronization Service

Steps

1. Follow the Installation instructions and the Enabling the Synchronization service instructions.
2. Copy the productive Workbook into a dedicated folder under the configured CompManServiced folder folder
3. Modify the VB-Project as intended while the productive Workbook remains in use!.
4. When the development/modification has been finished, close the Workbook! and proceed with the next steps.
5. Move the productive Workbook to the configured Serviced Synchronization Target Folder and open it. In case this folder has yet not been configured, switch to the open CompMan.xlsb and use the displayed Config Worksheet.
6. Open the moved Workbook and follow the synchronization steps. The synchronization will be done on a working copy (name with a suffix Synced).
7. When the synchronization has finished, save the working copy as the new productive Workbook - e.g. by dropping the Synced suffix from the name and moving it back to the "production location" it originates.
8. When everything has finally turned out perfect the remaining Workbook from step 3 may be removed

Worksheet synchronization

• New Worksheets
  • The Sync-Source-Worksheet is cloned to the Sync-Target-Workbook
  • Back-links to the Sync-Source-Workbook are eliminated
  • All concerned Names scope is synchronized
• Obsolete Worksheets are removed
• Worksheets' Name or! Code-Name change are synchronized
• Not yet implemented: Worksheets owned by the VB-Project, that means those protected and without any unlocked (input) cell, are synchronized by default - disregarding any change.

Attention! The Name and the CodeName of a Worksheet must never both be changed. When a Worksheet's Name and its CodeName is changed at the same time the concerned sheet will be considered new and the (no longer identifiable as such) corresponding sheet will be considered obsolete - which in such a case is definitely not what was intended.

References synchronization

New References are added and obsolete References are removed.

VB-Components synchronization

Synchronized are all types of VB-Components: (Standard Module, Data Module, Class Module, UserForm). New components are added, obsolete components are removed, and of changed components the code is updated.

Names synchronization

• In order to provide a full transparent synchronization of Names they are synchronized ++before++ the Worksheets are synchronized ¹ - though this makes it a bit more complex. When a Worksheet's name is about to change, the Names synchronization has to deal with an old-named Worksheet while the source Name refers already the new Worksheet name.
• When the referred range of a Name has changed this is not synchronized. Instead this issue may be skipped or used to interrupt the sync in order to synchronize the change in the source Worksheet's layout in the corresponding target Worksheet manually. In case the 'change' is caused by the fact that a new row/column has been inserted by the Workbook user while the VB-Project has been maintained this issue can just be ignored, i.e. skipped.
• For a best possible transparent process, multiply named ranges are handled separated by removing all target- and adding all corresponding source-Names.
• Synchronization of obsolete Names: A Name is regarded obsolete when it only exists in the Sync-Target-Workbook but not (no longer) in the Sync-Source-Workbook
• Synchronization of new Names: A Name is considered new when it (the Name's 'mere name' ²) exists in the Sync-Source-Workbook but not in the Sync-Target-Workbook.
• Synchronization of changed Names and/or Scopes: A ranges Name and/or its Scope may bee changed in the Sync-Source-Workbook and will accordingly synchronized in the Sync-Target-Workbook.
• Manual pre-synchronization preparation: When a synchronization is intentionally terminated (interrupted respectively) this will only be done in order to manually synchronize a design-change. To support this, the Sync-Source-Workbook and the Sync-Target-Workbook's working copy will be closed and the Sync-Source-Workbook re-opened. In the open dialog "manual pre-synchronization" will be chosen and once done the Workbook closed and re-opened with the option "continue with the ongoing synchronization".

Sheet-Shape synchronization

Still under construction! New Shapes (including ActiveX-Controls) are added, obsolete Shapes are removed. The Properties of all Shapes are synchronized. However, though largely covered the properties synchronization may still be incomplete.

Other

Status of the CompMan Add-in

Status	Meaning
configured	A valid, existing Add-in folder is specified. CompMan's Add-in instance may be setup/renewed
not configured	No Add-in folder is specified. CompMan's Add-in instance cannot be setup/renewed (the button is not visible)
setup	CompMan's Add-in instance is setup, i.e. available in the configured Add-in folder
not setup	CompMan's Add-in instance is not setup, i.e. not available in the configured Add-in folder.
paused	CompMan's Add-in instance is indicated 'currently paused'. I.e. even when open it will programmatic-ally be ignored
open	CompMan's Add-in instance is open (it may be paused however)
not open	CompMan's Add-in instance is not open. It will be opened when setup/renew and when Excel is started because the setup/renew establishes auto-open.
auto-open setup	With setup/renew the auto-open has implicitly setup/established.
auto‑open not setup	When the Add-in folder is de-configured (no folder is selected when with 'Configure') a setup auto-open is removed

Common Components

Common Components are considered a key to productivity and performance of VB-Project development - provided:

• well designed and carefully tested
• up-to-date in VB-Projects using them

One of CompMan's aims is to specifically support Common Components in recognition and management in order to keep them up-to-date in VB-Projects using them. For an optimum support CompMan distinguishes between hosted and used Common Components, whereby hosted remains an optional concept when using CompMan.

Pending Release Management

When a used or hosted Common Component's code is modified and exported the component is registered as Pending Release, which is the release of the modification pending to become publicly available in the Common-Components folder. Any Pending Release component is available in the Add-Ins menu from where the modification can be release to public one by one once the modification had become final.

The concept of "hosted" Common Components

Experience has shown than only a dedicated Workbook/VB-Project is appropriate for the development and especially the testing of a Common Component. It is required for the provision of a comprehensive test environment which also supports regression testing. CompMan supports this concept by allowing to specify a Common Component as being hosted in a Workbook. However, practice has shown that a modification or amendment of a Common Component is often triggered by a VB-Project just using, i.e. not hosting, it. CompMan therefore supports this by keeping a record of which Workbook/VB-Project has last modified it.

The services

CompMan's initial intention was to keep Common&nbspComponents 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 keeping a record of the modifying Workbook together with an incremented Revision Number. Subsequently the Update-Outdated-Common-Components service (by with the Workbook_Open event) checks for any outdated used or hosted Common Components and offers an update in a dedicated dialog which allows to check the code difference by means of WinMerge (WinMerge English, WinMerge German.

The Revision Number

CompMan maintains for_Common Components a Revision Number, increased whenever it is modified. The Revision Number is maintained in a file CompMan.dat located in the serviced Workbook's parent folder and kept in sync with the Revision Number in a file ComComps.dat located in the Common Components folder.

Other CompMan specific files

File	Location	Description
CompMan.dat	The serviced Workbook's parent folder	PrivateProfile file for the registration of all Hosted Common Components and all Used Common Components.
CompMan.Service.trc	The serviced Workbook's parent folder	Execution trace of the performed CompMan service, available only when the VB-Project's Conditional Compile Argument mTrc = 1 (mTrc is installed/used) or clsTrc = 1 (clsTrc is installed/used) is set.
CompMan.Service.log	The serviced Workbook's parent folder	Log file for the executed CompMan services.
CommComp.dat	The Common-Components folder	A PrivateProfile file with sections representing Common Components with various information like the hosting Workbook and the Revision-Number for instance.

Multiple computers involved in VB-Project's development/maintenance

When the Common-Components folder is handled/managed as a GitHub repository it will be easy to keep an up-to-date clone on various computers. Currently the location of the Common-Components folder is fixed and cannot be re-configured/located on a network. However, the whole environment, i.e. the CompManServiced folder folder may be moved to/kept at any location.

CompMan.xlsb versus CompMan as Add-in

All services are provided by an open CompMan.xlsb Workbook even when it is additionally setup as Addin. The Addin only provides the services when the CompMan.xlsb Workbook is not open. When the Addin is paused and the CompMan.xlsb Workbook is not open no services are provided until the CompMan.xlsb Workbook is open again and the Addin is continued. The Workbook may be closed then. The advantage of the Addin is that it remains (almost) invisible. That's all.

While any Workbook can use the services either form an open CompMan.xlsb Workbook ++or++ from the Addin, the CompMan.xlsb Workbook itself requires the Addin to update its own outdated Used Common Components.

CompMan's default files and folders environment

CompManServiced
  +---CompMan
  |    +--Addin
  |    +--source
  |    +--CompMan.xlsb
  |    +--WinMerge.ini
  | 
  +---Common-Components
       +--CompManClient.bas

File/Folder Name	Meaning and usage
CompManServiced	Default root folder serviced by CompMan.. The folder may be moved and/or renamed. When the CompMan.xlsb Workbook is opened it recognizes the parent of its parent folder as the serviced root folder and keeps a record of it in the CompMan.cfg file.
CompMan	Default parent folder of the CompMan.xlsb Workbook. The name preferably defaults to the Workbook's base name.
Addin	Folder for the CompMan.xlsb Workbook when configured as Addin (CompMan.xlsa). The folder name however must not be altered!
source	Default folder name for the Export-Files of changed components exported with each Save event. This folder is maintained for each serviced Workbook in the Workbook's dedicated parent folder. The folder name may be changed by means of the Config Worksheet.
CompMan.cfg	PrivateProfile file which keeps the current CompMan configuration. It is used with each open and adjusted on the fly if required. The file ensures that each subsequent download of the ComMan.xlsb Workbook works with the configuration saved with the last close of it.
CompMan.xlsb	The Workbook file originally opened copied (saved as) into its dedicated parent folder along with the initial setup. Once the opened Workbook has been saved to the new setup location it is deleted.
WinMerge.ini	File used by CompMan to display code changes by means of WinMerge with the options to ignore empty code lines and ignore case differences.
Common‑Components	Default folder where CompMan maintains a copy of the Export-File of hosted Common Component. These Export Files function as the source for a serviced Workbook's (possibly) outdated Used Common Components. Though primarily maintained by CompMan, the folder may contain any Export File of a VBComponent considered Common. Manually added Export-Files are regarded Common Component orphans until a Workbook claims it Hosted
CompManClient.bas	Export file of the Common Component hosted by CompMan.xlsb for being imported into any to-be-serviced Workbook. See Enabling the services.

6. The CompMan.xlsb Workbook requires two Macro Security Settings:
   1. Trust Center > Trust Center Settings > Macro Settingsin: "Deactivate all macros except this signed" and "Trust the access to the VBAProject Object model"
7. Providing the code in the IDE with a (SelfCert.exe) signature will be the perfect fit with the above setting

Configuration changes (CompMan's Config Worksheet)

Item, means	Meaning, usage
CompMan's serviced root folder	May be moved to any other location and/or renamed when the Workbook is closed. When the CompMan.xlsb Workbook is opened again it by default regards the parent folder of the parent folder as the serviced root folder. I.e. any Workbook in a subsequent folder will be serviced provided it is enabled.
Export folder	Name of the folder established and used in any serviced Workbook's parent folder to store the Export-Files of changed (or yet not exported) VBComponents.
Serviced Sync‑Target‑Folder	Folder into which a Workbook (for which the Synchronize VB-Project has been enabled is temporarily moved and opened in order to have its VB-Project synchronized with the corresponding (same named) Workbook residing in its dedicated folder within the configured CompManServiced folder. When the configuration of the Serviced Synchronization Target Folder is terminated, i.e. no folder is selected, the Serviced Synchronization Target Folder becomes 'not configured'.
SyncArchive folder	Folder obligatory for the Synchronize VB-Project service which archives a Sync-Target-Workbook before it is synchronized with its corresponding Sync-Source-Workbook. When the Synchronization Archive Folder selection dialog is terminated, i.e. no folder is selected, the Synchronization Archive Folder becomes 'not configured'.
CompMan‑Workbook status	CompMan's current status which may be changed by the Setup Auto-open/Remove Auto-open Command Button
CompMan Addin status	Status provided by the Provide Add-in/Give up Add-in Command Button.
Setup Auto‑open Remove Auto‑open	Sets up or reomes the Auto-Open for the CompMan.xlsb.
Pause Add‑in Continue Add‑in	Command Button to temporarily pause and subsequently continue the setup Add-in.
Give up Add-in Provide Add-in	Provide Add-in establishes the CompMan.xlsb as Add-in automatically opened when Excel starts. Give up Add-in removes the Addin (even when it is currently open, which requires a couple of tricks).

Download from public GitHub repo

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

Contribution

Contribution of any kind is welcome, raising issues specifically.

Footnotes

1. When during the Worksheet synchronization a new sheet is cloned all Names are cloned too which obstructs a transparent Names synchronization. ↩

2. A Name objects 'mere name' is one without a sheet-name-prefix ↩