Check the application version in Modern Office

In the good old days, it was easy to check the application version in Office with VBA.  You just used a little test of Val(Application.Version) to return the number.  12 was Office 2007, 14 was Office 2010, 15 was Office 2013, and 16 was Office 2016.  But then Office 365 came out, and 2019, and things fell apart.

Conducting a check of the application version in Modern Office is not as straight forward.  From Office 2016 onwards, Microsoft has not revved the Application.Version number - they all show as 16.0 - giving you no way to differentiate between versions.  (Bastien discusses this in a blog post a few months ago.) But worse, while he focuses on 2016 vs 2019, there is also no way to test between these and Office 365 subscription versions.  As there are now things that work differently for Office 365 than the perpetual licenses, this is another potential problem for developers.

This past week I ran into a scenario where I needed to do exactly this.  I needed to find a way to programatically enumerate whether a user is running Office 2016, Office 2019 or Office 365, as I had to do something different in each case.

So how can we check the application version in modern Office?

After doing a little digging, I finally found a registry key that seems to appear in Office 2019 and Office 365, but does not exist in Office 2016.  So that was good news. And even better, that key holds values like "O365ProPlusRetail" vs "Office2019ProfessionalPlus".  While I haven't tested with other SKUs, this would seem to indicate a pattern I hope we can rely on.

Given that, I've pulled together this function.  It's purpose is fairly simple: Test the application and see if it is a perpetual license or a subscription install, and return the version number.  So anyone with Office 365 installed should receive 365 as a return, otherwise you'll get a four digit number representing the version you have installed.

Function to check the application version in Modern Office

Function AppVersion() As Long
'Test the Office application version
'Written by Ken Puls (www.excelguru.ca)

Dim registryObject As Object
Dim rootDirectory As String
Dim keyPath As String
Dim arrEntryNames As Variant
Dim arrValueTypes As Variant
Dim x As Long

Select Case Val(Application.Version)

Case Is = 16
'Check for existence of Licensing key
keyPath = "Software\Microsoft\Office\" & CStr(Application.Version) & "\Common\Licensing\LicensingNext"
rootDirectory = "."
Set registryObject = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & rootDirectory & "\root\default:StdRegProv")
registryObject.EnumValues &H80000001, keyPath, arrEntryNames, arrValueTypes

On Error GoTo ErrorExit
For x = 0 To UBound(arrEntryNames)
If InStr(arrEntryNames(x), "365") > 0 Then
AppVersion = 365
Exit Function
End If
If InStr(arrEntryNames(x), "2019") > 0 Then
AppVersion = 2019
Exit Function
End If
Next x

Case Is = 15
AppVersion = 2013
Case Is = 14
AppVersion = 2010
Case Is = 12
AppVersion = 2007
Case Else
'Too old to bother with
AppVersion = 0
End Select

Exit Function

ErrorExit:
'Version 16, but no licensing key. Must be Office 2016
AppVersion = 2016

End Function

If you'd prefer to just download a workbook with the code in it, here you go.

Care to help me test it?

I'd love it if people could give this a try and see if it returns correctly based on the versions of Excel you're running, particularly if you have a flavor of Office 365 or Excel 2019.

Let me know how it goes!

EDIT:  I have made a small change to the code and sample file in case "O365" is not at the beginning of the registry key.  This should pick it up no matter where in the key the 365 term shows up.  I am starting to wonder if this key is only present for Insiders.  So if you do test, please let us know what channel you are on in addition to whether or not it works!

13 thoughts on “Check the application version in Modern Office

  1. Ben & XLarium,

    Are you Office Insiders, or running a different channel? I'm wondering if that registry key is only present for Insiders...

  2. Hey Ben,

    So... what version of Office 365 (as in Home & Office, Professional Plus, etc..)? Can you check again? I've just updated the code to test if 365 is anywhere in that reg key, rather than just at the beginning.

  3. @Ken, my subscription is running on the monthly channel - not on Office Insider.
    Version 1901 Build 11231.20130 click-and-run

    No change with the modified code.

  4. It didn't work for me either. I'm on Office 365 ProPlus (Monthly Channel) and it reports 2016.

    I checked in regedit and I don't have the key you are looking for "Software\Microsoft\Office\16.0\Common\Licensing\LicensingNext"

    I did notice one called "HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Licensing\LastKnownC2RProductReleaseId", however, that has a value of "O365ProPlusRetail"...you might want to take a look at that one.

  5. I did also a check on 3 older Excel versions which are installed on virtual machines:

    Office Professional Plus 2013 - Function returns 2013
    Office Professional Plus 2010 - Function returns 2010
    Office Enterprise 2007 - Function returns 2007

  6. I'm running Office 365 Personal and not on the Insider stream. The file I just downloaded at 4:35est reports that I am running Excel 365.

  7. Rob, that's awesome info. I'm checking with someone else who also doesn't have the key I targeted to see if they have the one you mentioned.

  8. Hi Ken. Function works fine for me.
    I'm using Office 365 ProPlus, monthly channel, version 1901 Build 11231.20130 click-to-run
    "O365ProPlusRetail" in the registry.
    Function returns 365.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.