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

I am not familiar with a method of creating a UDF in Excel 2003 using VS2005 and VSTO without having at least a bit of VBA. Here are 2 links that discuss this a bit further:

http://geekswithblogs.net/Denis/archive/2007/01/03/102623.aspx

http://blogs.msdn.com/pstubbs/archive/2004/12/31/344964.aspx

Answer 2

In this article Eric Carter goes on to explain how to do what you're asking. At the top he even links to an update of the aforementioned blog post.

Answer 3

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.

Answer 4

I don't understand why you want to do this?

VSTO and exposing UDFs via COM interop (from .NET) are two different tasks. Why do you want to host a UDF method inside of a VSTO project?

The way you register the .net UDF assembly means it will have to be in a seperate project to the VSTO project. However if you wanted to share data between the two apps then you have a variety of native .net methods for this, or simply "call" the UDF function from the appropriate range object within your VSTO project.

Is there a reason that you feel it is necessary to have UDF in VSTO?

Answer 5

Create the UDF as Eric Carter explained and pass as parameter to your UDF an Excel range. You're able to access Excel's object model through VSTO by using the given range: Excel.Range rg = param1 as Excel.Range; Excel.Workbook wb = rg1.Worksheet.Application.ActiveWorkbook;