Jaa

How to create Excel UDFs in VSTO managed code

  • Artikkeli

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

  • Anonymous
    February 21, 2006
    I fielded a question from an eager VSTO developer in the forums. It was about the UDF-managed code support...

  • Anonymous
    May 04, 2006
    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

  • Anonymous
    May 04, 2006
    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

  • Anonymous
    June 01, 2006
    Best of the text i read about a problem.

  • Anonymous
    April 03, 2007
    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.

  • Anonymous
    January 05, 2008
    PingBack from http://birthdays.247blogging.info/?p=1591

  • Anonymous
    January 23, 2008
    I've been thinking more about calling unmanaged XLL UDFs from managed code than about managed automation

  • Anonymous
    January 23, 2008
    I&#39;ve been thinking more about calling unmanaged XLL UDFs from managed code than about managed automation

  • Anonymous
    January 18, 2009
    PingBack from http://www.keyongtech.com/3730814-word-event-model-and-update

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/667379-creating-c-dll-for-excel

  • Anonymous
    October 14, 2009
    Idont like vsto? Are there other way to make addins for excel 2007?

  • Anonymous
    February 17, 2011
    The comment has been removed

  • Anonymous
    January 22, 2014
    10 years later, is it the same problem or has it been fixed ?