UDFs for Excel in VSTO

VSTO does not support yet the introduction of UDFs, which is a kind of crazy!
However there are few work around.



UDFs as Automation Add-in

This technique is good if you want to make the function available for every workbook in the Excel Application. It is based on the development of an "Excel Automation Add-in".
This consist of developing a kind of C# COM exposed class.
It was a good idea that the author included in the example the GUID identifiers to avoid registry bloating, however I don't know why he did not defined an Internface and let the class implement it, which is the standard way to develop COM exposed class in .NET

http://blogs.msdn.com/eric_carter/archive/2004/12/01/273127.aspx



UDFs in Code behind files
This technique is good when you want to define workbook level UDFs. It is based on a COM exposed class and some VBA wrapper that you need to write at workbook level. The only problem I had was to the the Exel.Application.Run command work fine. My mistake was due to the fact that I was adding the VBA code the the Excel file that shows up after building and running the program. I did not realized that each time I run, the original .xls file used by the solution was overwriting the one I was editing.

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



Consideration
Given the fact that VSTO does not suggest to save aa .XLA, a .XLS files based on VSTO solution, it is still quite hard to deploy an add-in that can use and excel file as data storage.
For example I developed some years ago an Excel Add-in .XLA to bootstrap the Interest Rate Swap Curve in US and Euro area, and I was using a spreadsheet in the .xls file to store data. The add-in makes available to every workbook a set of functions to compute some fixed income analytics : PV, duration, factor analysis...
Of course if this is the idea, the code-behind pattern VSTO solution is ruled out (it cannot make available function to everywork book that runs in an Excel application).
 We are left with the Automation add-in solution, which is basically a COM Exposed Class. However, the automation add-in does not have any worksheet to store the data. To be honest one could also develop a COM based add-in (which I do not cover here), but I think it would not sort the problem out.

If anybody has any idea on how to develop .xla kind of add-in, ie a VSTO solution that allows to

1) make UDFs available at application level
2) use an Excel file to store some data

as an .xla solution does, please let me know.