Solved: Version Control

[jwise]

In the mainframe world I came from, version control was much easier because the program source was stored separately from program data. When I work on a task, I have a spreadsheet (the data) and my code in the same ".xls" file. I often find myself in the situation where a bug in my code will destroy this data. My natural inclination is to "duplicate" this .xls file (using File SaveAs or COPY). Now I can easily recover from a bug.

Unfortunately, in a normal development cycle, this happens multiple times over multiple days. After a week or so, I can't remember which version is which. This is especially true when I get side-tracked onto other problems for a while.

I realize this is fully a problem of my own making, but I think others must have encountered this, and I'd like to know how you approached it. How do you handle version control?

TIA

[Bob Phillips]

Add custom properties of (base) filename and version, and modify the Save button to check if there is a version property, to save tow copies, one with the base name, one with base name and the version number. Just need to remembre to change the version number then.

[jwise]

Thanks again for your always-informative reply.

I found some example code which essentially puts the file name and dir name in a worksheet (an additional sheet is added to the workbook), and it includes a macro. This macro displays a command button (SAVE). By clicking this button, the user can save his .xls file under the name and path in the worksheet. Another cell keeps a "version number" which is incremented each time it is used, thus the full name is "path-file Vnnnn.xls".

Now the problem with this is you must put this worksheet in your workbook, and you must select the worksheet to get the command button to save. Plus you really should update the file name and path as appropriate (in this additional worksheet).

That is too many things to do!

Can I create an "add-in" such that I get an extra option on the "file save menu" which automatically appends the version number in the path/file name? Ideally, there would be some sort of "internal storage" for a list of these "versioned" files, and I could display that.


TIA.

[Bob Phillips]

jwise,

what I would probably do in that case is to add a custom document property or maybe a defined name) of say SaveVersion, and intercept the Save command to check if the propety exists, and if so, do a SaveAs with the next version number. All you would then do is to create the document property with a value of 1 when you create the workbook, and save it.

The reason that I suggested the double save method is that is the way I do it, because I always want to have a file with the build number, and a version without the build number which I can then release as a 'standard' name file.

No time now, it is midnight here, but I will knock up some code for you tomorrow for that first method.

[Bob Phillips]

As promised some code.

As I said, first create a custom document property (File>Properties>Custom) of SaveVersion and a value of 1.



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim nVersion As Long
Dim sFilename As String

With ThisWorkbook
Application.EnableEvents = False
On Error Resume Next
nVersion = .CustomDocumentProperties("SaveVersion")
On Error GoTo 0
If nVersion > 0 Then
If SaveAsUI Then
sFilename = Application.GetSaveAsFilename("", "Excel Files (*.xls), *.xls")
If sFilename = "False" Then
Cancel = True
Application.EnableEvents = True
Exit Sub
End If
Else
sFilename = .FullName
End If
nVersion = nVersion + 1
.CustomDocumentProperties("SaveVersion").Value = nVersion
sFilename = Replace(sFilename, " v" & nVersion - 1, "")
.SaveAs Replace(sFilename, ".xls", "") & " v" & nVersion & ".xls"
Cancel = True
End If
End With
Application.EnableEvents = True
End Sub

[jwise]

Thank you!

You not only solve the problem, you also open the door for me to solve other problems with the clear and straightforward approach.

After my preliminary study, I see the one weakness of this solution is that there is something you have to do to enable it. On the other hand, it is certain that I (and probably others) would not want this version control on EVERY workbook. The best solution to this seems to be to use the macro recorder to track the installation of the custom document property, and then have a macro that I can invoke on any workbook that enables this code for that workbook.

As a student of coding, I certainly appreciate your very clear and concise code. Your code always demonstrates very clear principles of design and implementation. A lot of code in version 1 looks patched because the original design was flawed, and then the code was adapted to work. This practice bodes well to the future. I believe that the true cost of software is highly influenced by the code's clarity and its ability to be modified by others.

[Bob Phillips]

Thanks.

As you say it does have the drawback/feature (depending upon your perspective) that the workbook does have to be primed. As you say, you wouldn't want to automatically version every workbook that you create, so you need some way to indicate whether or not this applies to a particular workbook.

I forgot to mention that this would best be created as an application event. By doing this, and putting the code in Personal.xls ThisWorkbook, it will automatically invoke for all workbooks. Then of course, that indication becomes paramount.

This is the code in application event mode



Private WithEvents app As Application

Private Sub app_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim nVersion As Long
Dim sFilename As String

With Wb
Application.EnableEvents = False
On Error Resume Next
nVersion = .CustomDocumentProperties("SaveVersion")
On Error GoTo 0
If nVersion > 0 Then
If SaveAsUI Then
sFilename = Application.GetSaveAsFilename("", "Excel Files (*.xls), *.xls")
If sFilename = "False" Then
Cancel = True
Application.EnableEvents = True
Exit Sub
End If
Else
sFilename = .FullName
End If
nVersion = nVersion + 1
.CustomDocumentProperties("SaveVersion").Value = nVersion
sFilename = Replace(sFilename, " v" & nVersion - 1, "")
.SaveAs Replace(sFilename, ".xls", "") & " v" & nVersion & ".xls"
Cancel = True
End If
End With
Application.EnableEvents = True
End Sub

Private Sub Workbook_Open()
Set app = Application
End Sub


and you can always add a simple sub to prime it



Public Sub PrimeWorkbook()

ActiveWorkbook.CustomDocumentProperties.Add _
Name:="SaveVersion", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=1
End Sub

[jwise]

Your code certainly fulfills every requirement that I suggested. Thank you very much.

You mentioned "Application event" and I assume this is similar to "workbook event" or "Worksheet event". I am familiar with the latter two. I will search for the former. I always feel that there are some hidden pieces to this puzzle. Where/how did you learn about this? I must be doing something wrong in my approach to learning how/what can be done.

Thanks again for this work.

[Bob Phillips]

Application events are like those two, just one level above.

The big problem with application events is that worksheet and workbook events have an obvious place in which to store the code. Application events don't have a ThisApplication location, so you have to store it inan addin, or Personal.xls.

As to learning, it is practice, and it is my job.

[jwise]

Thanks again for the information.

I will install/test today.

[jwise]

I created a test worksheet and saved it. I restarted Excel (I am now XL2003 on both systems), modified, saved, and exited again, multiple times. I had 6 versions of this test worksheet. The macro worked as designed which turned on the property.

By mistake actually, I edited V5 of the workbook today. I made a change and did a save. I then received a message that there was a duplicate v6 file, did I want to replace, end, or cancel. Since I recognized this as I had edited the wrong version (and there are circumstances where I would want to go to a back level), I selected CANCEL.

I got a message saying "Error Runtime 1004". Method "SaveAs" of object "_Workbook" failed.

I take this to mean the caller of the subroutine had a problem, not the subroutine code itself.

I also noticed a strange phenomenom... when I start XL2003, I get a blank screen where I used to get a new "book1.xls". There is only 1 sheet in this workbook (I selected 1 as a default value). I can find no option to specify the default file to start on. I probably did something wrong again.