Automatically registering ADD-IN COM DLL - Some help required.

[rhodinar]

Hi guys,

I've developped a COM ADD-IN in VB6 and it's working perfectly!
When the DLL is registered, the complement starts but the public
functions to be used inside the excel cells aren't shown in the
functions box. All the other things, such forms and routines works
well.

In order to show the functions I've to manually include de class via
Tools->Add-Ins->Automation->mydll.class

Doing that the functions are shown in the list in his own category.

Now the problem ... I don't want any manual interaction with the user.
I need to install the Add-In completly transparent for the final user,
and this include the functions.

I've tried to put the complement using this code .....

Dim oXL As Object, oAddin As Object
Set oXL = CreateObject("Excel.Application")
oXL.Workbooks.Add
Set oAddin = oXL.AddIns.Add( ... Path to my DLL ... , False )
oAddin.Installed = True
oXL.Quit
Set oXL = Nothing

.... but it crash with a 1004 error. Unable to get the Add property of
the AddIns class.
I've read this error comes if no workbook open, but this is not the
case. It only happens when I
call a DLL, if the code it's changed to refer a .xla file, it works
perfecty well.

Now, i'm driving me mad .....

Any help would be appreciated. Someone has solved this issue ???
Thanks in advance,
Julio

PD: If any needs more details, don't hesitate to contact me!

 

[Mat P:son]

Hi there Julio,

I might be a bit confused, because I'm currently not using a COM Add-In, but
a normal VBA, but shouldn't the add-in be immediately available to Excel
after entering the appropriate info into the Windows Registry?

You need to use the Add-In Manager dialogue for XLA files (unfortunately)
but I thought one of the good things about COM add-in:s is the ability to
circumvent this?

Anyway, some references from MSDN; hope it helps:

http://support.microsoft.com/?kbid=238228

Later,
/MP

 

[rhodinar]

Hi there Mat,

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. But the public functions are
not shown in the Custom Category ( as VBA style ), neither any other
categories.

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.
Then, they register the xla file, and problem almost solved.

Thanks for the info and the references.
Julio

 

[Mat P:son]

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 )

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-defined-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

 

[rhodinar]

Well Matt, thanks for the whole reply ...

The only thing that I've explained bad it's that when I'm saying "whole
funtionality", it doesn't include the functions.
I can't call them anyway ( only marking the class in the
add-ins->automation ). When they're visible I can call them.

I'll take a look to the links and I'll tell you my progress ( if any
;-) ).

Thanks again for your help.
Julio

 

[Mat P:son]

Well Matt, thanks for the whole reply ...

No probs, you're welcome

The only thing that I've explained bad it's that when I'm saying "whole
funtionality", it doesn't include the functions.

Okay, that sort of explains things...

I can't call them anyway ( only marking the class in the
add-ins->automation ). When they're visible I can call them.

Aha, but that's pretty encouraging -- surely, the Add-In Manager doesn't do
anything that you cannot do yourself, programmatically. So even though it's
annoying not to know exactly which calls or which reg keys are still missing,
it's nevertheless good to see that it is indeed possible to use your add-in
in exactly the way you intended.

You can always snoop on what's happening in the Windows Registry by using
tools from SysInternals. That way you can listen on what the Add-In Manager
is up to when you manually install the COM add-in. That's obviously just a
tip -- I don't know whether or not Excel actually needs to fiddle with the
Reg to make this work.

However, if the COM add-in somehow needs to be installed on a per-user basis
rather than on a per-machine basis then you may run into pretty severe
problems: if you're planning to let Administrators install the add-in on user
machines then the user may still have to take care of the final part of the
installation him-/herself, which more or less sums up to going into the
Add-In Manager (sigh!)

I'll take a look to the links and I'll tell you my progress ( if any
;-) ).

As always, good luck to you...

Thanks again for your help.
Julio

Any time,
/MP

 

[Mat P:son]

Oh, and you may want to drop a line or two to these guys as well, if/when
you're running into trouble in the future:

MSDN Home >
MSDN Newsgroups >
Office Solutions Development >
Office Development (General) >
office.developer.com.add_ins

Ta da,
/MP