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:
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:
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:
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:
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
