GetXLversion

xlf | GetXLver macro


GetXLver - about the macro


Description: returns the version number of the Excel application.

Excel version information is displayed in a message box. Example output is shown in figure 1.


xlf-getxf-function_num
Fig 1a. - Version message box displaying the output from the GetXLver macro.


xlf-get-version-1b
Fig 1b. - Version message box displaying the output from the GetXLver macro. Excel 2016 and Windows 10


Selected VBA functions used in this module.


VBA function / property Description
Application.Build (property)Returns a Long value for the Microsoft Excel build number
Application.OperatingSystem (property)Returns the name and version number of the current operating system
Application.Version (property)Returns a String value that represents the Microsoft Excel version number
Environ({envstring | number}) (function)Returns a String associated with an operating system environment variable. When the evstring is set to ProgramW6432, it is used to identify a 64 bit system

GetXLver - the VBA code



Code 1: Macro GetXLver returns a message box displaying the Excel version details
Public Sub GetXLver()
Dim VerNo As String, s64bit As String
Dim Prmt As String, Ttl As String        ' Message Box - Prompt, and title
Const DblSpace As String = vbNewLine & vbNewLine
 
Select Case Application.Version
    Case "5.0"
        VerNo = "Excel 5"
    Case "7.0"
        VerNo = "Excel 95"
    Case "8.0"
        VerNo = "Excel 97"
    Case "9.0"
        VerNo = "Excel 2000"
    Case "10.0"
        VerNo = "Excel 2002 (included in Office XP)"
    Case "11.0"
        VerNo = "Excel 2003"
    Case "12.0"
        VerNo = "Excel 2007"
    Case "14.0"
        VerNo = "Excel 2010"
    Case "15.0"
        VerNo = "Excel 2013"
    Case "16.0"
        VerNo = "Excel 2016"
    Case Else
        VerNo = "Unknown version"
End Select

    If Len(Environ("ProgramW6432")) > 0 Then s64bit = " (64-bit)"

With Application
    Prmt = "Version details: " & DblSpace & VerNo & s64bit & vbNewLine & "Number: " & .Version & ", build: " & .Build
    Prmt = Prmt & vbNewLine & "Operating system: " & .OperatingSystem
    Prmt = Prmt & DblSpace & "Coded by ExcelAtFinance " & Chr(169) & Chr(32) & Year(Date)
    Ttl = "About Excel"
End With
 
    MsgBox Prmt, vbInformation, Ttl
     
End Sub
											

About code 1: the Application.Version number, line 6, is returned as a text string by the Select ... Case statement. Identification of the 64-bit version is shown in line 31, and exists if the Environ string "ProgramW6432" has greater than zero characters. Further details of Environ are shown in code 2, and figures 2, 3 and 4.

Line 34 to 36 build the message box prompt. Sample output (Excel 2013 64-bit running in a windows 64-bit environment) is shown in figure 1 above.


The VBA Environ function


Code 2 uses a For...Next loop to print a list to the Immediate Window


Code 2: Macro GetEnviron returns a list of Windows environment variable values to the Immediate Window
Private Sub GetEnviron()
Dim i As Integer
Debug.Print "excelatfinance.com - GetEnviron - at: " & Time & vbNewLine
For i = 1 To 45		' upper value is set by trial and error
    Debug.Print "Environ #" & i & ": " & VBA.Interaction.Environ(i)
Next
End Sub

Code 3 uses a Do While loop to print a list to the Immediate Window


Code 3: Macro GetEnviron2 returns a list of Windows environment variable values to the Immediate Window
Private Sub GetEnviron2()
Dim i As Integer
Dim EnvironVar As String
    Debug.Print "excelatfinance.com - GetEnviron - at: " & Time & vbNewLine
    EnvironVar = Environ(1)
    Do While EnvironVar <> ""
        i = i + 1
        Debug.Print "Environ #" & i & ": " & Environ(i)
        EnvironVar = Environ(i + 1)
    Loop
End Sub

  1. Line 60: To convert to a Do Until loop, replace line 60 with
  2.         Do Until EnvironVar = ""
    

Immediate window

The GetEnviron macro in code 2 is run in a Windows 64 bit environment and a Windows 32 bit environment, and the output is shown in figures 2 and 3 respectively. The ProgramW6432 variable is shown as variable #25 in figure 2. The output for Windows 10 is included in figure 4.


xlf-windows7-64-ver
Fig 2. - Immediate window - sample output for Environment Windows 7 (64 bit)


xlf-windows7-32-ver
Fig 3. - Immediate window - sample output for Environment Windows 7 (32 bit). Variable ProgramW6432 does not exist


xlf-windows10-ver
Fig 4. - Immediate window - sample output for Environment Windows 10 (64 bit). Variable ProgramW6432 is #27