Using com add-in vs digital certs for app

[VanS]

Hello,
I have an app I am going to commercially distribute consisting of a VB6
front end with the back end of a VBA Excel 2003 app using automation and
also VBA Word. I was trying to see if it was possible and advisable to avoid
using digital certificates for the macros. One newsgroup respondent suggested
creating a com add-in in VB6 such as described in
http://www.cpearson.com/excel/CreatingCOMAddIn.aspx.
I am not at all familiar with this method so I was wondering if anyone has
good experience and knowledge of such and the following:
1. Is this a feasible and efficient technique for avoiding having to get
digital certificates?
2. From skimming some of the articles, do I understand correctly that I can
create an exe with such not only enhancing performance but security of the
VBA code?
3. Can I put my entire-and fairly large Excel and Word VBA project into a
com add-in to accomplish the above?
4. Since this will be a widely distributed commercial app, will I be able to
automate the entire installation process with a good installer like Wise
Installer including changes to the registry?
I appreciate any and all help. Thanks and God bless,
Van

 

[Chip Pearson]

A COM add-in is distributed as a DLL of compiled code, so the issue of
a digital signature doesn't enter into the equation. If the user loads
an CAI, it is assumed that he (1) wants to do that, and (2) trusts the
code and the vendor.

2. From skimming some of the articles, do I understand correctly that I can
create an exe with such not only enhancing performance but security of the
VBA code?

VBA and COM Add-Ins are largely independent from one another. A CAI
can call any function of Excel, and Excel VBA code can call methods in
the CAI but the CAI can be written so that there is no interaction
between the VBA project and the CAI. The existence of a CAI does not
imply the existence of VBA. Most of what I write commercially is in
the form of CAIs in either VB6 or VB.NET, with little, if any, VBA
code. As for the security of VBA code, this has nothing to do with a
CAI. Any VBA code that exists can still be protected (for what its
worth) and unprotected by someone who knows how to do so. But since a
CAI is compiled code in a DLL, the source code is never distributed,
so protection is not an issue. If you need any VBA code to bridge
between a workbook's project and the CAI, write that code such that it
doesn't matter if a user sees it. By and large, CAIs are used as
substitutes for VBA, for reasons of security (no source is
distributed), speed (CAIs are machine code), and maintainability
(changes can be made to the CAI and then distributed to users and the
users need not change existing workbooks that reference the CAI). A
CAI is a DLL, not an exe, and cannot be used instead of Excel as a
whole or as a worksheet. It must be referenced by a host application,
such as Excel or Word.

3. Can I put my entire-and fairly large Excel and Word VBA project into a
com add-in to accomplish the above?

Yes, but you can't simply copy/paste code out of VBA and paste it into
a VB6 or VBNET based CAI. You'll have to rewrite some code, or at
least make some modifications. The example on the page you cite
provides an example of a single CAI that supports both Excel and
PowerPoint. You can replace the code that references PPT with code
that references Word.

4. Since this will be a widely distributed commercial app, will I be able to
automate the entire installation process with a good installer like Wise
Installer including changes to the registry?

Yes, it is just like distributing any other applications or DLLs. I
use either SetupFactory or AdvancedInstaller to create setup files for
CAIs. In VB6, if you create a CAI using the "Add In" template, which
uses the Microsoft Add-In Designer library, the compiled code will
write all the requisite registry keys to fully install the CAI, as far
as Windows/Excel is concerned. You need only create keys that are
needed by your CAI.

If you are unfamiliar with writing code that access the Registry, see
http://www.cpearson.com/excel/Registry.htm and
http://www.cpearson.com/excel/registryworx.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)