Use Code of an Add-In by another Add-In

Question

Hi all!

I've got a question: I want to use a function of my Excel Add-In by another Add-In in Excel and by an Word Add-In.
Can someone explain me how to do this? Or Give me an example?
This would be great.

Greetz,
Tom

Answer 1

I followed the example on this MS-Page: http://msdn2.microsoft.com/en-us/library/bb608621.aspx
It works when I am calling the function from an VBA Macro, but from another Add-In Visual Studio 2008 says to me:

A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

What can I do to call the function correctly?

Thanks,
Tom

Answer 2

tommyboy30 wrote:

I followed the example on this MS-Page: http://msdn2.microsoft.com/en-us/library/bb608621.aspx It works when I am calling the function from an VBA Macro, but from another Add-In Visual Studio 2008 says to me: A first chance exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll What can I do to call the function correctly?

It might help if you show us the function, and the VB.NET code in the other Add-in that you're using to call the function.

Answer 3

Oh, I'm sorry, I forgot.

Here's the code from the other AddIn. I tried to adapt th code from the msdn page to vb.net, but I don't know if it is correct.
My other AddIn is called "ManyFunctions" and has a class with a function "MyFunction". The AddIn and the Class are shown at the bottom.
 
"Caller AddIn":

Code Snippet

        Dim addIn As Microsoft.Office.Core.COMAddIn
        Dim automationbject As Object
        addIn = Globals.ThisAddIn.Application.COMAddIns("ManyFunctions")
        automationbject = addIn.Object
        MsgBox(CStr(automationbject.zufallszahl()))

"Called AddIn"

Code Snippet

Public Class ThisAddIn
    Private funktionen As ManyFunctions

    Protected Overrides Function RequestComAddInAutomationService() As Object
        If funktionen Is Nothing Then
            funktionen = New ManyFunctions()
        End If
        Return funktionen
    End Function

    Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
    End Sub

    Private Sub ThisAddIn_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown
    End Sub
End Class

"Called Function"

Code Snippet

<System.Runtime.InteropServices.ComVisibleAttribute(True)> _
<System.Runtime.InteropServices.ClassInterface(System.Runtime.InteropServices.ClassInterfaceType.None)> _
Public Class ManyFunctions
    Function zufallszahl() As Double
        Dim zahl As New Random()
        Return zahl.NextDouble
    End Function
End Class

Answer 4

Can we assume that the error is being thrown at this line?

 

 MsgBox(CStr(automationbject.MyFunction()))

 

You don't show us "MyFunction" in the called add-in. What data type does it return? And do you have Option Strict On or Off set for your calling project?

 

Answer 5

Oops...
the function MyFunction() has to be zufallszahl(). But in my code it is correct.

It returns a double value.
Option strict is set to Off.

I tried it in VBA, and it works. I don't think that the Error is thrown at the Stringconversion.

hmmmm it looks like the exception is thrown at this line:

Code Snippet

addIn = Globals.ThisAddIn.Application.COMAddIns("ManyFunctions")

when i comment out this line (and the following) there is no exception (and of course no result).
But when I look at the Excel-Options AddIn menu, the AddIn "ManyFunctions" is running.

Answer 6

How about if you declare the data type of the variable addIn as Word.COMAddin rather than Office.Core.COMAddin?

 

Or use CType to convert the Word object (coming from "Application") to an Office COMAdd-in in the line of code you show in your last message:

(addIn = CType(Globals.ThisAddIn.Application.COMAddIns("ManyFunctions"), Office.Core.COMAddin) )

 

Answer 7

I'm using the AddIns in Excel, and there is no Excel.COMAddin. When I type it in Visual Studio, it is blue underlined and says: Type is not defined.

Answer 8

I can verify that the following line of code throws an InvalidCastException with the message "Conversion from string "ExcelAddIn1" to type 'Integer' is not valid."

 

addIn = Globals.ThisAddIn.Application.COMAddIns("ManyFunctions")

 

Per the reference topic for the COMAddIns collection in the Office core primary interop assembly (http://msdn2.microsoft.com/en-us/library/microsoft.office.core.comaddins.aspx), you need to use the COMAddIns.Item method to access a specific COMAddIn by using a string that contains the add-in name. I tried your exact code with just the following change, and it worked as expected:

 

addIn = Globals.ThisAddIn.Application.COMAddIns.Item("ManyFunctions")

 

I'm not an expert in this area, but it looks like the only way to index directly into the COMAddIns property from Visual Basic is to pass in an integer that represents the ordinal number of the add-in in the collection of loaded add-ins. To pass the add-in name, you must use the Item method of the collection. This is one of those areas where syntax possible in VBA (indexing the COMAddIns property directly by add-in name) isn't possible in managed languages because of the more strongly typed nature of the Office primary interop assemblies.

 

You might also find the following blog post by Andrew Whitechapel on this subject helpful, if you haven't seen it already:

http://blogs.msdn.com/andreww/archive/2007/01/15/vsto-add-ins-comaddins-and-requestcomaddinautomationservice.aspx

 

I hope this helps,

McLean Schofield