Version Control?

I'm developing excel VBA applications and I was curious to know if anyone is using any add-ins or anything to help automate version control. Anything good out there?

Hello Kesey,

You could purchase a third party ActiveX control for around $300 that uses this same function, but if all you need is to do is return the version information for an executable file (.exe) or dynamic data library (.dll), the following macro will work with Windows '95, '98, Me, NT4.0, 2000, and Xp operating systems and is compatible with 16, 32 and 64 bit file structures. Copy and Paste this code into a Standard VBA Module in your project. When calling the macro you can use just the name. The system will try to resolve the location using the LoadLibrary function. If it fails, you will need to include the full path and file name. This macro displays a message box with the version information. This can be changed easily if you simply want to return it as a formatted string.

'Written: June 9, 2007
'Author: Leith Ross
'Summary: Returns the Major and Minor version numbers of a file.
'Works with Windows '95, '98, Me, NT4.0, 2000, and Xp

Private Type VS_FIXEDFILEINFO
    'Contains the value 0xFEEFO4BD (szKey)
      dwSignature As Long
    'Specifies the binary version number of this structure.
      dwStrucVersion As Long
    'most significant 32 bits of the file's binary version number.
      dwFileVersionMS As Long
    'least significant 32 bits of the file's binary version number.
      dwFileVersionLS As Long
    'most significant 32 bits of the binary version number of
    'the product with which this file was distributed
      dwProductVersionMS As Long
    'Contains a bitmask that specifies the
    'Boolean attributes of the file.
      dwProductVersionLS As Long
    'least significant 32 bits of the binary version number of
    'the product with which this file was distributed
      dwFileFlagsMask As Long
    'Contains a bitmask that specifies the valid bits in dwFileFlags.
      dwFileFlags As Long
    'operating system for which this file was designed.
      dwFileOS As Long
    'general type of file.
      dwFileType As Long
    'function of the file.
      dwFileSubtype As Long
    'most significant 32 bits of the file's 64-bit
    'binary creation date and time stamp.
      dwFileDateMS As Long
    'least significant 32 bits of the file's 64-bit binary
    'creation date and time stamp.
      dwFileDateLS As Long
End Type


Private Declare Function GetFileVersionInfo _
  Lib "Version" _
   Alias "GetFileVersionInfoA" _
     (ByVal lptstrFilename As String, _
      ByVal dwHandle As Long, _
      ByVal dwLen As Long, _
      ByRef lpvData As Any) As Long
      
Private Declare Function GetFileVersionInfoSize _
  Lib "Version" _
    Alias "GetFileVersionInfoSizeA" _
     (ByVal FileName As String, _
      ByRef dwHandle As Long) As Long
      
Private Declare Function VerQueryValue _
  Lib "version.dll" _
    Alias "VerQueryValueA" _
     (ByRef pBlock As Any, _
      ByVal lpSubBlock As String, _
      ByRef lplpBuffer As Long, _
      ByRef puLen As Long) As Long

Private Declare Sub CopyMemory _
  Lib "Kernel32" _
    Alias "RtlMoveMemory" _
     (ByRef hpvDest As Any, _
      ByRef hpvSource As Any, _
      ByVal cbBytes As Long)
 
Private Function LOWORD(dw As Long) As Integer

  'Retrieves the low-order word from the given 32-bit value.
    If dw And &H8000& Then
        LOWORD = dw Or &HFFFF0000
    Else
        LOWORD = dw And &HFFFF&
    End If
    
End Function
 
Private Function HIWORD(dw As Long) As Integer

  'Retrieves the high-order word from the given 32-bit value.
    HIWORD = (dw And &HFFFF0000) \ &H10000
    
End Function

Sub GetVersionInfo(File_Name As String)

  Dim FI As VS_FIXEDFILEINFO
  Dim FileVer As String
  Dim dwHandle As Long
  Dim pBuffLen As Long
  Dim pBuff() As Byte
  Dim sBuff As Long
  Dim Ret As Long
    
    pBuffLen = GetFileVersionInfoSize(File_Name, dwHandle)
    
    If pBuffLen = 0 Then
       MsgBox "Invalid File Name or no Version information available"
       Exit Sub
    Else
       ReDim pBuff(pBuffLen)
    End If
    
    Ret = GetFileVersionInfo(File_Name, dwHandle, pBuffLen, pBuff(0))
    
  'SubBlock = "\" specifies the root block. The function retrieves a pointer to _
  'the VS_FIXEDFILEINFO structure for the version-information resource.
    Ret = VerQueryValue(pBuff(0), "\", sBuff, pBuffLen)
      If Not Ret = 0 Then
         CopyMemory FI, ByVal sBuff, Len(FI)
         FileVer = Trim$(Str$(HIWORD(FI.dwFileVersionMS))) & "." _
           & Trim$(Str$(LOWORD(FI.dwFileVersionMS))) & "." _
           & Trim$(Str$(HIWORD(FI.dwFileVersionLS))) & "." _
           & Trim$(Str$(LOWORD(FI.dwFileVersionLS)))
         MsgBox File_Name & vbCrLf & FileVer, 64, "Version Information for"
      End If

End Sub

Example of Version Macro Call:
GetVersionInfo "Excel.exe"
or
GetVersionInfo "C:\Program Files\Microsoft Office\Office\Excel.exe"

Sincerely,
Leith Ross