Forms always appear to have changed in Git

[NealHumphrey]

In the export process, Forms are exported as binary files (.frx). Since vbaDeveloper re-exports and overwrites and because of something in the file creation process(maybe a date stamp or some similar tag??), when putting the exported VBA form into version control it appears to have changed in every commit, regardless of whether the user actually made changes. This can lead to confusion in version control.

I see 3 choices:

1. Ignore, and adapt my Git workflow (i.e. just don't commit the unchanged files). This is slightly more manual, and also a slight training burden for my not-very-git-savy teammates.

2. Add a function to the vbaDeveloper to prompt whether to export forms and any other offending file types. Don't prompt if no offending file types would be exported.

3. Hunt down what the reason for the differing binary files and see if it is possible to exclude that component from the export.

Anyone else have opinions, or have a need for option 2 or 3 if I were to develop it? Anyone (particularly the project owner) opposed to option 2?

[gberl001]

I noticed this as well, my thought was to put a date stamp comment in the code. To elaborate, before it's exported, modify the first line in the code to have the timestamp of when it was last exported, kind of like .bas files.
With modules, when they are exported they have a line "Attribute VB_Name = "Module_Name"" which I know is actually removed once it's imported so it is never really seen by the developer but it could work.

Anyway, the flow would be...

1. Export function triggered
2. The program checks the date in the first line of the code (projectCodeDate)

• If (projectCodeDate = Null) then
  • export continues (adding a date)
• If (projectCodeDate > fileLastModifiedDate) then
  • export continues (updating the date)
• ElseIf (projectCodeDate < fileLastModifiedDate) then
  • MsgBox("Woah... "merge" conflict...") -- ask for next action (skip/continue)
  • export skips file
• Else
  • export skips file

In all honesty, I was thinking about doing this with all files, I'm afraid that I'm going to edit something in the exported file, forget, and then overwrite it by exporting from the add in. There needs to be some sort of QA check here (in my opinion).

Thoughts?

[gberl001]

Another option, while very involved, would be to modify the entire exporting function to, rather than use the official VBA module export function, use a file stream. This would also resolve the issue with class files because, apparently class files cannot just be imported via macros. I read a solution (that I also mentioned in the class files issue) in which the user stated that class files but be created via file input streams. That is, creating a new class object and then reading in the text.

Obviously this would be a huge overhaul but, if it's the most error free and accurate way... Just throwing it out there. This solution would also allow you to put the Time-stamp in the first line without the developer ever needing to see it in the VBA Project IDE just like the attribute line I mentioned in a previous comment.

[NealHumphrey]

gberl001,

I'm not completely certain I understand where those two date stamps you're referring to are coming from. My assumption is:
-projectCodeDate is the whole VBA project (not tied to a specific Form)
-fileLastModifiedDate is the "date tag" you're adding to the first line of the file

If I understand correctly, I'm not sure these date stamps will get what we want. If projectCodeDate is scoped on the project level, a change in another part of the code (a module for instance) would trigger an export even if the individual Form wasn't updated. For your issue of editing something in the exported file, this also won't resolve that unless you remember to manually change that date stamp (which would be error prone). Lastly, the .frx files are binary (not the .frm files, which are the code attached to the form, .frx is the form itself), so I'm not sure how to put the date stamp in there to begin with.

I don't know enough about the file stream method to know if that's a viable option. Would that create identical FRX binary files regardless of when they are exported? It would be great to fix the class file import issue, although export will be far more common than import for my workflow at least.

[gberl001]

Okay, I'll try to clarify

The add in would (when exporting any object) add a date (exported date) to each object's code (in the first line). When running export again, the add in would check the date in the first line of the code to see if it matches the file's last modified date.

The date in the image above would be compared to the file's last modified date

In the above case, this would export fine because the form in the Excel file has a date newer than the file on the network. However, I realize this is flawed now because the date in the Excel file would need to be changed every time that form module was changed and only when it was changed and there is no way (that I know of) to do that. An alternative to the date in the first line of each module would be to keep a text file with the module names and dates so it can't be manually altered but the point is moot now since I realized the flaw that I think you were mentioning about needing to manually update.
This would solve my problem (not wanting to overwrite a newer exported file since the date in the code is only changed on export, the date in the code is essentially the "last exported" date. I'll start a new comment to discuss the other concern

[NealHumphrey]

Yeah, that's what I meant by project modified date being scoped to the
whole VBA project. Regardless, this still doesn't address the issue that is
with FRX files (not FRM files - which work fine, the same as .bas files):

This shows up in the commit (unless I manually exclude the file) even when
I have not modified the form itself:
[image: Inline image 1]
Since it's binary, you can't add a date stamp.

I didn't realize you were concerned with overwriting a newer export - I
don't think that situation would ever come up in my workflow. If you
manually updated the VBA outside of Excel that could happen, but again
you'd need to remember to manually change the date stamp.

[gberl001]

If you
manually updated the VBA outside of Excel that could happen, but again
you'd need to remember to manually change the date stamp.
NealHumphrey

Not necessarily, VBA is reading the "Last Modified" date of the file which changes as soon as you hit save after editing it externally from Excel. It only looks at the date inside the code file when it's in the project (so it would still work for binary files). See Example flow chart

However, this still has the issue of, if you don't manually update the date in the code (in Excel) then the add in is going to assume you didn't make any changes because it will match the file date.

Well, I'm out of ideas as far as FRX goes. I think your number two option is the best one at this point. I looked into the number three option but it doesn't seem anyone else has been ambitious enough to figure out what changes because most stuff I found online said, just deal with it. Also, I am fairly certain it would be different by version at the very least.
I tested one form, exported it a few times, the same four hex segments changed every time. I tried another form from the same workbook, ironically while it was a smaller form, it had quite a few more changes at ~20 segments changed. There seemed to be a pattern 2200-->3b09 and 00-->09 but no way I want to try and figure out the pattern of all the changes. I think this application could benefit from a few custom settings...

• Ask to overwrite .FRX
• Always export on save
• Always import upon opening
• Apply settings to this session only

Anyway, before I ramble on any further. Again, I vote for option "2"

[NealHumphrey]

Okay, I think I'm going to go ahead and develop option 2; we're just
starting to use this app on an Excel tool that has dozens of forms and I've
got a couple very-new-to-version-control team members I'm trying to get
using it.

Note - I have a pull request out to Hilkoc that adds the 'always export on
save' setting (as well as an extra function to export the XML of the Excel
file, i.e. unzip the contents of the .xlsm file). He previously Ok'd it but
I needed to rebase it onto his current master; he made a note that he's a
bit busy to respond to stuff on the development so might not be in there
for a bit more, if you want it in the meantime you can fork off my branch.

I will add the 'ask to overwrite' as a custom setting; but I'll probably
take the quick and dirty route for now and just do it as a public variable
at the top rather than actually saving the user preference to the add in
and adding the necessary user forms.

Neal

[NealHumphrey]

On a mildly related note, do you put your Excel files/tools (VBA +
spreadsheet components) into Git for version control? I've been trying to
figure out an appropriate workflow that deals with all the associated
issues (merging parallel development etc.) and this vbaDeveloper add-in has
made it actually a feasible possibility and I'm close to a decent set of
workflow conventions but would be great to hear someone else's methodology
if they're trying to do the same.

[gberl001]

Personally, I follow the method described here, although I do not use release branches, I work on Dev to fix bugs and add in features planned for the next release, features not complete have their own branches. I also have a fixed setting in my gitconfig to use --no-ff for all merges (to keep history).

[NealHumphrey]

Yes, I follow something very similar (actually based off that same post),
although I've also started rebasing every feature branch to squash the many
branch components down to just one commit (or a couple if it makes sense -
but get rid of all the 'halfway done' and 'fixing typo' commits).

For Excel files, I include the .xlsm file in the commit, but vbaDeveloper
lets me track all the VBA changes and see the diffs in the commit history.
Unless I'm merging though, I essentially just use this as backup and
consider the version in the Excel file itself to be the 'golden' version.
For spreadsheet changes, I make a log and then use a consistent formatting
(hot pink for instance) to highlight any sections changed in a specific
feature. Before merging this into Master, I make a clean version so you end
up with two commits, an 'annotated' that has the spreadsheet changes
marked, and a 'clean' that has them removed which is the one that goes back
into master.

The big issue I haven't quite resolved is when two people have edited the
Excel file (either just changing the VBA or both changing the spreadsheet).
Obviously you have to do actual merging of content manually (facilitated by
the annotations, or by the 'import' function of vbaDeveloper if VBA) but I
haven't figured out where to put the interim files that end up happening -
when you make the merges, you have to pick one file or the other, and I
haven't figured if it makes more sense to do the manual incorporation
before or after.

So much cleaner when you just work with code...

[hilkoc]

Thanks for all this useful input.I think it makes sense to add option 2 as a feature to the VbaDeveloper addin.

On a mildly related note, do you put your Excel files/tools (VBA + spreadsheet components) into Git for version control?

(Unfortunately) we do commit excel binary files to version control. Simply because some workbooks are more than just VBA code. For example you may have text, data and formatting in a worksheet. Although it is possible to build your entire workbook programmaticaly in VBA code, this is usually quite a lot of effort to do.
Other workbooks, usually xlam files, like the VbaDeveloper addin consist of only VBA code and for this you don't need to keep the binary file. However we commit all excel binaries, just to be consistent and keep things simple.

How do we deal with forms? We simply git ignore the .frx files. This way they don't show up as changed (although they may have) and we don't commit them. No information is lost, because the entire form is already part of the excel binary file, which we do commit. If you'd want to, you can simply export the .frx again from the original excel workbook.