Version Control for Excel VBA Code - Use of GIT? 2

[kjv1611]

I've been using VBA to get things done and automate tasks for myself and others for many years now. I started a new job at a different employer last Summer, and there was little to no documentation on many of the tasks I do here. One of the things that has the least amount of documentation for the person handling the IT side of things is a large assortment of interwoven Excel workbooks with some centralized code, and some not so centralized code. Well, I've seen something Git and TortoiseGit (application) in various bits around, but it's something I've never used. My only exposure to Git before was sometimes looking at patches/updates for some web design items, and maybe a freeware/opensource program or two. But this was the first I'd seen of git being used for version control in Excel workbooks.

I've not really had time from fixing broken things to sit down and think through how to use it just yet. Does anyone here have any idea where I could get some good solid info on using Git or some other Version Control software to help keep track of Excel workbook/vba changes? I don't suppose it's necessary, but with how things have gone in this first year, I'm beginning to see where it very well could be helpful.

Thanks for any thoughts, links, etc.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[xwb]

I'd be very careful about excel - .xl? stores in binary, .xls? stores in xml. If you use something like GIT or SVN, it is pretty useless with the binary versions. It does not store the differences: it just stores whole copies. If your spreadsheet is 1K, after 5 saves, the DB will be 5K. What's even worse, is it won't tell you the diffs between the different versions.

 

[DjangMan]

Unfortunately (or, sometimes, fortunately) VBA stores it's code within the document so your modules and classes can't be easily processed using version control. One simple option is version based commenting. I have to do this with my work because I'm dealing with a mix of VB and VBA across 5 different versions of the software that I customize.

What I do is to make a point of placing code into modules and classes whenever I can. Any time that I make a change I simply add a comment starting with '??? and then add a comment describing the change. When I'm ready to release that chunk of code I go to a module that contains my version information. I have a const for the version number that I increment by 1. Then I add a final 'header' comment with ???, my initials, date and a summary of changes. Finally, I do a global search/replace to replace ??? with the version number. No need to remember a version number while I'm trying to write code.

Then I use version numbering in my files. It gets a bit full in the directory but sub-folders can fix that. So when I have a file such as VendorPriceList.xls I'll have a copy of that file for each version released:
VendorPriceList 001.xls
VendorPriceList 002.xls
VendorPriceList 003.xls

And, finally, a copy of the latest version without the version number:
VendorPriceList.xls
Why? So that icons/shortcuts that other people have don't have to be updated. They are always using the 'most up to date' version. If I have to roll back then I can rename the files and I'm back online.

So when I start work on VendorPriceList version 004 I copy the 003 file, rename it to 004_WIP. When I'm done I rename 004_WIP to 004 and then remove the VendorPriceList.xls and replace it with a copy of the 004 file.

I do the same for my module and class files, too, by exporting them from the IDE (or using MZTools). It makes it relatively easy to open the .cls or .bas file in a text editor, copy (most of) the lines and paste it right into the IDE to update to a newer version of the same module or class. This is primarily the only area where git or version control would be of use.

I'm the only one writing code at my job so one could argue that I don't need to do all of this but it does make my job easier when 30 different macros all make use of the same .bas and .cls files. I quick glance at the top of the code gives me the version number of that file and a quick scan of the source code folder tells me if it is the latest or not.

Good luck with your cleanup task!

 

[kjv1611]

Thanks for the comments.

xsb, I had no idea as to the differences and how they are stored. Some of our files are still .xls (.xl?) and some now as (.xls?). Haven't been able to totally convert to all .xls? as there are still a few holdout computers running older software - WinXP, and one or two of those running Excel 2003. Regardless, that's good to know it stores whole copies. So in that case it would be somewhat similar to what I do manually anyway, keeping different copies. In general, so far we have production and test. I work in test to make changes, and then we eventually push that to production (copy to production after backing up current/previous production files).

DjangMan, that sounds like a lot to keep track of. It may indeed work well, but I'm not sure I'm ready to try to implement that at this point. I do try to make a point and comment each change with my name, date, and why I changed it. But I suppose if you keep a centralized location documenting the changes with actual version numbers or change numbers or some identifier, then you could go to that central reference, copy the version/item number, and search for it in code to find where you changed something.

For now, I'll definitely just keep things where they, and keep researching alternative methods.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Hmm... what I think at least one person did along the way was connected Excel to Visual Studio, and perhaps that's how he made use of Git. I'll keep digging into this off and on. Can't spend much time with it yet unfortunately.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[xwb]

If you download and the office compatiblity pack from MS (FileFormatConverters.exe) it will allow Office 2003 to read and save xlsx, xlsm etc. Note that the xml formats are quite fussy. If it has macros, they would like you to save it as xlsm. Once you have that, then everything will be in xml. The file format converters work on XP.

 

[kjv1611]

Thanks for the info, xwb.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57