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" wrote in message
...
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