Excel Version Trap

A

amescha

Hello All,

I am looking for a listing of MS Excel verions (releases). I have searched "all of Microsoft.com" to no avail. Specifically I want to keep a VBA procedure from running if the user has anything less than releases 9.0x or 10.0x. The procedure creates a Pivot Table and Pivot Chart which requires the aforesaid two versions. I was considering using a Select Case statement that would result in an appropriate message box and "Exit Sub" unless a version that handles Pivot Charts is being used. Thanks in Advance!
 
N

Norman Jones

Hi Amescha,

Possibly, something like:

If CDbl(Application.Version) >= 9 Then
'Run your pivot table
Else
'Dont!
End If


---
Regards,
Norman
amescha said:
Hello All,

I am looking for a listing of MS Excel verions (releases). I have searched
Click to expand...
"all of Microsoft.com" to no avail. Specifically I want to keep a VBA
procedure from running if the user has anything less than releases 9.0x or
10.0x. The procedure creates a Pivot Table and Pivot Chart which requires
the aforesaid two versions. I was considering using a Select Case statement
that would result in an appropriate message box and "Exit Sub" unless a
version that handles Pivot Charts is being used. Thanks in Advance!
 
G

Gord Dibben

If Application.Version < "9.0" Then
MsgBox "This utility will not work in your version of Excel. " & _
"You must upgrade to version 9.0.", vbExclamation
End
End If

Gord Dibben Excel MVP
 
T

Thai

Let me repost it:

If application.version < 9 then exit sub
call PVT
..
..
..
..
..
Sub PVT()
'you put your Pivot Table and Pivot Chart things here
'in a seperate Sub
'Because if you include Pivot things up, then Excel 97
wouldn't run
End Sub
 
A

amescha

Gord Dibben said:
If Application.Version < "9.0" Then
MsgBox "This utility will not work in your version of Excel. " & _
"You must upgrade to version 9.0.", vbExclamation
End
End If

Gord Dibben Excel MVP
Click to expand...
Dear Gord,

Well this code works fine on the current platform that I am using, namely excel version 8.0e. I will not have the opportunity to try version 9.0x & 10.0x until next week. Stay tuned.
My question here is that I am given to understand that the application.version
property returns a String. It is obvious that string "values" are being compared here. The question then becomes what happens when I try this on a 10.0x platform. When I type =IF(H3<I3,TRUE,FALSE) on a worksheet and enter 8.0e in cell H3 and 9.0x in cell I3 I see a TRUE but when cell I3 becomes 10.0z or 10 as a number then I see FALSE.
Could you please elaborate if you have the time?
Thanks in advance - again
amescha
 
G

Gord Dibben

Application.Version returns a numeric, not a string.

It does not differentiate between releases(x,y,z etc.)as far as I can tell.

Sub version()
Range("A1").Value = Application.Version
End Sub

=ISNUMBER(A1) returns TRUE

Tested on versions 8.0, 10.0 and 11.0

Don't know how we got from VBA to worksheet Functions....

Your original question was......

I am looking for a listing of MS Excel verions (releases). I have searched
"all of Microsoft.com" to no avail. Specifically I want to keep a VBA
procedure from running if the user has anything less than releases 9.0x or
10.0x. The procedure creates a Pivot Table and Pivot Chart which requires the
aforesaid two versions. I was considering using a Select Case statement that
would result in an appropriate message box and "Exit Sub" unless a version
that handles Pivot Charts is being used. Thanks in Advance!

Gord
 
D

Dave Peterson

I think your test depends on going back to excel's worksheet. And then excel's
parser takes over and sees that entry as numeric.

But if you stay in VBA:
Option Explicit
Sub testme()

Dim myArr As Variant
Dim iCtr As Long
myArr = Array(3.3, "3.3", Application.Version, Val(Application.Version))

Debug.Print "Ctr Val VBA FromWks"
For iCtr = LBound(myArr) To UBound(myArr)
ActiveSheet.Range("a1").Value = myArr(iCtr)
Debug.Print iCtr & "--" & myArr(iCtr) & "--" & TypeName(myArr(iCtr)) _
& "--" & Application.IsNumber(ActiveSheet.Range("a1").Value)
Next iCtr

End Sub
Returned:
Ctr Val VBA FromWks
0--3.3--Double--True
1--3.3--String--True
2--10.0--String--True
3--10--Double--True
 
T

Tom Ogilvy

? application.Version
8.0e

--
Regards,
Tom Ogilvy

Dave Peterson said:
I think your test depends on going back to excel's worksheet. And then excel's
parser takes over and sees that entry as numeric.

But if you stay in VBA:
Option Explicit
Sub testme()

Dim myArr As Variant
Dim iCtr As Long
myArr = Array(3.3, "3.3", Application.Version, Val(Application.Version))

Debug.Print "Ctr Val VBA FromWks"
For iCtr = LBound(myArr) To UBound(myArr)
ActiveSheet.Range("a1").Value = myArr(iCtr)
Debug.Print iCtr & "--" & myArr(iCtr) & "--" & TypeName(myArr(iCtr)) _
& "--" & Application.IsNumber(ActiveSheet.Range("a1").Value)
Next iCtr

End Sub
Returned:
Ctr Val VBA FromWks
0--3.3--Double--True
1--3.3--String--True
2--10.0--String--True
3--10--Double--True
Click to expand...
namely excel version 8.0e. I will not have the opportunity to try version
9.0x & 10.0x until next week. Stay tuned.compared here. The question then becomes what happens when I try this on a
10.0x platform. When I type =IF(H3<I3,TRUE,FALSE) on a worksheet and enter
8.0e in cell H3 and 9.0x in cell I3 I see a TRUE but when cell I3 becomes
10.0z or 10 as a number then I see FALSE.
 
H

Harald Staff

Be careful here folks. Application.Version returns a String value. Sort
strings and you get a list like
1
10
11
2
20

So for Excel versions:

Sub test()
MsgBox "8.0" < "9.0" 'true
MsgBox "9.0" < "9.0" 'false
MsgBox "10.0" < "9.0" 'true
MsgBox "11.0" < "9.0" 'true
End Sub

HTH. Best wishes Harald


amescha said:
Hello All,

I am looking for a listing of MS Excel verions (releases). I have searched
Click to expand...
"all of Microsoft.com" to no avail. Specifically I want to keep a VBA
procedure from running if the user has anything less than releases 9.0x or
10.0x. The procedure creates a Pivot Table and Pivot Chart which requires
the aforesaid two versions. I was considering using a Select Case statement
that would result in an appropriate message box and "Exit Sub" unless a
version that handles Pivot Charts is being used. Thanks in Advance!
 
A

amescha

amescha said:
Hello All,

I am looking for a listing of MS Excel verions (releases). I have searched "all of Microsoft.com" to no avail. Specifically I want to keep a VBA procedure from running if the user has anything less than releases 9.0x or 10.0x. The procedure creates a Pivot Table and Pivot Chart which requires the aforesaid two versions. I was considering using a Select Case statement that would result in an appropriate message box and "Exit Sub" unless a version that handles Pivot Charts is being used. Thanks in Advance!
Click to expand...

I have come up with a solution to my own problem, perhaps not the most elegant but it does work on versions 8.0x, 9.0x and 10.0x, the relevant part of the code is:

Dim VersionString As String
Dim VersionNumber As Double
VersionString = Application.Version
VersionNumber = Val(VersionString)
MsgBox "This is MS Excel version " & VersionNumber
If VersionNumber >= 9 Then
MsgBox "Go ahead and build your Pivot Chart"
Else
MsgBox "Sorry, this version does not support Pivot Charts!"
Exit Sub
End If

Still I welcome further comment,

amescha
 
A

Alan Beban

Is there a problem with eliminating VersionString and VersionNumber and
simply using

If Application.Version >= 9

Alan Beban
 
Top