Howto develop VBA applications across different office versions

[claws]

Hi everybody

I often need to develop, e.g. an access application, which needs to
open Word or Excel. This in it self is straight forward. However, more
often than not, the users of the database have different versions of
office installed, so the reference made to e.g. Outlook 11.0 is not
sufficient.
1) How do I develop version-independent?
2) Are Office applications all backward compatible so that I can
develop for the lowest version, and be sure that all later
versions will work for this? Then I guess, I could use
CreateObject for late-binding, or?

Thanks a lot

 

[Scott Holmes via OfficeKB.com]

How to handle different versions depends on whether the version can interpret
the VBA code.

1. If the version can interpret the VBA code (attempting to run does not
cause errors), then do something like:

If (intWordVersionNumber = 8) Then
'Word 97, so do this code...

ElseIf (intWordVersionNumber = 9) Then
'Word 2000, so do this code...

Else
'Word 2002 or later, so do this code...

End If

Public Function intWordVersionNumber() As Integer
'Note that only Word versions 8 and higher support VBA.
' 6 = Word 6
' 7 = Word 95
' 8 = Word 97
' 9 = Word 2000
'10 = Word 2002
'11 = Word 2003
intWordVersionNumber = Val(Application.Version)
End Function


2. If the version cannot interpret the VBA code, then you must use
conditional compiler directives to keep the compiler from seeing code it
can't handle. Use the "#If...Then...#Else Directive". To get information
from within the VBA editor, type and select "#If" and then hit the F1 key for
information. An example:

#If (VBA6) Then 'Conditional compilation
'Handle Word 2000 or later.
Set docSource = Documents.Open(FileName:=sFileFullNameToInsert,
AddToRecentFiles:=False, Visible:=False)
#Else
'Handle Word 97.
Set docSource = Documents.Open(FileName:=sFileFullNameToInsert,
AddToRecentFiles:=False)
docTarget.Activate 'Another way to hide docSource -- W97 can't
handle the Visible parameter.
#End If


3. Another way is to put code for different versions in separate code
modules. Then use the approach in (1) and call the appropriate procedures.
VBA does not check the code in modules not called! Example:

If (intWordVersionNumber = 8) Then
Call modWord97.ProcedureName

ElseIf (intWordVersionNumber = 9) Then
Call modWord2000.ProcedureName

Else
Call modWord2002orLater.ProcedureName

End If