Walkthrough: Calling Code in an Application-Level Add-in from VBA(MSDN document)

http://msdn.microsoft.com/en-us/library/bb608614.aspx

1 Define a class that you can expose to other Office solutions
Add the following statements to the top of the file.

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

Replace the empty AddInUtilities class declaration with the following code.

Public Interface IAddInUtilities
Sub ImportData()
End Interface



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.Value2 = "This is my data"
End If
End Sub
End Class

2 Exposing the method for external use
Add the following code to ThisAddin class

Private utilities As AddInUtilities

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


3 Code to call VSTO Method
Add the following VBA code to the code file. This code first gets a COMAddIn object that represents the ExcelImportData add-in. Then, the code uses the Object property of the COMAddIn object to call the ImportData method.

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