Using an independent COM component (VSTOEE90.dll), and some code auto-generated by the Office tools, the VBA programmer can easily call managed code in a VSTA add-in assembly. The complete code necessary is shown in this screenshot of the VBA IDE.
Excel 2007 VBA calls a managed class in VSTA Add-in:
Managed VB class in Excel VSTA Add-in:
<Microsoft.VisualBasic.ComClassAttribute()> <System.Runtime.InteropServices.ComVisibleAttribute(True)> Public Class ThisWorkbook
Public Function AddNumbers(ByVal _
x As Integer, _
ByVal y As Integer) As Integer
Return x + y
End Function
Private Sub ThisWorkbook_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
End Sub
Private Sub ThisWorkbook_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown
End Sub
End Class
This is a simple technique for adding WinForm or WPF dialogs to your VBA solution and calling WCF services and other .NET Framework features from VBA code.
Posted
May 26 2009, 01:09 PM
by
BillL