none
Calling Code in an Application-Level Add-in from VBA RRS feed

  • Question

  • Dear all,

    I have been following this how to (in VB.net) to be able to call VSTO code from outside.

    http://msdn.microsoft.com/en-us/library/bb608614(v=vs.100)

    When I run the macro from Excel VBA I get the error 438: Property or method not supported by this object on the line 

    automationObject.ImportData
    I have already tried adding the line 
    <InterfaceType(ComInterfaceType.InterfaceIsIDispatch)>

    like I saw it in the video-how to.

    This did not help.

    I am running VS2010 and Office 2010 on Win7

    Any pointers? Ultimately I want to call an Outlook Addin from Word and Excel. I have found the information that my class then should be derived from StandardOleMarshalObject. Unfortunately I have not been able to find an example of how to do this from VB.net.

    Regards, Birgit

    Monday, February 4, 2013 1:20 PM

Answers

  • well since that was the culprit, editing code would make this whole conversation moot, so leave it there and just mark your own response as answer. As for help regarding marshalling, search a little, here are example

    http://blogs.msdn.com/b/andreww/archive/2008/08/11/why-your-comaddin-object-should-derive-from-standardolemarshalobject.aspx?Redirected=true

    • Marked as answer by BirgitA Thursday, February 7, 2013 2:05 PM
    Thursday, February 7, 2013 9:39 AM
  • Dear Damian,

    Ok, a stupid mistake :(, sorry. While transfering the code (copy/paste)  to my other machine with Office 2007, I suddenly saw the "End Class" twice behind each other in the code of AddInUtilities. Removed that and worked without any further problems. I was unable to add the dll to the References, so you had a good suggestion there.

    Is it an idea to edit the code I posted above to the correct code for the benefit of other people? I can add that the code was changed to be correct.

    Can I get help with how to implement the StandardOleMarshalObject in this thread or should I post a new thread?

    Cheers, Birgit

    Thursday, February 7, 2013 9:21 AM

All replies

  • show us your full code from both sides (.net and vba)
    Monday, February 4, 2013 1:30 PM
  • Thanks for looking into this. Here comes

    Imports System.Data
    Imports System.Runtime.InteropServices
    Imports Excel = Microsoft.Office.Interop.Excel
    
    Public Class AddInUtilities
        <ComVisible(True)> _
        <InterfaceType(ComInterfaceType.InterfaceIsIDispatch)>
        Public Interface IAddInUtilities
            Sub ImportData()
        End Interface
    
        <ComVisible(True)> _
        <ClassInterface(ClassInterfaceType.None)> _
        Public Class AddInUtilities
            Implements IAddInUtilities
    
            ' This method tries to write a string to cell A1 in the active worksheet. 
            Public Sub ImportData() Implements IAddInUtilities.ImportData
    
                Dim activeWorksheet As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet
    
                If activeWorksheet IsNot Nothing Then
                    Dim range1 As Excel.Range = activeWorksheet.Range("A1")
                    range1.Value = "This is my data"
                End If
            End Sub
        End Class
    End Class
    


    Public Class ThisAddIn
    
        Private Sub ThisAddIn_Startup() Handles Me.Startup
    
        End Sub
    
        Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
    
        End Sub
    
        Private utilities As AddInUtilities
    
        Protected Overrides Function RequestComAddInAutomationService() As Object
            If utilities Is Nothing Then
                utilities = New AddInUtilities()
            End If
            Return utilities
        End Function
    End Class
    

    And in thisWorkbook in Excel:

    Sub CallVSTOMethod()
        Dim addIn As COMAddIn
        Dim automationObject As Object
        Set addIn = Application.COMAddIns("CallVSTOexample")
        Set automationObject = addIn.Object
        automationObject.ImportData
    End Sub

    "CallVSTOexample" is the name of my AddIn.

    Cheers, Birgit

    Monday, February 4, 2013 1:57 PM
  • remove  <InterfaceType(ComInterfaceType.InterfaceIsIDispatch)> - not necessary and confusing

    second - does this vba code run inside the same process that the add-in?

    Please try to import your IAddinUtilities into your vba (via Tools-> references) - do you see your add-in class there? try to change declaration of automation object from object to IAddinUtilities (of course after adding reference to it)

    Monday, February 4, 2013 3:32 PM
  • Hi Damian

    <<second - does this vba code run inside the same process that the add-in?>>

    I think the following statement near the end of the original question may be an issue:

    "Ultimately I want to call an Outlook Addin from Word and Excel"


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, February 4, 2013 5:43 PM
    Moderator
  • Hi Cindy

    'ultimately' - not necesserily now - even then it should be possible with the use of StandardOleMarshalObject that will automatically provide marshalling for STA calls. Let's see how it unravels.

    Monday, February 4, 2013 7:28 PM
  • Hi Cindy,
     
    One should be able to call into an Outlook addin from the outside using code that's standalone, running in Word or Excel. I've done it from inside Word and Excel addins in some of my code, as well as from standalone code and even from other Outlook addins.

    --
    Ken Slovak
    [MVP-Outlook]
    http://www.slovaktech.com
    Author: Professional Programming Outlook 2007
    "Cindy Meister MVP" <=?utf-8?B?Q2luZHkgTWVpc3RlciBNVlA=?=> wrote in message news:b9802d78-df56-4bc9-a206-e4aa931de4a7...

    Hi Damian

    <<second - does this vba code run inside the same process that the add-in?>>

    I think the following statement near the end of the original question may be an issue:

    "Ultimately I want to call an Outlook Addin from Word and Excel"


    Cindy Meister, VSTO/Word MVP, my blog


    Ken Slovak MVP - Outlook
    Tuesday, February 5, 2013 7:23 PM
  • Dear Damian,

    Ok, a stupid mistake :(, sorry. While transfering the code (copy/paste)  to my other machine with Office 2007, I suddenly saw the "End Class" twice behind each other in the code of AddInUtilities. Removed that and worked without any further problems. I was unable to add the dll to the References, so you had a good suggestion there.

    Is it an idea to edit the code I posted above to the correct code for the benefit of other people? I can add that the code was changed to be correct.

    Can I get help with how to implement the StandardOleMarshalObject in this thread or should I post a new thread?

    Cheers, Birgit

    Thursday, February 7, 2013 9:21 AM
  • well since that was the culprit, editing code would make this whole conversation moot, so leave it there and just mark your own response as answer. As for help regarding marshalling, search a little, here are example

    http://blogs.msdn.com/b/andreww/archive/2008/08/11/why-your-comaddin-object-should-derive-from-standardolemarshalobject.aspx?Redirected=true

    • Marked as answer by BirgitA Thursday, February 7, 2013 2:05 PM
    Thursday, February 7, 2013 9:39 AM
  • Dear Damian,

    Thank you very much for your help. I'll look into the link you suggested. Looks very much like the answer to my second question :).

    Thursday, February 7, 2013 1:00 PM
  • To complete this:

    The correct syntax in VB.net to derive the class from StandardOleMarshalObject is the following:

    Public Class AddInUtilities
        Inherits StandardOleMarshalObject
        Implements IAddInUtilities

    Thursday, February 7, 2013 2:08 PM