Call Add in from Macro

R

Rog

Does anyone have any experience of help on calling an office add in from
a macro?
Thanks
 
E

Eugene E. Starostin

If the add-in is yours it is possible. In this case you are to:

1. In OnConnection, set a reference to an instance (that implements the
methods you need) to the AddInInst.Object property.

2. In your macro find the add-in in the comaddins collection and call its
methods via AddIn.Object.YourMethod.

If the add-in is not yours it is not possible to directly call the add-in.
But you can call the add-in's buttons via CommandBarButton.Execute.
 
R

Rog

Thank you Eugene for the help,
The add in is mine, and I tried to add a reference in the macro vb
editor but I was not able to find it there.
Can you give more detail on option 1?
Thanks
 
R

Rog

Actually Eugene, I was able to do this:
MsgBox Application.COMAddIns.Item(1).Description

and it was my add in, but then how do I call my code from the macro?
Thanks so much.
 
S

Stephen Bullen

Hi Rog,
Actually Eugene, I was able to do this:
MsgBox Application.COMAddIns.Item(1).Description

and it was my add in, but then how do I call my code from the macro?
Click to expand...

In the COM Addin's OnConnection event, add the line:

AddinInst.Object = Me

and a public property or method. So the Designer class might look like:


Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, custom() As Variant)

AddInInst.Object = Me
End Sub

Public Sub ShowMessage()
MsgBox "In Addin"
End Sub


And let's say the project is called "MyAddin" and the Designer class is
called "dsrConnect"

Then, from VBA, you can show the message by calling:

Application.COMAddIns("MyAddin.dsrConnect").Object.ShowMessage

and any other public properties or methods you add to that class can be
accessed similarly.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk
 
Top