Maintaining Version and Build Information

Introduction:
This article shows the method I use to maintain versions and builds in my applications and add-ins, which is to add properties to the ThisWorkbook module. These properties store their values in custom document properties, accessed from File|Properties|Custom, as shown below:

Benefits of this approach
Some of the benefits you get by using this method are:

  • You get a place to store a value within the workbook, but avoid the need to store it in a worksheet cell. this can potentially save you creating a worksheet specifically for this purpose.
  • You can avoid the use of code to place information in the registry, which makes a workbook more portable between users.
  • You can store a piece of variable information between procedures. (Variables go out of scope after procedures complete.)
  • You can use intellisense to easily call up a property when coding

Drawbacks of this approach

  • Users can change or delete these values if they know that they exist, or have reason to do so.

Other uses
The other uses for this are numerous, but one that stands out, in particular, is the creation of a "DebugMode" routine.

Methodology
The biggest issue we face when trying to hold properties is scope. Once all procedures end, all variables are released, which gives us an issue if we are trying to hold a property between routines. This particular method stores the property value in a Custom Document Property and retrieves it when called. Not only will it hold a value after all code completes, but it can also hold a value between sessions. It does require the ability to write to the workbook, as any property adjusted here will only hold between sessions if the workbook is saved.

The method below creates properties for both the build and version of my applications.

The Build Property
We'll start by creating the procedures to set the value of the Version and Build properties. To do this, we need to use Property Let statements. The Property Let statements require values to be passed to them, so that the properties can be assigned the specified values. These procedures will create customdocument properties (called "Version" and "Build"), if required, and store the values there.

Place the following code in the ThisWorkbook module:

Code:
Public Property Let Version(lng As Long)
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Sets the Version property to requested value

    Dim DocProps As DocumentProperties

    'Save Version value in a customdocument property
    On Error Resume Next
    ThisWorkbook.CustomDocumentProperties("Version") = lng

    'If customdocument property did not exist, create it
    If Err.Number <> 0 Then
        Set DocProps = ThisWorkbook.CustomDocumentProperties
        DocProps.Add Name:="Version", _
                LinkToContent:=False, _
                Type:=msoPropertyTypeNumber, _
                Value:=lng
    End If
    On Error GoTo 0
End Property

Public Property Let Build(lng As Long)
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Sets the Build property to requested value

    Dim DocProps As DocumentProperties

    'Save Build value in a customdocument property
    On Error Resume Next
    ThisWorkbook.CustomDocumentProperties("Build") = lng

    'If customdocument property did not exist, create it
    If Err.Number <> 0 Then
        Set DocProps = ThisWorkbook.CustomDocumentProperties
        DocProps.Add Name:="Build", _
                LinkToContent:=False, _
                Type:=msoPropertyTypeNumber, _
                Value:=lng
    End If
    On Error GoTo 0
End Property

Now we need a way to get the information out of the properties. To do this, we need "Property Get" statements. (Properties with only Property Get statements are read only, but we need the ability to write to these.) The Property Get statments for the "Version" and "Build" properties follow, and also goes in the ThisWorkbook module:

Code:
Public Property Get Version() As Long
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Return Version from customdocument property
    Version = ThisWorkbook.CustomDocumentProperties("Version")
End Property

Public Property Get Build() As Long
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Return Build from customdocument property
    Build = ThisWorkbook.CustomDocumentProperties("Build")
End Property

Initializing the values
The easiest way to initialize the values is to run the following statements in the Immediate window:

Code:
ThisWorkbook.Version = 1
ThisWorkbook.Build = 1

This will set each to a value of 1, creating the CustomDocument property for further use.

Using the properties
One of the great things about using this method is that it exposes the property to intellisense as part of the ThisWorkbook object. As an example, I've snapped a screen shot of using it in the Immediate window below:

The way that I tend to access these properties most, however, is the creation of four menu items, which trigger the following routines:

  • Version_Change - To change the version to a number that I supply
  • Version_Increment - To increment the version number by 1
  • Build_Change - To change the build to a number that I supply
  • Build_Increment - To increment the build number by 1

PLEASE NOTE: There is no error handling in any of the following routines to date. If a user deletes the Custom Property, they will fail at runtime.

The procedures all go in a standard module, and are as follows:

Code:
Private Const vbNL = vbNewLine
Public Sub Version_Change()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Change the version number to a new value

    Dim varNewVer As Variant

    'Get the new version number
    Do
        varNewVer = InputBox("You are currently working with:" & vbNL & _
                "Version" & vbTab & ":" & vbTab & ThisWorkbook.Version & vbNL & _
                "Build" & vbTab & ":" & vbTab & ThisWorkbook.Build & vbNL & _
                "Please enter the new Version number", "Enter new Version", _
                ThisWorkbook.Version + 1)
        'Exit if user cancels or deletes value
        If varNewVer = vbNullString Then Exit Sub
    Loop Until IsNumeric(varNewVer)
    'Update value
    ThisWorkbook.Version = cLng(varNewVer)
End Sub

Public Sub Version_Increment()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Increment the Version number by one
    ThisWorkbook.Version = ThisWorkbook.Version + 1
End Sub

Public Sub Build_Change()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Change the build number to a new value

    Dim varNewBld As Variant

    'Get the new version number
    Do
        varNewBld = InputBox("You are currently working with:" & vbNL & _
                "Version" & vbTab & ":" & vbTab & ThisWorkbook.Version & vbNL & _
                "Build" & vbTab & ":" & vbTab & ThisWorkbook.Build & vbNL & _
                "Please enter the new Build number", "Enter new Build", _
                ThisWorkbook.Build + 1)
        'Exit if user cancels or deletes value
        If varNewBld = vbNullString Then Exit Sub
    Loop Until IsNumeric(varNewBld)
    'Update value
    ThisWorkbook.Version = cLng(varNewBld)
End Sub

Public Sub Build_Increment()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Increment the build number by one
    ThisWorkbook.Build = ThisWorkbook.Build + 1
End Sub

Example File

Share:

Facebook
Twitter
LinkedIn

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts

Excel Fundamentals Boot Camp

COACHED TRAINING: Excel Fundamentals Boot Camp Course Description In the Fundamentals Boot Camp, you will begin with a review core skills for the Excel analyst. This section is geared to

Read More »