How to Get Excel version using VBA Code

.

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 🙂
 

Version Number Version Name
8 Excel 97
9 Excel 2000
10 Excel 2002
11 Excel 2003
12 Excel 2007
14 Excel 2010
15 Excel 2013
16 Excel 2016

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 !!

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

4 Comments

  1. Bhavik

    Don’t you need Case Else statement “Excel Unknown Version” to future proof the Excel UDF

    Bhavik

    Reply
    • Vishwamitra Mishra

      thanks Bhavik for the feedback… here you go with the else statement… I appreciate your suggestion 🙂

      Reply
  2. Bhavik

    Thank you for sharing your code with everybody.

    Reply
  3. hassan

    TNX…My Friend

    Reply

Submit a Comment

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.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest