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
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
