How to create Excel 2003 UDF with a C# Excel add-in using vsto 2005 SE

Question

I have seen this http://blogs.msdn.com/pstubbs/archive/2004/12/31/344964.aspx.

However i want to get this working in C# Excel add-in using vsto 2005 SE, can any one help?

Answer 1

Creating UDF using a simple automation addin is quite easy. You will have to create a dedicated assembly and make it visible from COM. Unfortunately, you can't define UDF in a managed VSTO Excel Addin.

Anyway, there is a work around, which I found very limiting. It is described in this discussion. Basically, our addin needs to inject some VB code into each workbook to register UDF it contains.

Answer 2

Hi Romain,

That is basically the same of the blog I posted which about is in VB.net.

But then I try to load Excel I get:
The customization assembly could not ne found or could not be loaded.
You can still edit and save the document.....

Details:
Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))


************** Exception Text **************
System.Runtime.InteropServices.COMException (0x80020005): Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
at Microsoft.Office.Interop.Excel._Application.Run(Object Macro, Object Arg1, Object Arg2, Object Arg3, Object Arg4, Object Arg5, Object Arg6, Object Arg7, Object Arg8, Object Arg9, Object Arg10, Object Arg11, Object Arg12, Object Arg13, Object Arg14, Object Arg15, Object Arg16, Object Arg17, Object Arg18, Object Arg19, Object Arg20, Object Arg21, Object Arg22, Object Arg23, Object Arg24, Object Arg25, Object Arg26, Object Arg27, Object Arg28, Object Arg29, Object Arg30)
at ExcelWorkbook4.ThisWorkbook.ThisWorkbook_Startup(Object sender, EventArgs e) in C:\projects\ExcelWorkbook4\ExcelWorkbook4\ThisWorkbook.cs:line 42
at Microsoft.Office.Tools.Excel.Workbook.OnStartup()
at ExcelWorkbook4.ThisWorkbook.FinishInitialization() in C:\projects\ExcelWorkbook4\ExcelWorkbook4\ThisWorkbook.Designer.cs:line 66
at Microsoft.VisualStudio.Tools.Applications.Runtime.AppDomainManagerInternal.ExecutePhase(String methodName)
at Microsoft.VisualStudio.Tools.Applications.Runtime.AppDomainManagerInternal.ExecuteCustomizationStartupCode()
at Microsoft.VisualStudio.Tools.Applications.Runtime.AppDomainManagerInternal.ExecuteCustomization(IHostServiceProvider serviceProvider)


************** Loaded Assemblies **************

Answer 3

You should also have a look at ExcelDna - http://www.codeplex.com/exceldna. ExcelDna allows managed assemblies to expose user-defined functions (UDFs) and macros to Excel through the native .xll interface. The project is open-source and freely allows commercial use.

Your user-defined functions can be written in C#, Visual Basic, F#, Java (using IKVM.NET), and can be compiled to a .dll or exposed through a text-based script file. Excel versions from Excel 97 to Excel 2007 are supported.

Some advantages of using the .xll interface rather than making automation add-ins include:

• older versions of Excel are supported,
• deployment is much easier since COM registration is not required and references to user-defined functions in worksheet formulae do not bind to the location of the add-in, and
• the performance of UDF functions exposed through ExcelDna is excellent.