VBA Version Property

It is sometimes useful to know which version of Excel your user is using.  An example of when this would be useful is knowing whether the user is in Excel 2007/2010 or and older version.  Excel 2007 was the first release that allowed over 65535 rows and 255 columns.  Excel 2007 has over 1 million rows (1048576) and 16384 columns.

This can make a big difference to what you can do with the data available to you.  If your user is in Excel 2003 for example your code could crash if it attempts to copy too much data into the active workbook.  The function below can be used within your code to confirm the Excel version, you can then call a different routine depending on it’s results.

Private Function ReturnExcelVersion() As String
'-----------------------------------------------------------------
' Procedure : ReturnExcelVersion
' Author    : Matthew - Zypher.co.uk
' Purpose   : return a string value denoting the version of Excel
'             Check the first two characters as Excel 97 has
'             several different releases and patches
'             http://support.microsoft.com/kb/232652
'-----------------------------------------------------------------
'
    Select Case Left(Application.Version, 2)

         Case Is = "14"
            ReturnExcelVersion = "2010" 
        Case Is = "12"
            ReturnExcelVersion = "2007"
        Case Is = "11"
            ReturnExcelVersion = "2003"
        Case Is = "10"
            ReturnExcelVersion = "2002"
        Case Is = "9."
            ReturnExcelVersion = "2000"
        Case Is = "8."
            ReturnExcelVersion = "97"
        Case Is = "7."
            ReturnExcelVersion = "95"
        Case Else
            ReturnExcelVersion = "Unknown"
    End Select
End Function

Excel 2010 is version 14. The version number 13 was skipped because of the aversion to the number 13.  Another relevant link is XL97: Overview and History of Excel Patches.

You may also want to look into Application.OperatingSystem.

As always, if you have any questions do let us know via the comments section.

Special Folders using the FileSystemObject

There are several methods to get find out the file paths for Microsoft Windows’ special folders (Systems folder, Temporary folder etc).  The example below uses the FileSystemObject.

Sub Special_Folders()
'-----------------------------------------------------------------
' Procedure : Special_Folders
' Purpose   : Retrieve file path for MS Windows 'special folders'
'             Requires a reference to Microsoft Scripting Runtime
'-----------------------------------------------------------------
'
    On Error GoTo ErrTrap

    Dim oFS As FileSystemObject

    Set oFS = New FileSystemObject

    ' Windows Folder Path
    MsgBox FS.GetSpecialFolder(WindowsFolder)

    ' System Folder - (example - Windows\System32)
    MsgBox oFS.GetSpecialFolder(SystemFolder)

    ' Temporary Folder Path
    MsgBox oFS.GetSpecialFolder(TemporaryFolder)

    If Not oFS Is Nothing Then Set oFS = Nothing

ErrTrap:
    Select Case Err.Number
        Case Is = 0
            ' No error continue
        Case Else
            MsgBox Err.Number & " - " & Err.Description
            Err.Clear
    End Select
End Sub

This routine requires a reference Microsoft Scripting Runtime.