In my current position, I manage the care and feeding of a type of planning system. The whole thing has been built in Excel, and presents more than a few challenges. Since I didn’t do the original construction, I inherited the application, and without being able to make major back-end changes, I’ve only been able to make the types of improvements and updates that have made the users’ live easier.
One of the pieces that makes my life more difficult is that this system is actually comprised of several different Excel files. One of them is the “Big Mutha”, the spreadsheet that tries to act like a database. It contains all the data and the templates (now) use SQL to pull data out it.
Other of these files are reports of varying sorts that (hopefully) give the user the info they need to make decisions.
Yet another set of files are the data entry templates. These are the Excel files in which users enter their data and submit it to my group, which we then process into the Big Mutha. These files are not so restricted that people can’t save them. They can. They can fill them out, save them to their local machine, and submit the data to us. The hiccup with these files is that we have to update them with new fiscal calendar data once per quarter (or, every three months, for those of you not in a fiscal mindset). While I have a routine that will pull the new fiscal data into the templates when the data is ready, there are other times when we need to make an enhancement or fix a bug and then issue a new release of the template. When this happens, the template the user has saved on their machine is outdated and can’t be used again.
So, how do I control when it’s time to issue a new release? How do I inform the user that their current template is old and can no long be submitted to us?
“Email”, you say? Yeah. No. Nobody reads. And I’ve found this to be true in every company I’ve ever worked for. If you send an email out and assume people will read it and obey what you’re telling them, well, you know what they say about when you assume. Except you’ve only made an ass of you.
Sneaker network? (For those who’ve never heard the term, “sneaker network” is when you get up and walk from cube to cube and update everyone you talk to.) In a land of 1500 users, that too is a no, my friend.
So, seriously, how do you do it? How do you control releases in Excel?
With Visual Basic for Applications, of course!
My methodology has been to use a combination of VBA and configuration files to manage this. Let’s start with the configuration file:
[VersionControl] Version=1.2 UpdateRequired=FALSEI’ve got a section for version control. Under that section there are two keys, one for the version number, and one for whether an update to the new release is required.
In the Excel file, I make sure that I have a public constant that holds which version of the template that file represents:
Public Const gsVERSION As String = "1.1"Don’t forget the constants that hold the configuration file name and path:
Public Const gsINI_PATH As String = "C:\SomeLocation\SomeLocation\SomeLocation\" Public Const gsINI_FILE As String = "your_configuration_file.ini"Next, I have a function that I can call that tests whether the template is the current version and, if it’s not the current version, whether an update is required. It evaluates whether the version number in the configuration file matches the version number in the public constant. If the match, great. If they don’t, then it does a second check to see if an update is required to the newer version:
Function bUpdateRequired() As Boolean '******************************************************************************* ' Description: This checks if this workbook is the latest released workbook based ' on a setting in the configuration file and then checks if an ' updated version is required. ' ' Author: Scott Lyerly ' Contact: scott.c.lyerly@gmail.com ' ' Arguments: None ' ' Returns: Boolean ' ' Change Log: ' Name: Date: Init: Modification: '--------------------------------------------------------------------- ' bUpdateRequired V1 04-AUG-2014 SCL Original development ' '******************************************************************************* ' Check the version of this workbook against the latest released version If CDbl(sManageSectionEntry(iniRead, "VersionControl", "Version", gsINI_PATH & gsINI_FILE)) > CDbl(gsVERSION & gsBUILD) Then ' If the released version is greater than this workbook's version, check if an update is required. bUpdateRequired = CBool(sManageSectionEntry(iniRead, "VersionControl", "UpdateRequired", gsINI_PATH & gsINI_FILE)) Else bUpdateRequired = False End If End FunctionFor this function, I’m calling my function that consolidated the reading and writing from and to a configuration file. You can read about it here.
Now, controlling what someone can do this this file is as easy and tapping into the bUpdateRequired function. Just check to see if it’s False and then you can control the action you take next. Something like this:
If Not bUpdateRequired Then Msgbox "This is not the latest version of this file. You must download an updated template.", vbExclamation Exit Sub End IfThat’s all there is to it. There is probably a finer point to be made about what happens when a user falls so far behind in versioning that, even though an update might not be required to the latest release, the version the user is using is no longer supported. For example, an update might not be required to go from version 3.2 to 3.3, but the user is using version 1.5. Version 1.5 might be too out of date and is no longer supported at all, but since the UpdateRequired key reads False, there’s nothing to stop them from using 1.5. Honestly, I haven’t run into that yet. If I do, I’ll figure something out. For now, what I use above is more than enough for me to control versions.
Scott C Lyerly 
Nice work. I had identified the same issue, and agree with your assessment that version control is the only way to make sure the latest version is being used. I did have a system where I used shortcuts to point to a master pricelist, but it still required training people not to “save as” a file and use it instead.
I have an initial test version control macro. I’ll be interested to see how it compares to yours once I have time to do that. I know already it’s not as strong as yours. The initial difference is I decided to use a spreadsheet to contain the version information as I didn’t know how to deal with a text file in a macro. Also, I haven’t got the second flag for “required”. My thinking was to force the upgrade on every change, but understand why you wouldn’t necessarily want to do that.