Which Excel Version Best for Creating New Applications or Add-ins?

[SummitXLer]

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.

 

[syswizard]

I am creating a new Excel-based program (seewww.PKTutor.comfor 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), seewww.SummitPK.com

Much thanks in advance.

NONE....unless you don't care about your code being stolen.
Listen-up: I've been in communication with Michael Worosz
[[email protected]] who was supposed to be the product
manager of Excel 2007, but (for some strange reason), no longer
is....and he won't provide the name of the new Excel 2007 product
manager....telling. I told him about the need for XLA add-in security
that is hacker-proof. He indicated this was a "new feature". YEAH,
right ! Microsoft is not beefing-up XLA/Add-in code security for Excel
2007 on purpose.
They are (again, they love to push people around !) pushing developers
to go the VSTO which is "secure".
http://msdn.microsoft.com/vstudio/tour/vs2005_guided_tour/VS2005pro/Smart_Client/BuildingUI.htm
Of course, it will cost you about 2-3 years time and a couple of
thousand dollars of books and training before you can build apps at
the same speed as VBA.
Good luck !

 

[Charles Williams]

Use VB6 if you think XLA security is a problem.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

I am creating a new Excel-based program (seewww.PKTutor.comfor 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), seewww.SummitPK.com

Much thanks in advance.

NONE....unless you don't care about your code being stolen.
Listen-up: I've been in communication with Michael Worosz
[[email protected]] who was supposed to be the product
manager of Excel 2007, but (for some strange reason), no longer
is....and he won't provide the name of the new Excel 2007 product
manager....telling. I told him about the need for XLA add-in security
that is hacker-proof. He indicated this was a "new feature". YEAH,
right ! Microsoft is not beefing-up XLA/Add-in code security for Excel
2007 on purpose.
They are (again, they love to push people around !) pushing developers
to go the VSTO which is "secure".
http://msdn.microsoft.com/vstudio/tour/vs2005_guided_tour/VS2005pro/Smart_Client/BuildingUI.htm
Of course, it will cost you about 2-3 years time and a couple of
thousand dollars of books and training before you can build apps at
the same speed as VBA.
Good luck !

 

[Peter T]

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

 

[syswizard]

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.

 

[SummitXLer]

NONE....unless you don't care about your code being stolen.
Listen-up: I've been in communication with Michael Worosz
[[email protected]] who was supposed to be the product
manager of Excel 2007, but (for some strange reason), no longer
is....and he won't provide the name of the new Excel 2007 product
manager....telling. I told him about the need for XLA add-in security
that is hacker-proof. He indicated this was a "new feature". YEAH,vance.

Thanks. I know Excel security is weak - always has been. And piracy is
plentiful with any
Office app. But let me narrow my inquiry. Say, between Excel 2003 and
2007, which would
be the preferred version for development of a new commercial app?
Considerations would be
backward and forward compatability for world-wide distribution, VBE
development environment
and protection, code stability, deployment issues, and the like. I'm
not concerned about ribbons
and sharing, but about stability, longevity, compatability,
usability....and such.

 

[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.

The VB6 suggestion was thrown in, the OP didn't even mention security
(though he has since). However he did mention he wants to support early
versions. .Net won't work in W2k without the gigantic Framework, bit of an
overkill for an addin.

But to your point, how long VB6 will survive is anyone's guess but, as it
works in Vista, presumably it will until the next generation of Windows at
least.

I was rather surprised to learn recently that MS still sell VB6 via MSDN
Subscriber Downloads, though not VS. FWIW there is also speculation as to
how long .Net will survive.

Regards,
Peter T

 

[Charles Williams]

a VBA solution will be supported by MSOffice for many years to come: even
XLM is still supported in Excel 2007 and that was obsoleted by VBA many
years ago (1993) in Excel 5 .

I dont see the point in trying to develop an Excel addin for .net and VSTO
until they are properly supported and functional under Office: I believe
VSTO is currently targeted at a different marketplace.

Proper .net/VSTA support may or may not happen in the next office version
(14) but I have not heard any public Msoft announcements to that effect: it
would require a major development effort by MSoft that might not be
cost-effective for them.

On the other hand I believe there is a public statement that VBA will be
supported in the next Office version.

If you want a solution that supports Excel 97 through Excel version 14 I
personally dont see much choice at the moment other than VBA or VB6 or a C++
XLL.
If your marketplace is large corporates then you can probably drop Excel 97,
but I don't think that would make much difference.

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.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com