checking Excel version number

M

MarkFL

I am writing a VBA macro in Excel 2000 and would like to check the version number of Excel as a first step. (I have some users with Excel 2000, and others with XP & 2003.) Is there a simple way to do this in VBA code? Thanks.
 
B

Bob Phillips

Mark,

Use

Val(Application.Version)

Walk has a list of them at http://www.j-walk.com/ss/excel/index.htm

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

MarkFL said:
I am writing a VBA macro in Excel 2000 and would like to check the version
Click to expand...
number of Excel as a first step. (I have some users with Excel 2000, and
others with XP & 2003.) Is there a simple way to do this in VBA code?
Thanks.
 
A

Alan Beban

Bob said:
Mark,

Use

Val(Application.Version)

Walk has a list of them at http://www.j-walk.com/ss/excel/index.htm
Click to expand...
Is the Val necessary? Application.Version seems to work. I.e., in my
xl2000
Application.Version < 9 returns False
Application.Version >= 9 returns True
Application.Version > 9 returns False

Are there some pitfalls to using it without coercing to a double?

Alan Beban
 
B

Bob Phillips

Not come across any Alan, just that Help describes Version as a read-only
string. Defensive programming again.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

Harald Staff

Is the Val necessary?
Click to expand...

Definitely. Application.Version returns a string. Except from type
mismatches, "10" is smaller than "9".

?"11">"9"
False

HTH. Best wishes Harald
 
A

Alan Beban

Hi Harald,

I'm not following what you're saying. In my xl2000 (Version 9)

Application.Version = 9 returns True
Application.Version = 11 returns False
Application.Version < 11 returns True and
Application.Version > 11 returns False

What's the problem again with using these to test a Version number?

Alan Beban
 
R

Rob Bovey

Hi Alan,

The real problem (although admittedly I haven't seen it since Excel 97)
is that Microsoft would append letters onto the end of the version number to
indicate the service pack. So if you check Application.Version on Excel 97
SR2 it comes back as "8.0e". This will obviously fail in any numeric
comparisons unless you use Val to coerce it into a number.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
A

Alan Beban

Rob said:
Hi Alan,

The real problem (although admittedly I haven't seen it since Excel 97)
is that Microsoft would append letters onto the end of the version number to
indicate the service pack. So if you check Application.Version on Excel 97
SR2 it comes back as "8.0e". This will obviously fail in any numeric
comparisons unless you use Val to coerce it into a number.
Click to expand...
Thanks, Rob.

Alan Beban
 
M

Martyn

Hi Bob,
Can the code be modified so that it returns a value such as (9.0.2720)
instead of just (9)?
TIA
 
H

Harald Staff

Alan Beban said:
Hi Harald,

I'm not following what you're saying. In my xl2000 (Version 9)

Application.Version = 9 returns True
Application.Version = 11 returns False
Application.Version < 11 returns True and
Application.Version > 11 returns False

What's the problem again with using these to test a Version number?
Click to expand...

Hi Alan

Problem is that version is text, not number. Ran this macro in Excel2003 (v
11):

Sub test()
Dim Counter As Long
On Error GoTo errorprint
Counter = 1
If Application.Version > 9 Then _
Debug.Print Counter, Application.Version & _
" newer than 9"
Counter = Counter + 1
If Application.Version < 9 Then _
Debug.Print Counter, Application.Version & _
" older than 9"
Counter = Counter + 1
If Application.Version > "9" Then _
Debug.Print Counter, Application.Version & _
" newer than ""9"""
Counter = Counter + 1
If Application.Version < "9" Then _
Debug.Print Counter, Application.Version & _
" older than ""9"""
Counter = Counter + 1
If Val(Application.Version) > 9 Then _
Debug.Print Counter, Val(Application.Version) & _
" newer than 9"
Counter = Counter + 1
If Val(Application.Version) < 9 Then _
Debug.Print Counter, Val(Application.Version) & _
" older than 9"
Exit Sub
errorprint:
Debug.Print Counter, Err.Number & " " & Error
Resume Next
End Sub

and it printed this:

1 13 Type mismatch
1 11.0 newer than 9
2 13 Type mismatch
2 11.0 older than 9
4 11.0 older than "9"
5 11 newer than 9

Best wishes Harald
 
A

Alan Beban

Hi Harald,

I ran the following in xl2000 (Version 9.0)

Sub foo2000()
Dim Counter As Long
On Error GoTo errorprint
Counter = 1
If Application.Version > 8 Then _
Debug.Print Counter, Application.Version & _
" newer than 8"
Counter = Counter + 1
If Application.Version < 8 Then _
Debug.Print Counter, Application.Version & _
" older than 8"
Counter = Counter + 1
If Application.Version > "8" Then _
Debug.Print Counter, Application.Version & _
" newer than ""8"""
Counter = Counter + 1
If Application.Version < "8" Then _
Debug.Print Counter, Application.Version & _
" older than ""8"""
Counter = Counter + 1
If val(Application.Version) > 8 Then _
Debug.Print Counter, val(Application.Version) & _
" newer than 8"
Counter = Counter + 1
If val(Application.Version) < 8 Then _
Debug.Print Counter, val(Application.Version) & _
" older than 8"
Exit Sub
errorprint:
Debug.Print Counter, Err.Number & " " & Error
Resume Next
End Sub

and it printed this

1 9.0 newer than 8
3 9.0 newer than "8"
5 9 newer than 8

What's going on?

Alan Beban
 
H

Harald Staff

Alan Beban said:
Hi Harald,

I ran the following in xl2000 (Version 9.0) (snip)
and it printed this

1 9.0 newer than 8
3 9.0 newer than "8"
5 9 newer than 8

What's going on?
Click to expand...

Beats me Alan. This is the result from my XL2000 on norwegian WinXP /
regional settings:

1 13 Type mismatch
1 9.0 newer than 8
2 13 Type mismatch
2 9.0 older than 8
3 9.0 newer than "8"
5 9 newer than 8

I don't have anything but 9 and 11 on this machine, so I can't test further
until tonight.

Best wishes harald
 
H

Harald Staff

And here are the votes from american Excel2000 on norwegian WinXP:

1 13 Type mismatch
1 9.0 newer than 8
2 13 Type mismatch
2 9.0 older than 8
3 9.0 newer than "8"
5 9 newer than 8

American Excel Xp on norwegian WinXP:

1 13 Type mismatch
1 10.0 newer than 8
2 13 Type mismatch
2 10.0 older than 8
4 10.0 older than "8"
5 10 newer than 8

American Excel 2003 on norwegian WinXP:

1 13 Type mismatch
1 11.0 newer than 8
2 13 Type mismatch
2 11.0 older than 8
4 11.0 older than "8"
5 11 newer than 8

So if VAL isn't always necessary on all setups, I'd still say it's the only
reliable method. I have no idea why you don't get as many errors as I
do -except that I'm a pretty unlucky person by default.

Best wishes Harald
 
P

Peter T

Hi all,

I use this:

xlVer = CLng(Left(Application.Version, 2))

Do you think it's reliable for any version, system,
language?

Regards,
Peter
 
Top