It's normally recommended to develop in the oldest version you want your app
to be compatible with, so for you that implies Excel 97, though you would
also want to test, and possibly adapt, in later versions. VBA6 was
introduced in XL2000 which includes several new vba functions that help
speed things up and avoid workarounds, you might want to do something like
this -

#If VBA6 then
s = Replace(etc)
#Else
s = application.worksheetfunction.Substitute(etc)
#End If

Each successive Excel version includes new functions and/or functions with
additional optional arguments. If you want to take advantage of these you
will need to cater for app.version, putting later version stuff in
procedures (ideally in dedicated modules) that will never be called by
earlier versions.

There are a few particular issues with vba in XL97 that were resolved in
later versions, relatively not too many.

Head all you modules with Option Explicit and fully declare all object
variables. Do a debug compile in each version and go on to fully test in
each.

It might be worth making a separate version for XL2007. There have been many
posts in this ng highlighting differences and problems to overcome with
certain aspects.

If you want to secure your code, as mentioned security is minimal in VBA,
consider a VB6 dll as suggested by Charles Williams. If this is to use Early
Binding, ensure the reference is set to the earliest version. The same dll
could be a Com Addin for XL2000+ and an ordinary ActiveX for use in XL97
with a VBA wrapper. Or, if your app is a full program that uses Excel,
perhaps a VB6 exe.

Regards,
Peter T


"SummitXLer" wrote in message
oups.com...
I am creating a new Excel-based program (see www.PKTutor.com for a
preliminary demo). Given the plethora of Excel vesrions out there, I
am interested to know which version is regarded as best for modern
development of commercial products. Backward compatibility seems
pretty good with v.11 and v.12. Since this will be an international
educational and tutorial-type interactive program, I need good
compatibility all the way back to Excel 97 and into the future. Also,
I won't need to use XML. I can develop or test on almost any XL
version from Excel 97 onwards. Your comments (pros and cons) will be
greatly appreciated. For an example of my current Excel product for
medical/pharmaceutical/pharmacy data analysis (available in Excel 97
and XP), see www.SummitPK.com

Much thanks in advance.