Call code in VSTO ribbon from VBA (Word)

Sorry, but I can't see any description on how to use functions in the ribbon code from Word templates.

I would love to see more specific examples.

(I do know how to Google)

Yes it is possible
Let button1 be a button on the ribbon
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
        ' Myshow is the macro name written in the word template
        Globals.ThisDocument.Application.Run("Myshow")
End Sub

No  - it's the other way. I want to call functions in VSTO *from* VBA

You can do this providing your VSTO add-in exposes its functionality correctly.

Within your VBA you can access the COMAddIns property of the Application object. This is a collection of objects representing the loaded add-ins. Each of these objects has a property called Object of type Object which you can access from within your VSTO solution.

Inside your VSTO ThisAddIn class, override the RequestComAddInAutomationService function like this:

Protected Overrides Function RequestComAddInAutomationService() As Object
      Dim myAddIn = MyClassThatDoesStuff
        Return myAddIn
End Function

Create a public class (in the above code, it's MyClassThatDoesStuff) in your VSTO solution that exposes the public methods that you want to be able to call from your VBA.

So now, in your VBA code you can find the Addin like this:

For Each addIn As COMAddIn In Application.COMAddIns
      If addIn.ProgId = "MyVSTOAddin.Whatever" AndAlso addIn.Object IsNot Nothing Then
            'call some methods
            addIn.Object.MyMethod
      End If
Next


(where "MyVSTOAddin.Whatever" is your VSTO AddIn name.)

I hope that makes sense. If not, let me know.


Pete
Xenacode Ltd

Like this?
First snippet is from VSTO and the last from Word VBA.
I think I find the right AddIn, but get an "Object variable or With block variable not set"
What's wrong?

Public Class ThisAddIn
    Protected Overrides Function RequestComAddInAutomationService() As Object
        Dim myAddIn = New Testing()
        Return myAddIn
    End Function
 
    Public Class Testing
        Sub ThorTest()
            MsgBox("It works!!")
        End Sub
    End Class
End Class
 
=================================
 
Sub Testing()
    Dim AddIn As COMAddIn
    For Each AddIn In Application.COMAddIns
        MsgBox AddIn.ProgID
        If AddIn.ProgID = "CPHRibbon" And Not IsNull(AddIn.Object) Then
            AddIn.Object.ThorTest
        End If
    Next
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

You will need to keep a reference to the Testing object that you create within ThisAddIn or it will be Null/Nothing when the VBA tries to call the ThorTest method. My sample didn't show that so sorry for not being specific. Also try moving the Testing class outside of class ThisAddIn like so:

Public Class ThisAddIn

      Private myTesting as Testing

      Protected Overrides Function RequestComAddInAutomationService() As Object
                myTesting  = New Testing()
                Return myTesting  
      End Function

End Class

Public Class Testing
        Sub ThorTest()
            MsgBox("It works!!")
        End Sub
End Class

I think that should do it.

Pete

Not really. My code is now as below but I still get  "Object variable or With block variable not set"
Is there a way to debug from here?

Public Class ThisAddIn
    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
 
    Private myTesting As Testing
 
    Protected Overrides Function RequestComAddInAutomationService() As Object
        myTesting = New Testing()
        Return myTesting
    End Function
End Class
 
Public Class Testing
    Sub ThorTest()
        MsgBox("It works!!")
    End Sub
End Class
 
===========
 
Sub Testing()
    Dim AddIn As COMAddIn
    For Each AddIn In Application.COMAddIns
        MsgBox AddIn.ProgID
        If AddIn.ProgID = "CPHRibbon" And Not IsNull(AddIn.Object) Then
            AddIn.Object.ThorTest
        End If
    Next
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

Can you check if the addin is actually loaded? Perhaps you could add a line into the VBA like this to check if the add-in has been found in the COMAddIns collection:

Sub Testing()
    Dim AddIn As COMAddIn
    For Each AddIn In Application.COMAddIns
        MsgBox AddIn.ProgID
        If AddIn.ProgID = "CPHRibbon" And Not IsNull(AddIn.Object) Then
            MsgBox ("Found the addin")
            AddIn.Object.ThorTest
        End If
    Next
End Sub

and perhaps another in the addin startup to confirm your VSTO code is running:

Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) _
   Handles Me.Startup
       MsgBox ("Addin starting")
End Sub

Pete

AddIn is starting in VSTO and AddIn is found in VBA :-(

But still "Object variable or With block variable not set"

I tested it this time :o)