macro to tell people they are not using the correct version of excel

[tonywatsonhelp]

Hi Everyone,
I'm not quite sure how or if it would be possible to do this but I have problems sharing my documents with others as sometimes the functions I've used like the new filter formula do not work on their version of excel,
fortunatly this is getting less and less but trying to explain their version of excel is not new enough is a big pain,
so I was wondering if there was a way when excel opens to run a macro that checks to see if the version of excel is new enough? now as I understand it, it doesn't have to be the very newest version so I'm not sure how we could check this but any ideas would be a great help
Thanks
Tony

 

[Micron]

By version you mean the likes of 2016, 2013 etc? Then maybe use Application.Version ?

 

[HaHoBe]

Hi Micron,

AFAIK 2016, 2019, 2021, and 365 will all indicate 16.

Maybe use the Build-Number_

Public Sub test()
'https://stackoverflow.com/questions/31718490/finding-ms-office-revision-and-build-version-using-vba
  Dim version As String
  Dim chkref As Object
  Dim major As String
  Dim majorup As String
  Dim minor As String
  Dim minorup As String

  ' List of references
  For Each chkref In ThisWorkbook.VBProject.References
    version = RetrieveDllVersion(chkref.fullpath)
    major = RetrievePart(version, 0)
    majorup = RetrievePart(version, 1)
    minor = RetrievePart(version, 2)
    minorup = RetrievePart(version, 3)
    If chkref.Name = "Excel" Then
      MsgBox chkref.Name & " : " & major & "." & majorup & "." & minor & "." & minorup
    End If
  Next chkref
End Sub

Private Function RetrieveDllVersion(ByVal dll As String) As String
  Dim fso As Object 'Scripting.FileSystemObject
  Set fso = CreateObject("Scripting.FileSystemObject")
  RetrieveDllVersion = fso.GetFileVersion(dll)
End Function

Private Function RetrievePart(ByVal version As String, ByVal pos As Integer) As String
  RetrievePart = Split(version, ".")(pos)
End Function

Holger

 

[Fluff]

This will show if the user has 2021 or 365

Private Sub Workbook_Open()
   Dim x As Variant
   
   On Error Resume Next
   x = Application.Sequence(10)
   On Error GoTo 0
   If IsArray(x) Then
      MsgBox "Ok"
   Else
      MsgBox "Too old"
   End If
End Sub

 

[tonywatsonhelp]

Brilliant, thank you to all of you for your help,
fluff, i think your idea is brilliant,
thanks
Tony

 

[Fluff]

Glad we could help & thanks for the feedback.