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

1
votes

I saw an article on creating Excel UDFs in VSTO managed code, using VBA: http://blogs.msdn.com/pstubbs/archive/2004/12/31/344964.aspx.

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

I tried the technique Romain pointed out but when trying to load Excel I get the following exception:

The customization assembly could not be 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 **************
2
c#excelvstoadd-inuser-defined-functions

2 Answers

5
votes

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.
1
votes

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 a 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, your addin needs to inject some VB code into each workbook to register the UDF it contains.