AddIn Project for Excel

  • Thread starter Thread starter Craig Buchanan
  • Start date Start date
C

Craig Buchanan

What is the value of the VB6 Addin project over a regular COM object if I
simply want to expose 6 functions for use in Excel?

It appears that I need to create 6 stub functions in Excel in either case.

I could simply build all of the logic in Excel (instead of the two options
above), but I would like to secure the code.

Can anyone share their insights on this matter?

Thanks,

Craig Buchanan
 
Excluding the Function question, you may care to consider
the security question.

It is my understanding that VB6 offers greater protection
than any Excel version.

Excel addins can be circumvented via password breaking
programs (readily available on-line).

On the other hand, VB6 / COM Add Ins can be de-compiled
(I believe).

I guess it's down to how valuable you perceive your code to
be.

Ultimately, I suspect you will lose your code.

Regards.
 
Putting the code within VBA is an option, the advantage of this is that it
is for a lot of people the most intuitive form of VB, largely due to the
facility to record macros and then view them (even though the macro recorder
doesn't always output the most efficient, robust code), together with the
fact that your code is always compiled on demand and can be debugged easily.
A VBA project also exists as an entity that is attached to a workbook,
giving you the added intuitiveness of storing data and code that are related
together - the utilisation of this feature is likely the biggest reason for
using an Excel-VBA project. This can also be seen as a disadvantage however,
as you have to create a workbook in order to have a VBA project, so if for
example you've got code that you might want to use whatever workbook you're
in, you always have to open the workbook containing the code you want to
run. This is where you would be most likely to be best served by building an
Excel Add-In with VB6. If you want to create an Excel add-in using VB6 like
this, it is compiled as a DLL which once installed on a system will take on
the relevant behaviour as set in the designer window of 'Startup', 'On
Demand', etc. It can be loaded (on demand) by customizing the toolbars, then
adding the COMAddins icon from the tools section. Another three things
you'll need to do:
a) Change the Application property drop down in the Connect module's
designer window to MS Excel
b) Change the declaration
Public VBInstance As VBIDE.VBE
to
Public WithEvents xlApp as Excel.Application
and update the related code. The Application object that gets passed to the
OnConnection event will then be an Excel.Application when Excel loads the
addin.
c) Change the code that adds an icon to the toolbar to check whether it's
already there first - this is clearly something they overlooked when
designing the template!
Then you can compile it and test it by clicking the COMAddins button in
Excel and loading it (if you didn't set it to startup automatically). You
can apparently debug a COM Addin by putting it in run mode, and then loading
it from Excel.
You can apparently also write an Excel Addin by going into Excel, creating
some VBA code and saving it as an .xla file.
You can create a standard ActiveX DLL, but this is more for when you're
offering a set of 'programmer's functions' rather than 'analysts functions'
that can just be invoked from a menu command, as, say, you might be able to
when creating an add-in.
 
Craig Buchanan said:
What is the value of the VB6 Addin project over a regular COM object if I
simply want to expose 6 functions for use in Excel?
Click to expand...

In SHORT though, the fact that it can be easily invoked from a toolbar
button. The main beauty of add-ins is that although possibly slightly tricky
and unintuitive to create, they *are* just that - an add-in - you can write
an 'extra-bit' to a standard, useful application like Excel - and seamlessly
integrate it with the running of that application. You can even use them for
patches - I recently had a problem with Word 2000, whereby it used to crash
whenever I used the mouse wheel. No warning, no tolerance - the second you
used the mousewheel on Word it would INSTANTLY throw a GPF. No questions
asked. No amount of installing the latest mousedrivers, using a different
mouse or restarting the machine worked - until I had the idea to write an
add-in that subclasses whatever window has the focus and just see if I could
get it to ignore the WM_MOUSEWHEEL message. I thought it wouldn't work as I
was going to intercept it and pass the CallWindowProc function the
WM_VSCROLL instead - but that didn't work as I couldn't figure out how to
determine the correct parameters for WM_VSCROLL. So I thought, well doing
nothing would be better than crashing. So, trying my luck, I simply put an
'Exit Function' in the case of any WM_MOUSEWHEEL messages, so it exited and
returned zero without calling CallWindowProc. And sure enough to my
amazement, not only did it stop it crashing, but the mousewheel worked
perfectly! And it does anytime the addin is loaded, but not when it isn't -
so it always is, and it's obviously the work of the addin that prevents it
from crashing. My only conclusion is that XP sends messages to parent
windows and child windows, while Windows 2000 (which Word 2000 was designed
to run on) sends messages to only child windows (Word and Excel have three
levels of windows, even though Word 2000 is an SDI app) and I can only
presume that Word 2000 was only programmed to handle messages to one of its
windows, and was getting in some sort of deadlock situation, but Word XP
would be able to handle the multiple messages that its operating system
sends. No prize for guessing what MS's solution to my problem would have
been!
 
how will he 'lose' it?

Stuart said:
Excluding the Function question, you may care to consider
the security question.

It is my understanding that VB6 offers greater protection
than any Excel version.

Excel addins can be circumvented via password breaking
programs (readily available on-line).

On the other hand, VB6 / COM Add Ins can be de-compiled
(I believe).

I guess it's down to how valuable you perceive your code to
be.

Ultimately, I suspect you will lose your code.

Regards.
Click to expand...
 
Ben-

Let me summarize to ensure that I understand the options:

Assumptions:
1). I want to expose 6 functions that can be referenced like any other
worksheet function.
2). The logic in each function is a company asset that I want to protect
as much as possible
3). I want to make the usage/installation of these 6 functions as each as
possible
4). I want to maximize the computational speed of each function.

Options:
1). Write the code in VBA and save it as a .xla (Excel Addin) document.
Benefits:
a). .xla file is easy to distribute and requires no COM
registration.
b). .xla file is listed in the Tools | Add-ins... menu, so usage
is simplified
Compromises:
a). code is compiled on demand, so performance would be less than
compiled code.
b). code is "protected" by a password. cracking this password is
trival.
2). Create a VB6 COM object
Benefits:
a). code is compiled on demand, so performance would be
maximized.
b). code is compiled, which it is more difficult to examine, thus
protecting our intellectual property
Compromises:
a). DLL must be registered in order to be used.
b). code is not listed in the Tools | Add-ins... menu, making
usage more complicated.
c). a stub function must be created in Excel for each function
exposed by the DLL; these stub functions could be saved as an Excel .xla
file.
3). Create a VB6 Addin
Benefits:
a). code is compiled on demand, so performance would be
maximized.
b). code is compiled, which it is more difficult to examine, thus
protecting our intellectual property
c). DLL is automatically loaded in Excel. Functions don't appear
to be automatically added to the list of worksheet functions (this is what
is really would like).
Compromises:
a). DLL must be registered in order to be used.
b). code is not listed in the Tools | Add-ins... menu, making
usage more complicated.
c). a stub function must be created in Excel for each function
exposed by the DLL; these stub functions could be saved as an Excel .xla
file. (or so it appears).

Am I missing anything?

Thanks,

Craig
 
Hi Craig,
Am I missing anything?
Click to expand...

If you're using Excel XP or more recent, 2c is no longer an issue, as
you can instal your COM DLL as an 'Automation Addin', using Tools >
Addins > Automation Addins.

Option 4 is to write the addin using C and Excel's C API and compile it
as an xll addin, which is much more complicated, but gives the best
performance and greatest degree of control over the function's
attributes when viewed in the Function Wizard.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
Back
Top