Need some testers to display there findings from the following script that checks for excel version being used

[johnnyL]

Sub ExcelVersionChecker()
'
    Dim ExcelVersion As Integer
'
    ExcelVersion = Application.Version
'
    Select Case ExcelVersion
        Case 8:
            ThisVersionfExcel = "Excel 97"
        Case 9:
            ThisVersionfExcel = "Excel 2000"
        Case 10:
            ThisVersionfExcel = "Excel 2002"
        Case 11:
            ThisVersionfExcel = "Excel 2003"
        Case 12:
            ThisVersionfExcel = "Excel 2007"
        Case 14:
            ThisVersionfExcel = "Excel 2010"
        Case 15:
            ThisVersionfExcel = "Excel 2013"
        Case 16:
'           Test for Excel 2016, 2019 and 365 in Win and Mac Excel
            Dim Case16Result As String
'
            On Error Resume Next
'
'           Test for the XMATCH function available in Excel 365
            Case16Result = Application.Evaluate("=XMATCH(5,{5,4,3,2,1})")
'
            If Err = 0 Then
                ThisVersionfExcel = "Excel 365"
            Else
                Err.Clear
'
'               Test for the CONCAT function available in Excel 2019
                Case16Result = Application.Evaluate("=CONCAT(""A"",""B"")")
'
                If Err = 0 Then
                    ThisVersionfExcel = "Excel 2019"
                Else
                    Err.Clear
                    ThisVersionfExcel = "Excel 2016"
                End If
            End If
        Case Else:
            ThisVersionfExcel = "Excel Unknown Version of " & ExcelVersion
    End Select
'
    MsgBox "You are running " & ThisVersionfExcel & " on " & Application.OperatingSystem & "."
'
End Sub

Just want to see how accurate it is.

 

[johnnyL]

I just checked Excel 2007 & Excel 2013 32 bit on my end and it was correct.

 

[Dan_W]

A spell correction:

Sub ExcelVersionCheckerV2.5()
'
    Dim ExcelVersion        As Integer
    Dim ThisVersionOfExcel   As String
'
    ExcelVersion = Application.Version
'
    Select Case ExcelVersion
        Case 8:
            ThisVersionOfExcel = "Excel 97"
        Case 9:
            ThisVersionOfExcel = "Excel 2000"
        Case 10:
            ThisVersionOfExcel = "Excel 2002"
        Case 11:
            ThisVersionOfExcel = "Excel 2003"
        Case 12:
            ThisVersionOfExcel = "Excel 2007"
        Case 14:
            ThisVersionOfExcel = "Excel 2010"
        Case 15:
            ThisVersionOfExcel = "Excel 2013"
        Case 16:
'           Test for Excel 2016, 2019 and 365 in Win and Mac Excel
            Dim Case16Result As String
'
            On Error Resume Next
'
'           Test for the XMATCH function available in Excel 365
            Case16Result = Application.Evaluate("=XMATCH(5,{5,4,3,2,1})")
'
            If Err = 0 Then
                ThisVersionOfExcel = "Excel 365"
            Else
                Err.Clear
'
'               Test for the CONCAT function available in Excel 2019
                Case16Result = Application.Evaluate("=CONCAT(""A"",""B"")")
'
                If Err = 0 Then
                    ThisVersionOfExcel = "Excel 2019"
                Else
                    Err.Clear
                    ThisVersionOfExcel = "Excel 2016"
                End If
            End If
        Case Else:
            ThisVersionOfExcel = "Excel Unknown Version of " & ExcelVersion
    End Select
'
    MsgBox "You are running " & ThisVersionOfExcel & " build #" & Application.Build & " of " & Application.OperatingSystem & "."
'
End Sub

So I tested the version quoted above (with the corrected spelling).

When I ran the code, my laptop just started laughing maniacally at me - then it burst into tears and it ran out of the apartment... what manner of evil sorcery have you cast over this code?!?

Just kidding - it ran perfectly and I can confirm that the output below is correct.

There was one problem though - you had put a decimal in the name of the updated subroutine - Sub ExcelVersionCheckerV2.5() - VBA wasn't happy with that...

 

[johnnyL]

Just kidding - it ran perfectly and I can confirm that the output below is correct.

View attachment 43744

There was one problem though - you had put a decimal in the name of the updated subroutine - Sub ExcelVersionCheckerV2.5() - VBA wasn't happy with that...

Dang it!!!!

 

[johnnyL]

Sub ExcelVersionCheckerV3()
'
    Dim ExcelVersion        As Integer
    Dim ThisVersionOfExcel   As String
'
    ExcelVersion = Application.Version
'
    Select Case ExcelVersion
        Case 8:
            ThisVersionOfExcel = "Excel 97"
        Case 9:
            ThisVersionOfExcel = "Excel 2000"
        Case 10:
            ThisVersionOfExcel = "Excel 2002"
        Case 11:
            ThisVersionOfExcel = "Excel 2003"
        Case 12:
            ThisVersionOfExcel = "Excel 2007"
        Case 14:
            ThisVersionOfExcel = "Excel 2010"
        Case 15:
            ThisVersionOfExcel = "Excel 2013"
        Case 16:
'           Test for Excel 2016, 2019 and 365 in Win and Mac Excel
            Dim Case16Result As String
'
            On Error Resume Next
'
'           Test for the XMATCH function available in Excel 365
            Case16Result = Application.Evaluate("=XMATCH(5,{5,4,3,2,1})")
'
            If Err = 0 Then
                ThisVersionOfExcel = "Excel 365"
            Else
                Err.Clear
'
'               Test for the CONCAT function available in Excel 2019
                Case16Result = Application.Evaluate("=CONCAT(""A"",""B"")")
'
                If Err = 0 Then
                    ThisVersionOfExcel = "Excel 2019"
                Else
                    Err.Clear
                    ThisVersionOfExcel = "Excel 2016"
                End If
            End If
        Case Else:
            ThisVersionOfExcel = "Excel Unknown Version of " & ExcelVersion
    End Select
'
    MsgBox "You are running " & ThisVersionOfExcel & " build #" & Application.Build & " of " & Application.OperatingSystem & "."
End Sub

I'll get it right sooner or later. I Hope.

Thank you @Dan_W ! Correction made,