How to create Excel UDFs in VSTO managed code
One question that I frequently get is how to call managed code from VBA. In general it is not recommended to mix VBA with managed code mainly due to the non-deterministic eventing model. In other words if VBA and managed code are listening for the same event there is no guarantee of the order that the handlers will be called. Another issue with using VBA and VSTO in the same solution is that you now have to deal with two separate security models. With that said, there are still times when you want to call VSTO code from VBA. One scenario is that you are upgrading an existing VBA solution to use VSTO. In this scenario you are keeping all of the existing VBA and are adding new capabilities to your solution using VSTO. Another scenario is that you want to create a solution in VSTO but you want to use User Defined Functions (UDF) in Excel. UDFs still require that they be written in VBA, but you can create your UDFs in managed code and call them from VBA. This is the technique that I describe below. This solution requires that you pass a reference to your managed code to VBA. Once the you have a reference to the managed code you can call that code from VBA. I recommend creating a wrapper in VBA for the managed functions this allows you to “call” the managed code from VBA.
Here is any easy way to call Managed functions from VBA.
- Create a class with your functions in VSTO
Public Class MyManagedFunctions
Public Function GetNumber() As Integer
2. Wire up your class to VBA in VSTO
Private Sub ThisWorkbook_Open() Handles Me.Open
Me.Application.Run("RegisterCallback", New MyManagedFunctions)
3. Create Hook for managed code and a wrapper for the functions in VBA
In a VBA module in your spreadsheet or document
Dim managedObject As Object
Public Sub RegisterCallback(callback As Object)
Set managedObject = callback
Public Function GetNumberFromVSTO() As Integer
GetNumberFromVSTO = managedObject.GetNumber()
Now you can enter =GetNumberFromVSTO() in a cell, when excel starts the cell value should be 42.