{Sturgeon’s Law} “Sure, 90% of all software is crap. That’s because 90% of everything is crap.” [Mary Shaw, Carnegie-Mellon University]

Abstract

Microsoft has decided in its eternal wisdom not to increase Application.Version beyond 16 from Excel 2016 on.

My function ApplicationVersion fixes this and returns a human readable version string.

Appendix – ApplicationVersion Code

Please read my Disclaimer.

Option Explicit

Function ApplicationVersion() As String
'Returns MS Excel's version - with a little kludge
'Source (EN): http://www.sulprobil.com/applicationversion_en/
'Source (DE): http://www.bplumhoff.de/applicationversion_de/
'(C) (P) by Bernd Plumhoff 19-Jan-2022 PB V0.3
Dim n As Integer
n = Val(Application.Version)
Select Case n
Case 16
    ApplicationVersion = "Excel 2016"
    'Excel 365 introduced Lambda - but this test does not seem to be
    'sufficient so far.
'    On Error Resume Next
'    ThisWorkbook.Names.Add Name:="HasLambda", RefersTo:="=LAMBDA(x,x)"
'    n = Evaluate("HasLambda(19)")
'    ThisWorkbook.Names("HasLambda").Delete
'    On Error GoTo 0
'    If n = 19 Then
'        ApplicationVersion = "Excel 365"
'    Else
        'Excel 2021 introduced RandArray
        On Error Resume Next
        n = Application.WorksheetFunction.RandArray(1, 1, 18, 18, True)(1)
        On Error GoTo 0
        If n = 18 Then
            ApplicationVersion = "Excel 2021/365" '"Excel 2021"
        Else
            'Excel 2019 introduced TextJoin
            On Error Resume Next
            n = Val(Application.WorksheetFunction.TextJoin(" ", True, "17"))
            On Error GoTo 0
            If n = 17 Then ApplicationVersion = "Excel 2019"
        End If
'    End If
Case 15
    ApplicationVersion = "Excel 2013"
Case 14
    ApplicationVersion = "Excel 2010"
Case 12
    ApplicationVersion = "Excel 2007"
Case 11
    ApplicationVersion = "Excel 2003"
Case 10
    ApplicationVersion = "Excel 2002"
Case 9
    ApplicationVersion = "Excel 2000"
Case 8
    ApplicationVersion = "Excel 97"
Case 7
    ApplicationVersion = "Excel 7/95"
Case 5
    ApplicationVersion = "Excel 5"
Case Else
    ApplicationVersion = "[Error]"
End Select
End Function