Create UDF using VSTO and no VBA

Question

Similar to this question (but in my case not VSTO SE), however, I just want to confirm that it is not possible to create a UDF using pure VSTO in Visual Studio 2005 and Excel 2003 - so, to absolutely clear, my question is:

Is it possible to create a Excel 2003 UDF using Visual Studio 2005 and a VSTO solution without using any VBA or other tricks?

I'm aware of ManagedXLL, ExcelDNA, Excel4Net etc but don't want to consider those for the moment.

Thanks

Answer 1

Concerning whether there is a way around COM or VBA I don't think that it is possible (at least not without any very dirty tricks). The reason is that the only way Office can execute external code (i.e. you add-in) is via COM. Even VSTO is still using the old IDTExtensibility2 COM interface underneath. IDTExtensibility2 is a COM interface that all add-ins for Microsoft Office applications must implement.

Before VSTO, Office add-ins had to implement this IDTExtensibility2 interface themselves. In such a COM based add-in (or COM-visible managed add-in) you can simply add your UDF as described here.

However, now with VSTO, there is an additional layer of abstraction: VSTO uses a so-called Solution Loader implementing IDTExtensibility2, which is a dll provided by the VSTO runtime. This means that your add-in is no longer COM-visible. Hence, if you added a UDF to your VSTO add-in it won't be visible to Office.

Paul Stubbs explains on his blog how to do with VSTO and VBA: How to create Excel UDFs in VSTO managed code

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.