Hi Dan,

First you need to create a class (must be ComVisible) with your methods. E.g.:

[System.Runtime.InteropServices.ComVisible (true)]

public class MyUdf
{
public object MyFunction()
{
return "My Function!";
}
}

Then put the following line in ThisWorkbook_Startup method:

ThisApplication.Run ("CallbackReg", new MyUdf()); // this will start "CallbackReg" macro in your workbook

Next you need to open your workbook and add new module with following code:

Public MyUdf As Object

Public Sub CallbackReg(callback As Object)
Set MyUdf = callback
End Sub

Public Function MyFunction() As String
MyFunction = MyUdf.MyFunction()
End Function

And this is it!

Also, your document needs to be digitally signed or trusted in order to run CallbackReg macro.

Hope this helps!

It does help, but we want a global set of functions that the workbook has no notion of. With the answer above you must put that CallbackReg code in every workbook that wants to use your functions.

This is extremely important functionality to add especially with the click once type of manifest deployment the new VSTO addins appear to have. I work on a trading floor with 500 some computers. Each computer uses excel and some custom add-ins we wrote.

If there is a bug in an addin you can see the pain.

I agree a more general mechanism is needed for UDFs to be useful from Excel Add-Ins. Any workarounds? For example, can this VB macro be added programmatically to the current active workbook from the C# Add-In itself?

Hi Andrew,

You can create Excel VSTO Add-in and programmatically insert the VBA code in every new workbook that gets opened.

e.g.

Microsoft.Vbe.Interop.VBComponent component = Wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);

component.CodeModule.AddFromString("..... add VBA code for UDFs ....");

and finally call

Cool! Looks like a C# wrapper could be written to do the VBA code generation behind the scenes as well.

Thanks,

Andrew

Hi Dan,

Have a look at ExcelDna http://exceldna.typepad.com. It is an open-source library that allows you to create .xll addins using .Net. In particular, creating user-defined functions in .Net is easy, something not directly supported by VSTO.

The user code can either be C# or VB code in text-based script files, or managed compiled .dlls. Also supported are custom categories, function and argument descriptions and the like. ExcelDna needs only the .Net 2.0 framework, and works with all versions of Excel (from Excel 97).

Regards,

Govert van Drimmelen