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.

VSTO "Excel Disigner Could Not Be Activated" error

After Installing VSTO on my PC, and trying our my first "Code Behind" project, I could not have access to the Excel designer. No controls were displayed in the Control toolbox and I could not any button or any type of control on the excel worksheet I was working on
The error message was a pretty scary one

"Excel Designer Could Not Be Activated"

After a bit of diggin on google I found this Post on the msdn forum, which helped me out sort the problem.



Among the different suggestions the one that worked for me was to reinstall the VSTO run time.

If you are developing solution for Excel 2003 and VS2008 this is the one you need


Microsoft Visual Studio 2005 Tools for Office Second Edition Runtime (VSTO 2005 SE) (x86)


or the latest version (up to date of publishing)


Microsoft Visual Studio 2005 Tools for Office Second Edition Runtime (VSTO 2005 SE) (x86)(build 8.0.50727.940)




If you are developing solution for Excel 2007 and VS2008 this is the other link

Microsoft Visual Studio Tools for the Microsoft Office system (version 3.0 Runtime) SP1 (x86)

Office 2003, PIA Installation guidelines for .NET platform

In case you have problem getting the Primary Interop Assembly (PIA) working fine with your .NET Visual Studio platform, just check this link.

Installing the PIA for Office 2003





For example I kept having a "tlbimp.exe" generated assembly (not the PIA) when adding a COM Reference to Microsoft.Office.Interop.Excel, from the "Add Reference", COM tab window.

This means the the .dll path of this reference was pointing to a local dll (local to the project), rather than the one registered in the GAC.


I sorted the problem just changing the "Copy Local" property to false, removing the reference and adding it again.

However, you could also have had some problem with the registration of the PIA in the GAC (Global Assembly Cache). The above link help you sort this kind of problems.