On Apr 18, 4:23 am, "Peter T" <peter_t@discussions wrote:
It's normally recommended to develop in the oldest version you want your app
to be compatible with, so for you that impliesExcel97, 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 successiveExcelversion 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 tosecureyour 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 usesExcel,
perhaps a VB6 exe.
Regards,
Peter T
Good solutions all, but this is not the best moving forward. You'll
eventually have to embrace .NET and VSTO/Interop...it's just
inevitable. If MSFT was still supporting VB6 and COM (like they EASILY
COULD HAVE), the above would be the best option.