Paul Stubbs

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.

• 1. Create a class with your functions in VSTO

<System.Runtime.InteropServices.ComVisible(True)> _
Public Class MyManagedFunctions
    Public Function GetNumber() As Integer
        Return 42
    End Function
End Class

2.      Wire up your class to VBA in VSTO

    Private Sub ThisWorkbook_Open() Handles Me.Open
            Me.Application.Run("RegisterCallback", New MyManagedFunctions)
    End Sub

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

    Public Function GetNumberFromVSTO() As Integer
        GetNumberFromVSTO = managedObject.GetNumber()
    End Function

Now you can enter =GetNumberFromVSTO() in a cell, when excel starts the cell value should be 42.