VBA test for finding Excel version?

9tanstaafl9

Well-known Member
Joined
Mar 23, 2008
Messages
535
Is there a way in vba to test to see what version of Excel is being used?
My macro would crash if their data would exceed the 65K row limit in Excel 2003. I have a message that pops up if they do that, and it handles the problem just fine, but if they are ALREADY using Excel 2007 (or whatever comes later) I don't want the message to appear.

How do I test?

Jennifer
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

vicsar

New Member
Joined
May 5, 2013
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
What happened to version 13???? Another MS conspiracy?!?!!? :eek:

I know this is an old post but:


  • No MS-DOS version of Excel 1.0 for Windows ever existed: the Windows version originated at the time the Mac version was up to 2.0.
  • There is no Excel 6.0, because the Windows 95 version was launched with Word 7. All the Office 95 & Office 4.X products have OLE 2 capacity — moving data automatically from various programs — and Excel 7 would show that it was contemporary with Word 7.
  • And finally: Version number 13 was skipped due to superstition (Triskaidekaphobia).

True story.
 

vicsar

New Member
Joined
May 5, 2013
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
  2. Web


I know this is an old post but:


  • No MS-DOS version of Excel 1.0 for Windows ever existed: the Windows version originated at the time the Mac version was up to 2.0.
  • There is no Excel 6.0, because the Windows 95 version was launched with Word 7. All the Office 95 & Office 4.X products have OLE 2 capacity — moving data automatically from various programs — and Excel 7 would show that it was contemporary with Word 7.
  • And finally: Version number 13 was skipped due to superstition (Triskaidekaphobia).

True story.

-By the way here is some more information in case you are curious:
Excel Versions
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,978
Office Version
  1. 2016
Platform
  1. Windows
The real pity about the original question is the OP did not get the information she should have gotten. Her stated reason for wanting to know the version of Excel in use was this... "My macro would crash if their data would exceed the 65K row limit in Excel 2003". There is no need to get the Excel version number in order to write code that protects against this problem... while we don't know the exact test she would have needed to perform for her given application, using Rows.Count to find out how many rows were available in the version of Excel running her macro would surely have been enough.
 

NigelF

New Member
Joined
May 17, 2013
Messages
3

ADVERTISEMENT

This thread has been very valuable as I was looking for how to get the Excel version number, but only because I wanted to do a test based on different versions, namely I want to create - that is objWorkbook.SaveAs <filename>,<filetype> - a file that users can access in Excel XP (2002), Excel 2003 and Excel 2007. If I am running 2007 the save-as statement works fine when I use file type 56 (2002 format). But the excel 2002 does not seem to recognise 56 as a valid parameter when I run the code in 2002 - it gives an error and does not save the file. I therefore assume that I need to know which version is being used (hence the value of this thread) and if it is 2002 user file format xlNormal (-4134, is it?) rather than 56? Would that be correct? I need the VBA to run in any of the above versions and creates a file that anyone can open. Thanks for any assistance.
 

mikepfly2

New Member
Joined
Oct 19, 2010
Messages
14
How would one go about getting the detailed version number (including latest upgrade?) See highlighted area below. I'm not asking how to get the release (#14) number. I want to get the whole 14.0.7128.5000. I've scoured the web, but no such luck...

I've found, especially with Office 2013, that those without the latest update pack have strange bugs (especially around printing) that are fixed when they have the latest updates.

Thanks.

10.09.2014-12.44.png
 

NigelF

New Member
Joined
May 17, 2013
Messages
3

ADVERTISEMENT

Thanks for all help with this thread - I eventually found the solution to my problem. The objExcel.version parameter returns 10 for Excel 2002, 11 for 2003 and 12 for 2007. I used this as follows to determine the version being used and setting the correct parameter in the SaveAs to get the format I wanted. I hope it helps someone else. (Note: LF01+LF03 represents the file name in the package we are using)
' check the version of excel being used by this user
' if ver 10 (2002) save the file in Normal format (-4143)
' if ver not 10 (assume 11, 12; 2003 or 2007) save file in 2002 format (56)
If objExcel.version="10.0" Then
objWorkbook.SaveAs LF01+LF03,-4143
Else
objWorkbook.SaveAs LF01+LF03,56
End If
 

mikepfly2

New Member
Joined
Oct 19, 2010
Messages
14
Any thoughts on this? I have to assume it's possible to get this value? Do I have to get into registry values? Any ideas are welcome! Thanks

How would one go about getting the detailed version number (including latest upgrade?) See highlighted area below. I'm not asking how to get the release (#14) number. I want to get the whole 14.0.7128.5000. I've scoured the web, but no such luck...

I've found, especially with Office 2013, that those without the latest update pack have strange bugs (especially around printing) that are fixed when they have the latest updates.

Thanks.

10.09.2014-12.44.png
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
Any thoughts on this? I have to assume it's possible to get this value? Do I have to get into registry values? Any ideas are welcome! Thanks

Came across this thread here during a search to solve the same problem, i.e. how to get more specific info. Found an answer at StackOverflow. I don't know who this Maxime Porté chap is, but in my estimation he's a stud. (y)

I realize it's been almost three years since mike asked this question, but thought I'd share the solution in case mike or anyone else doesn't come across the solution at stackoverflow.

Code:
'——————————————————————————————————————————————————————————————————————————————
'// solution taken from here:
'// https://stackoverflow.com/questions/31718490/finding-ms-office-revision-and-build-version-using-vba

Public Sub TestBuildVersion()
    Dim version As String
    Dim chkref As Object, Major$, MajorUp$, Minor$, MinorUp$

    ' 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)
        MsgBox chkref.Name & " : " & Major & "." & MajorUp & "." & Minor & "." & MinorUp, vbInformation
    Next
End Sub

Private Function RetrieveDllVersion(ByVal dll As String) As String
  Dim fso As Object 'Scripting.FileSystemObject
  Set fso = CreateObject("Scripting.FileSystemObject")
  On Error Resume Next
  RetrieveDllVersion = fso.GetFileVersion(dll)
  If Err.Number <> 0 Then Let RetrieveDllVersion = "00.00.00.00"
End Function

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

Oh, and BTW, in case anyone should need them, when I'm testing just the main versions, I just use my own set of enumerated constants:
Code:
Public Enum ge_XLVersions
    verExcel5 = 5           '//    released 1993
    verExcel95 = 7          '//    released 1995  (there is no Excel version 6)
    verExcel97              '//  8 released 1997
    verExcel2000            '//  9 released 1999
    verExcel2002            '// 10 released 2001
    verExcel2003            '// 11 released 2003
    verExcel2007            '// 12 released 2007
    verExcel2010 = 14       '//    released 2010  (there is no Excel version 13)
    verExcel2013            '// 15 released 2012
    verExcel2016            '// 16 released 2016
End Enum
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,840
Messages
5,638,666
Members
417,041
Latest member
Molo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top