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.

    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.

 


Comments (13)

  1. I fielded a question from an eager VSTO developer in the forums. It was about the UDF-managed code support…

  2. The above code is not working in C#.

    See as below code is not working:

    In VBA:-

    Sub RegisterMacroParameterized(callback As Object)

    callback.CustomSave()

    End Sub

    In VSTO:-

    Step 1:-

    namespace WordTemplate

    {

    [System.Runtime.InteropServices.ComVisible(true)]

    class MyManagedFunctions

    {

    public void CustomSave()

    {

    MessageBox.Show("Save -VSTO");

    }

    }

    }

    Step 2:-

    private void ThisDocument_Startup(object sender, System.EventArgs e)

    {

    WordTemplate.MyManagedFunctions objMacoParam = new MyManagedFunctions();

    object obj = objMacoParam as object;

    this.Application.Run("RegisterMacroParameterized", ref obj,

    ref missing, ref missing, ref missing, ref missing,

    ref missing, ref missing, ref missing, ref missing, ref missing,

    ref missing, ref missing, ref missing, ref missing, ref missing,

    ref missing, ref missing, ref missing, ref missing, ref missing,

    ref missing, ref missing, ref missing, ref missing, ref missing,

    ref missing, ref missing, ref missing, ref missing, ref missing);

    }

    It gives the error when I call Aplication.Run.

    Error is as:-

    Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

    Regards,

    Arun

  3. The above code is not working in C#.

    See as below code is not working:

    In VBA:-

    Sub RegisterMacroParameterized(callback As Object)

    callback.CustomSave()

    End Sub

    In VSTO:-

    Step 1:-

    namespace WordTemplate

    {

    [System.Runtime.InteropServices.ComVisible(true)]

    class MyManagedFunctions

    {

    public void CustomSave()

    {

    MessageBox.Show("Save -VSTO");

    }

    }

    }

    Step 2:-

    private void ThisDocument_Startup(object sender, System.EventArgs e)

    {

    WordTemplate.MyManagedFunctions objMacoParam = new MyManagedFunctions();

    object obj = objMacoParam as object;

    this.Application.Run("RegisterMacroParameterized", ref obj,

    ref missing, ref missing, ref missing, ref missing,

    ref missing, ref missing, ref missing, ref missing, ref missing,

    ref missing, ref missing, ref missing, ref missing, ref missing,

    ref missing, ref missing, ref missing, ref missing, ref missing,

    ref missing, ref missing, ref missing, ref missing, ref missing,

    ref missing, ref missing, ref missing, ref missing, ref missing);

    }

    It gives the error when I call Aplication.Run.

    Error is as:-

    Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

    Regards,

    Arun

  4. rape stories says:

    Best of the text i read about a problem.

  5. dgilbreath says:

    Using this technique, I have a module that contains wrappers for several VSTO functions (all in the same “MyManagedFunctions” class) and this method works for each call to these wrappers inside the first sub routine that calls these wrappers (i can call several functions back to back in this sub without missing a beat).  However, the next time these functions get called, which in this case in in a Worksheet_Change routine, the managedObject is = Nothing and the call fails with this error “Run-time error ’91’:

    Object or variable With block variable not set

    What would cause this?  Outside of RegisterCallback I am never setting managedObject to anything.

  6. I’ve been thinking more about calling unmanaged XLL UDFs from managed code than about managed automation

  7. I&#39;ve been thinking more about calling unmanaged XLL UDFs from managed code than about managed automation

  8. Nydv says:

    Idont like vsto? Are there other way to make addins for excel 2007?

  9. Debs Dey says:

    I've hooked up my VSTO UDFs via an XLAM.  How do I pass an array of e.g. double from VSTO to Excel via the XLAM?

    email me debsdey@hotmail.com

    email me Debashish.Dey@barnett-waddigham.co.uk

  10. nicolas says:

    10 years later, is it the same problem or has it been fixed ?