Prevent VBA from exporting Forms that have not changed

[gkuenzli]

Description

A well known problem, already discussed in original hilkoc#5, hence not solved so far.

Every time a Form (alias UserForm or MSForm) get exported, both the code (.frm) and the OLE ActiveX binary data (.frx) files do change even if the neither got edited in any way. The code file (.frm) because an extra new line is inserted between the VBAttribute header section and the actual code. The OLE ActiveX file (.frx) for some obscure undocumented legacy reason.

Other requirement

The binary nature of the OLE ActiveX frx file does not allow to track actual changes in the design of a UserForm with git. We need human-readable data.

Proposed solution

Instead of 2 files resulting from a Form export, let the system produce 3. The third file (.frd) is going to be a textual description of the Form design data (the textual counterpart of the binary OLE ActiveX data), JSON formatted.

During export, the potential frd stream based on current data is compared to the persisted version. If a change is detected, the frx and frd files are actually exported. Same process for code, that is actually exported only if a change is detected vs the stored frm version.

Implementation & Shortcomings

For sake of simplicity and efficiency, the solution will rely on Tim Hall's VBA-JSON for the export of the frd file.

Since reflexivity does not exist in VBA, enumerating the properties of the Form controls is no straightforward thing. In some Office Applications, like Access, a Properties collection does exist for Form controls. But alas, Excel does not expose such thing. As a trade off, we can hard code the export of properties for every of the 16 standard MSForms controls (plus 1 for the highly controversial Excel RefEdit control).

For any externally-referenced ActiveX control, however, this approach won't work, unless some manual hard coding is appended to the project. Far, far from perfect!

Anyway, since I need some pragmatic solution right now, I go this way, hoping that someone ends up later with a brighter idea.

Pictures in controls and the form are a topic by itself that remains unsolved so far. A future release might export the images as actual files and/or as additional JSON data in the frd file.

[MartinRoller]

I had a related problem and saw your idea (thank you). I think I have a generic solution, based on type lib. I don't think I have the time to adopt it to your project, but drop me a line and I send it to you privately, if you're interested.
[email protected]