View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Mat P:son[_2_] Mat P:son[_2_] is offline
external usenet poster
 
Posts: 97
Default Automatically registering ADD-IN COM DLL - Some help required.

Hi there Mat,

Hi there again Julio

yes, you're right ... but AFAIK only with xla files. For DLL Com
Add-Ins when you register the DLL ( regsvr32 ) the Add-in is called
automatically ( depends on the behaviour ) when Excel is open, so all
the functionality is available for use.


Yes, so that's pretty much what I though then... Good.

But the public functions are
not shown in the Custom Category ( as VBA style ), neither any other
categories.


Okay, so you mean you don't see them as User Defined Functions (UDF:s, also
known as Worksheet functions) in the little Insert Function dialogue, right?

However, you said earlier that "all the functionality is available for use"
-- do you mean that you can actually call the methods you expose from the DLL
from within Excel or even from within the Excel cells themselves? From what
you write, it seems to me as if the only thing that does not work is that the
methods you expose are not visible in the dialogue, but surely that's not a
major problem as long as you can use them properly? So I suppose I've missed
something...

Anyway, for XLA-based code you can use the Application.MacroOption method to
control exactly how your UDF:s will be presented in the Insert Functions
dialogue. I don't assume you will be able to use the MacroOption method for
your COM add-in, but there may still be something pretty similar out there
for you to use.

I've seen other people who creates a pararel xla files with the public
functions, beeing that ones who are calling the functions in the DLL.


Yes, that's how we do it. By keeping the glue layer (the VBA code in the XLA
file) thin you effectively put the bulk of your add-in code in your VB6 DLL:s
anyway.

Then, they register the xla file, and problem almost solved.


No, I wouldn't say so -- in fact, XLA files are a real pain in the behind :o)

Until very recently, we were forced to support all platforms from Excel 97
and forward. So, for us, COM add-ins were never an option. However, for you
the situation is probably different.

COM Add-in:s were introduced in Excel 2000, and I've heard that back then
you were not able to use them to provide UDF:s. This limitation is probably
gone nowadays (I suppose you wouldn't be trying if it weren't possible,
right?)

In fact, I managed to hunt down an old presentation I saw on the web quite
som e time ago:

http://www.codematic.net/excel-user-...-functions.htm

It discusses very valid points in a compact manner. I believe you will find
it useful.

And, of course, more or less everything found on Chip Pearson's site is
highly relevant to Excel developers:

http://www.cpearson.com/excel/topic.htm

Good luck,
/MP

Thanks for the info and the references.
Julio