Dear Friends,

Usually while working on any of the VBA projects, it becomes important for me to first check the version of excel which user is using and based on that certain functionality of my tool may or may not work. For Example: If you have some sales data using more than 256 columns then if user is using Excel 2003, then it would not be possible as Excel 2003 has got only 256 columns. But if user is using any higher version like Excel 2007 etc. then it is possible to do so without any error as they have got 16000+ columns in it. This way before processing the data if you can identify the version of the excel user is using then you will be able to handle the error without crashing the excel.

As user I would always prefer a clear message stating that “this functionality is not available in so and so Excel version” rather than getting some weird Microsoft error message and messing up other functionalities as well.

It is a very simple piece of code which will determine which version of excel user is using. Whenever you call the bellow function at any moment of time when it is necessary to check the version of the excel.

Function GetVersion() As String
    Dim verNo As Integer
    verNo = VBA.Val(Application.Version)
    Select Case verNo
        Case 8:
        GetVersion = "Excel 97"
        Case 9:
        GetVersion = "Excel 2000"
        Case 10:
        GetVersion = "Excel 2002"
        Case 11:
        GetVersion = "Excel 2003"
        Case 12:
        GetVersion = "Excel 2007"
        Case 14:
        GetVersion = "Excel 2010"
        Case 15:
        GetVersion = "Excel 2013"
        Case 16:
        GetVersion = "Excel 2016"
        Case Else:
        GetVersion= "Excel Unknown Version”
    End Select
End Function

Important Note:

VBA.Val(Application.Version) returns a number. Each version of the excel can be determined by the following mapping of Version Number and name. Same mapping I have put in the above code:
 
Do not ask me why version number 13 is missing from the sequence 🙂
 

How to use this function?

Step 1. Copy the above code
Step 2. Create a publich module in your VBA project
Step 3. Paste this code there
Step 4. Now you are ready to call this function anywhere in your workbook
you can also call this function from you excel cell as a formula and it will return the version of the excel you are using currently.
Enjoy writing full proof VBA code without any unknown error related to excel version. Keep looking in this space for many more such small tips and tricks in VBA.

Have a nice weekend !!