C++ Com Addins for Excel

[Frank]

I'd like to write some addins to replace some user-defined
functions I created in VBA for Excel by C++ COM. Now that
I have all MSDN universal softwares, I want to know how to
do this. Any suggestions?

My needs:
1. The add-ins will be installed on PCs with Excel 2000
only, without additional VS, VB, VC VSOT tools.
2. VSOT for Excel 2003 can't be used.
3. Want to use VC++, because VB is not fast enough.

The problem is that there is very limited references on
this subject. I searched MSDN discussions and technical
papers, and found the following with questions:

1. Most VC++ Add-ins focus on Office 97 with VS6 as tools,
and are probably dated. But that is where most the
discussions are. I have no problem using VS6 as tools.

2. More recent and sigificantly newer tools are for Office
2000. There is a VB Programmer's Guide. But less
discussions on VC++ as a tool rather than using VB with
Office 2000 Developer.

3. More recently, there is Office XP Developer. Is this
much better than Office 2000 Developer? Is it true that
both are for VB as a tool, not for VC++ or other tools?
Can I use Office XP Developer to create C++ Addins and
install them for Excel 2000?

4. Officially, Office 2000 and XP Developers have been
discontinued, due 2001 legal settlement. I own them so I
can still use them.

5. Visual Studio Tool for Office (VSTO) from VS . Net 2003
is supposed to replace the Office Developers, but can not
be used for anything other than Office 2003 Pro, which
really limited it use. Correct?

 

[Yury Lobanov]

Frank, I don't know how to write COM objects in C++ for excel,
but you can easyly transfer your VBA code to VB(ActiveX Dll) and it will
work a level faster.

Yury

 

[Jens Thiel]

I'd like to write some addins to replace some user-defined
functions I created in VBA for Excel by C++ COM.
My needs:
1. The add-ins will be installed on PCs with Excel 2000
only, without additional VS, VB, VC VSOT tools.

This limits your choices to XLL add-ins or VBA wrapper functions around COM
or traditional DLLs (which you probably want to avoid).

1. Most VC++ Add-ins focus on Office 97 with VS6 as tools,
and are probably dated.
2. More recent and sigificantly newer tools are for Office
2000.

Things have not changed since Office 97. For traditional add-ins the Excel
97 SDK is still the reference and XLL add-ins are supported by Excel
97-2003. COM add-ins do not allow the creation of worksheet functions in
Excel 97-2000.

3. More recently, there is Office XP Developer.
Can I use Office XP Developer to create C++ Addins and
install them for Excel 2000?

No. COM add-ins for worksheet functions have been introduced with Excel
2002.

5. Visual Studio Tool for Office (VSTO) from VS . Net 2003
is supposed to replace the Office Developers, but can not
be used for anything other than Office 2003 Pro, which
really limited it use. Correct?

Mostly. You do not need the Pro version though.

If you don't mind using managed C++ (which is also required for VSTO) you
may have a look at http://ManagedXLL.net (disclaimer: I am the author). You
can use managed C++, C#, VB.NET etc. with Excel 97-2003, mostly by adding a
single [WorksheetFunction] attribute to your code.

Otherwise I still have libXLL and a few links available for free (but I do
not support these anymore). See

http://www.jens-thiel.de/finance/excel

for links.


Jens.

 

[Rob van Gelder]

Frank,

As I understand it, Excel 2002 is the first version to allow User Defined
Functions in COM add-ins.
For what it's worth, in Excel 2002, it works great and in my opinion, is the
only solution (other than XLL) worth considering.

 

[Frank]

#1.

This limits your choices to XLL add-ins or VBA wrapper functions around COM
or traditional DLLs (which you probably want to avoid).

I have seen other people creating DLL for Excel to call as
functions (Matlab functions using C++ complier, for
example). Is DLL better than XLL?

#2.

Things have not changed since Office 97. For traditional add-ins the Excel
97 SDK is still the reference and XLL add-ins are supported by Excel
97-2003. COM add-ins do not allow the creation of worksheet functions in
Excel 97-2000.

My goal is to replace my VBA functions with something much
faster, but I also need to give this to other people in my
company to use, who have no more than Excel 2000. I can't
ask them to install too many things such as .Net
Framework. Does this mean I have to work with DLL or XLL?

#3. Can I use VB to create user defined functions such
that they are fast enough (much faster than VBA)?

 

[Yury Lobanov]

It is very easy to test:

- create ActiveX Dll
- port your function code there
- make in VBA Function
Function Myfunc(arg)
Myfunc=Myfunc_(arg) 'call Dll
End Function

So you have user defined function which work much faster than VBA's.
If you want to stay with Excel 2000.
Yury