REPOST: Calling Excel Automation Add-In in VBA

[Guest]

We have an Automation Add-In that we developed in C# (2.0) which we are
calling from the worksheet in Excel (2003). We want to be able to call
functions in that AddIn from VBA code in teh workbook as well. If it was a
COM Add-IN, we could call
Application.COMAddIns.Item("TestAutomationAddIn.Functions").Object to get a
handle to the AddIn and make calls on it, but the Application.AddIns
Collection (where Automation AddIns are accessed) does not expose the Object
property. How can I get a handle to my Automation AddIn?
thanks!

 

[Guest]

while you are waiting for an answer here why not also post in

microsoft.public.office.developer.automation
microsoft.public.office.developer.programming

 

[Guest]

I posted in the automation forum as well, but the issue has mroe to do with
Excel since Excel is the only office app the supports "automation add-ins"
(as opposed to "COM add-ins"). Awaiting a response...

 

[Guest]

The automation add in exists as a COM dll - right?

Do you want to access the same instance of the automation addin? I don't
know how to do that. If you want to access an instance then simply add a
reference to the dll in your VBA project.

 

[Matthew Wieder]

Yes, I want to access the same instance as the automation addin. As I
mentioned, this can be done with a COM AddIn by getting
Application.COMAddIns.Item("TestAutomationAddIn.Functions").Object
however, an automation addin is not part of the comaddins collection.
However, as you point out, it IS a 'COM dll' and should be accessable
from the VBA. Can someone help?
thanks!

 

[Matthew Wieder]

Yes, I want to access the same instance as the automation addin. As I
mentioned, this can be done with a COM AddIn by getting
Application.COMAddIns.Item("TestAutomationAddIn.Functions").Object
however, an automation addin is not part of the comaddins collection.
However, as you point out, it IS a 'COM dll' and should be accessable
from the VBA. Can someone help?
thanks!

 

[keepITcool]

Is it a problem to set a reference to the dll in your projects?
I've built an automation addin in vb6 (with Function Wizard
descriptions) that I can reference without problems, albeit with a
small detour

Sub AutomAddinTest()
Dim udf As UDFdemo.Functions
Set udf = New UDFdemo.Functions
debug.print = udf.UDFtest(Empty, Empty, Empty)

End Sub

 

[Matthew Wieder]

That is creating a new instance of the COM object - not getting a
refernce to the AddIns instance. By way of illustration, we have a
worksheet function that takes some params and makes a connection to a
database. We want to expose that connection to VBA through a method in
the add-in. Creating another instance of the COM object obviously would
have no knoweldge of that conenction created by the sheet.

 

[keepITcool]

Sorry. I missed your need to work with the same instance
in worksheet and vba..

I fear you may be limited to using Evaluate method.
or is that unacceptable?

 

[Matthew Wieder]

Obviously it's not ideal, but is that even possible? I thought
'Evaluate' could only return things that would make sense in a
spreadhseet - it wouldn't know what to do with a COM object (in my
example, the connection object that gets returned). There should be a
way to get a reference to the automation addin just as there is to get
one from a COM AddIn.

 

[keepITcool]

ofcourse evaluate cant return the comaddin as an object.

I meant it allows you to call functions from vba
in the same instance as your formulas in the spreadsheet.

 

[Matthew Wieder]

I will retype my last response, amybe it will be clearer; when calling a
function in VBA, it's possible to return a COM object (in our example a
database connection object). When calling a function on a worksheet it
is only possible to return a value (string, int etc.). By using
Evaluate to make calls into my AddIn, I would not be able to return any
objects that I normally would in VBA. However, if I am able to get a
handle to the actual addIn then I could make method calls that return
objects (as I can do with a COM AddIn). That's why using Evaluate isn't
a good solution; let me know if that makes sense.

 

[Mike Rosenblum]

Hey John,

My experience with this is that only ONE instance of your COM Addin is
ever loaded. And although more than one instance of your Automation
Addin class implementing IDTExtensibility2 can be, it's rare that it is
(and this is fully under your control).

The key though, again, is that only one instance of the COM Addin is
loaded. To test this, create a public long variable and have it
incremented by 1 in your Sub Main() startup that the 'COM Addin' will
call when loaded and also report that value via a MsgBox "Main:" &
CStr(myLongVar) and it will return "Main:1" as its result.

Then reference that variable in the 'IDTExtensibility2_OnConnection()'
routine (but don't increment it), calling MsgBox "IDTX:" &
CStr(myLongVar). The result will be "IDTX:1". The key here is that it
is returning 1, not zero.

(I just did this with my addin, which is doing some VERY complex things
at load-up, and so I can't be 100% sure if this is standard behavior,
but I'm pretty sure that it is.)

Anyway, what I would do is, have an internal, Public variable and call
it, say, 'myAddinInst'. Then within the IDTExtensibility2_OnConnection()
sub, set myAddinInst= Me. Thereafter this value can be accessed from
the 'COM Addin' side of the fence.

The one catch I can think of is that I do not think that the Automation
Addin even loads at all until one of its UDFs is actually called from
the Worksheet. So this means that when Excel starts up, your COM Addin
is loading and the 'myAddinInst' will be Nothing. So, what you can do to
"force the issue" is within your Sub Main(), call Evaluate() on any of
your UDFs, this will force Excel to load the Automation Addin located at
the ProgID that is specified, which causes the
IDTExtensibility2_OnConnection() sub to run, where your code sets
myAddinInst= Me. From that point onward the COM Addin has its
'myAddinInst' that it can use at will.

There's a lot of moving parts here, so I cannot GUARANTEE that the above
will work, but I'm fairly confident...

Let us know how it goes!
Mike

VBTalk .NET Office Automation:
http://www.xtremevbtalk.com/forumdisplay.php?f=105

 

[Mike Rosenblum]

Sorry, I meant "Hey Matthew", not "John", sorry!

-- Mike