excel addin subs called from vba

Hi,

SITUATION:
excel 2007, 2010, 2013
tested on excel 2013 only
compiled as any cpu
visual studio 2012 with office kit installed

PROBLEM:
We have too many subs in many excel sheets in our compagny
I am about to make some new subs for sharepoint 2013 and they need to be accessible for everyone
As you know vba cannot really access sharepoint 2013 client object model (yeah yeah i could call the DLL with all parameters, but, no thanks)

SOLUTION:
Program a excel addin and include the subs in it

PROBLEM:
you cannot simply call a sub in a addin from VBA
you need to go through a test first ;)

So here is my resulting code:

after you create a normal excel addin, you get:
Public Class ThisAddIn

    Private Sub ThisAddIn_Startup() Handles Me.Startup

    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

    End Sub

end class

Your addin project must be called "exceladdin1" for this code

Change the code of the addin for this to be able to call any sub in your addin from vba:
(with parameter in bonus)
note: the test sub is "shared" to be able to call it from the other class created to be able to be called from vba

'=== use addin sub in vba in excel

'=== call from VBA:
'Sub CallVSTOMethod()
'    Dim addIn As COMAddIn
'    Dim automationObject As Object
'    addIn = Application.COMAddIns("exceladdin1")
'    automationObject = addIn.Object
'    automationObject.ImportData("Hello world!")
'End Sub

'=== http://msdn.microsoft.com/en-us/library/vstudio/bb608614.aspx (video have the right code, demo have not it seems)
'=== video CallAddInFromVBA.wmv (working)

Imports System.Data
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

<System.Runtime.InteropServices.ComVisibleAttribute(True)> _
<System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsIDispatch)> _
Public Interface IAddInUtilities
    Sub ImportData(message As String)
End Interface


<System.Runtime.InteropServices.ComVisibleAttribute(True)> _
<System.Runtime.InteropServices.ClassInterface(System.Runtime.InteropServices.ClassInterfaceType.None)> _
Public Class AddInUtilities
    Implements IAddInUtilities

    ' This method tries to write a string to cell A1 in the active worksheet. 
    Public Sub ImportData(message As String) Implements IAddInUtilities.ImportData
        Call ThisAddIn.test(message)

    End Sub
End Class

Public Class ThisAddIn

    Private utilities As AddInUtilities

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

    Public Shared Sub test(message As String)
        MsgBox(message)
    End Sub
    '=== stas excel addin
    '=== made with visual studio 2010
    '=== debugged with excel 2013

    '=== this addin rearrange 2 sheet source display: database, notes
    '=== it generate a frame to enter data in it (bottom frame), sheet name: cartouche
    '=== it calculate the right width for database and notes columns to fill an A2 form with the notes and data
    '===
    '=== goal: make the final result PDF to be approvable by 4 poeple (numeric signatures)
    '=== goal: make final printed result usable in factory to adjust instruments settings for a machine


    Private Sub ThisAddIn_Startup() Handles Me.Startup

    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

    End Sub

end class


now put that in your vba macro

---------------------- vba macro
'=== call from VBA:
Sub CallVSTOMethod()
    Dim addIn As COMAddIn
    Dim automationObject As Object
    addIn = Application.COMAddIns("exceladdin1")
    automationObject = addIn.Object
    automationObject.ImportData("Hello world!")
End Sub