Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling COM addin methods (Excel 2002/1)

    How does one call Excel Com addin methods? Here is some code I am currently using:

    In the Excel ThisWorkbook object's Workbook_Open event

    Private Sub Workbook_Open()

    dim oAddin as object

    ' make sure the COM addin is connected
    Application.COMAddIns("MyCOMAddIn.AddInDesigner1") .Connect = True ' This seems to work

    ' Get an instance of a COM object
    Set oAddin = Application.COMAddIns("MyCOMAddin.AddInDesigner1") ' This seems to work as oAddin is now set to Gobal ThisWorkBook which is the Addin Display Name

    ' Call dummy function as a test
    oAddin.Dummy() 'This is where it fails. I get an error message saying Run time error 438. Object does not support this property or method.

    End Sub

    The function dummy is declared in the COM Addindesigner1 as
    Public Function dummy() as Boolean

    End Function
    This function is only being used as a test to understand how to call methods in a COM addin. I would prefer to declared methods in thier own class in the COM addin and I have tried this but with the same results.

    If anyone has an example of calling methods in a COM Addin please show it to me.

    Thank you

    Jim Bassett

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calling COM addin methods (Excel 2002/1)

    Hi,
    Does your add-in's OnConnection event contain something like:
    <pre> On Error Resume Next
    AddInInst.object = Me
    </pre>

    It needs to in order for your function calls to work.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling COM addin methods (Excel 2002/1)

    Yes I have AddInInst.object = Me

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calling COM addin methods (Excel 2002/1)

    I just noticed that you have:
    Set oAddin = Application.COMAddIns("MyCOMAddin.AddInDesigner1")
    but I think you need to specify:
    Set oAddin = Application.COMAddIns("MyCOMAddin.AddInDesigner1") .Object
    since the default property of a COMAddIn is Description not Object.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling COM addin methods (Excel 2002/1)

    With the release of 2002, you can do this with an Automation Addin. There is too much to cover here, it is in Stephen Bullen's book, Excel 2002 VBA. Here is an outline:
    1) They can only be created with VB6, not Office Developer Edition
    2) The class must be publicly-creatable( ie Instancing = Multi-User or Global-Multi-User)
    3) The procedure must be a Function and declared Public
    4) You must use the CallByName function to invoke your function

    It may be that #4 above will solve your problem, assuming that you have declared Dummy Public. Instead of
    <pre>oAddin.Dummy() </pre>

    try
    <pre>Result = CallByName(oAddin, "Dummy", vbMethod)</pre>

    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling COM addin methods (Excel 2002/1)

    Ok that fixed that problem as I am now able to access the dummy function in AddDesigner1. I have a class in the COM addIn that if I try to access a simliar function it cannot, giving me the error message Run-time error '9' Subscript out of range when I do the following:
    Set oAddIn = Application.COMAddIns("MyCOMAddIn.MyClass").Object . Shouldn't I be able to access public functions in my own classes in the COM addin?

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling COM addin methods (Excel 2002/1)

    Sorry, you need to go slower. What fixed what in your Dummy test program? And how is the new configutration different from the old?
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling COM addin methods (Excel 2002/1)

    using your suggestion of calling by name: Result = CallByName(oAddin, "Dummy", vbMethod) allowed me to call the dummy public function in AddInDesigner1 of my COM Addin.

    Now instead of calling that function in AddInDesigner1 I am attempting to call a function by the same name in a class I created in my COM addin. Here is the code.

    Dim oAddIn as Object
    Dim f as Boolean

    Set oAddIn = Application.COMAddIns("MyCOMAddIn.CGlobalThisWorkB ook").Object ' It fails here giving me the error message Run-time erro 9 Subscript out of range

    f = CallByName(oAddIn, "Dummy", vbMethod)

    End Sub

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling COM addin methods (Excel 2002/1)

    I know that I'm sounding like I get a commission on the book, but you really need to get Stephen Bullin's book. It's on page 336, but too long to post. Basically, in the Designer's class module, you provide a reference to the AddIn class:
    <pre> AddInInst.Object = Me</pre>

    Then you add Property Get routines to create & return new instances of your Class:
    <pre>Public Property Get CommFunctions() as MyCommClass
    set CommFunctions = New MyCommClass
    End Property</pre>


    Then in VBA, you can reference the Dummy function that you have moved into MyCommClass by
    <pre>Result = Application.ComAddins("MyCOMAddin.AddInDesigner1") .Object.CommFunctions.Dummy()</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling COM addin methods (Excel 2002/1)

    Thanks!. Does the techiques you have described apply only to Excel 2002 or also to Excel 2000 in regards to access COM addin class functions?

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling COM addin methods (Excel 2002/1)

    The Automation AddIn that I first mentioned is only 2002. The rest appears to work in XL2000. I just added a class with a function to my Office 2K platform:
    The designer now looks like: (new lines in pink)
    <pre>Option Explicit
    '
    Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    ByVal AddInInst As Object, custom() As Variant)
    Set xlApp = Application
    Set xlBook = xlApp.ActiveWorkbook
    CreateToolbar
    EnableWorksheets
    SummaryButton
    <font color=magenta>AddInInst.object = Me</font color=magenta>
    MsgBox App.ProductName & _
    " V" & App.Major & "." & App.Minor & "." & App.Revision & _
    " connected to " & xlApp.Name
    End Sub
    '
    Private Sub AddinInstance_OnDisconnection( _
    ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _
    custom() As Variant)
    DeleteToolbar
    MsgBox App.ProductName & " disconnected from " & xlApp.Name
    Set xlApp = Nothing
    Set xlBook = Nothing
    End Sub
    '
    <font color=magenta>Public Property Get slFunctions() As slClass
    Set slFunctions = New slClass
    End Property</font color=magenta></pre>


    I added a new class named slClass:
    <pre>Option Explicit
    Public Function Dummy()
    Dummy = 2
    End Function</pre>

    After rebuilding and connecting the addin to Excel, I wrote the following test macro in Excel:
    <pre>Option Explicit
    Sub Macro2()
    With Application.COMAddIns("ezSpeck.slConnect").Object. slFunctions
    MsgBox .dummy()
    End With
    End Sub</pre>

    Everything worked fine, but what a hard way to get a MsgBox to say 2! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    I guess it is worth it when the method to derive the 2 is proprietary!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling COM addin methods (Excel 2002/1)

    I tried that section of code such as:
    With Application.COMAddIns("ezSpeck.slConnect").Object. slFunctions
    MsgBox .dummy()
    End With
    and it never makes it passed the With statement. giving me the error "Run-time error 9, Subscript out of range". Are you using Excel 2000 or Excel 2002. I am using Excel 2002.

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling COM addin methods (Excel 2002/1)

    Before you call your class function, the AddIn must be connected. Also, you need to change my names into yours. Something like:
    <pre>Application.COMAddIns("MyCOMAddIn.AddInDesign er1").Connect = True
    With Application.COMAddIns("MyCOMAddIn.AddInDesigner1") .Object.xxx</pre>

    where xxx is the property that you added to your designer.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling COM addin methods (Excel 2002/1)

    BTW, I forgot to answer all of your questions. I am currently running on Office 2K machine (you had asked if it would run on XL2K), but am 95% certain that it will run on XP. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  15. #15
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling COM addin methods (Excel 2002/1)

    You might want to look at MSFT KB articles 256624 and 285337.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •