Determining Office Version

  • Thread starter Thread starter lance-news
  • Start date Start date
L

lance-news

How do I determine the version of Excel one is using?

I am opening a text file and found out that the code is different
for Office 97 and Office XP. So I now need an IF statement

If (XLversion = 97) Then

End iF

If (XLversion = XP) Then

End If


Lance
 
Try this

Sub test()
If Val(Application.Version) = 8 Then
MsgBox "97"
Else
MsgBox "2000-2003"
End If
End Sub
 
try:-


ver = Application.Version

select case ver
case 10
' code for XP here
case 9

case else

end select
 
You received information on how to determine the version, but I would
suspect you would be better off using the code for xl97. If you have
arguments for a later version and try to compile the code in xl97, you will
still get an error. This is more problematic with xl2000 and xl2002 since
both are VBA6 and conditional compilation can't identify the difference.
 
I guess I'm out of luck too?

I have found that there are two different strategies needed to disable Excel's built-in menu/toolbar buttons depending upon Excel version. In Excel 2002, the property CommandBars.DisableCustomize must be used to prevent re-enabling the buttons through customization, thanks to changes including the new Toolbar Options feature and loss of the "Well" command bars in xlVBA. CommandBars.DisableCustomize does not exist in earlier versions (Excel 2000) and a different strategy is needed to accomplish the same result.

Unfortunately the Excel 2000 VBA compiler barfs when it sees CommandBars.DisableCustomize. Conversely (and perhaps worse), using the code that works in 2000 leaves a gaping back door open in 2002. There appears to be no way to identify the Excel or Office version in conditional compilation directives to prevent this.

Or have I missed something?

Thanks.

Jim
 
I've lost the original post in this thread, but you could check the
application.version to see what version of excel you're running:

Option Explicit
Sub test()
If CDbl(Application.Version) >= 10 Then
Call SubToDisableCustomize
Else
'do your earlier version stuff
End If
End Sub

Then in a module that contains nothing but these lines:

Option Explicit
Sub SubToDisableCustomize()
Application.CommandBars.DisableCustomize = True
End Sub

The second module won't compile in earlier versions of excel, but it won't be
run either--so it doesn't need to.

And Peter Beach had a neat idea to use late binding to a similar question:
http://groups.google.com/groups?threadm=#zeczIyqCHA.2488@TK2MSFTNGP12

Sub a()
Dim o As Object

Set o = Application

On Error Resume Next
o.CommandBars.DisableAskAQuestionDropdown = True
If Err.Number = 0 Then
MsgBox "2002"
Else
MsgBox "not 2002"
End If
End Sub


=====
You would change that to
o.commandbars.disablecustomize = true
 
Thanks for your response, Dave.

I know about Application.Version. Problem is that
CommandBars.DisableCustomize doesn't exist in Excel 2000
VBA, but I need it when the workbook is hosted in Office
XP. I can force the 2000 compiler to ignore that code if
I use conditional compilation with compiler directives:
#If...#Then...#Else...#End If (new in Excel 2000.)
Otherwise, 2000 VBA throws an error just at the sight of
CommandBars.DisableCustomize, before any code in the
module even executes.

Unfortunately, application.version doesn't work with the
compiler directives. The directives work only with hard
constants, and there aren't any built-in constants for
Excel/Office version that I can find.

The solution I have found is somewhat convoluted,
involving the use of Add-In methods to load (and later on
unload) part of the VBA code from a file or a worksheet
into a code module after editing it to set a conditional
compilation constant based on application.version. It's
not pretty, but it does meet the objective of a single
code base and so far it seems to work.

Jim Gross
-----Original Message-----
I've lost the original post in this thread, but you could check the
application.version to see what version of excel you're running:

Option Explicit
Sub test()
If CDbl(Application.Version) >= 10 Then
Call SubToDisableCustomize
Else
'do your earlier version stuff
End If
End Sub

Then in a module that contains nothing but these lines:

Option Explicit
Sub SubToDisableCustomize()
Application.CommandBars.DisableCustomize = True
End Sub

The second module won't compile in earlier versions of excel, but it won't be
run either--so it doesn't need to.

And Peter Beach had a neat idea to use late binding to a similar question:
http://groups.google.com/groups?threadm=% 23zeczIyqCHA.2488%40TK2MSFTNGP12

Sub a()
Dim o As Object

Set o = Application

On Error Resume Next
o.CommandBars.DisableAskAQuestionDropdown = True
If Err.Number = 0 Then
MsgBox "2002"
Else
MsgBox "not 2002"
End If
End Sub


=====
You would change that to
o.commandbars.disablecustomize = true
Click to expand...
needed to disable Excel's built-in menu/toolbar buttons
depending upon Excel version. In Excel 2002, the
property CommandBars.DisableCustomize must be used to
prevent re-enabling the buttons through customization,
thanks to changes including the new Toolbar Options
feature and loss of the "Well" command bars in xlVBA.
CommandBars.DisableCustomize does not exist in earlier
versions (Excel 2000) and a different strategy is needed
to accomplish the same result.it sees CommandBars.DisableCustomize. Conversely (and
perhaps worse), using the code that works in 2000 leaves
a gaping back door open in 2002. There appears to be no
way to identify the Excel or Office version in
conditional compilation directives to prevent this.
 
Jim,

You can use Conditional Compilation combined with CallByName to
do what you want. For example,

#If VBA6 Then
If CInt(Application.Version) > 9 Then
' Excel 2002 or 2003
CallByName Application.CommandBars, "DisableCustomize",
VbLet, True
Else
' Excel 2000
End If
#Else
' Excel 97
#End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top