• 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

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 1 Comment
    1. Madmexx's Avatar
      Madmexx -
      Hello Ken!

      Thx for sharing your skill with us.

      Public Sub Build_Change() has a little mistake in the last Row

      ThisWorkbook.Version must be ThisWorkbook.Build