How do I call a VSTO function from a formula in excel?

Question

I'd like to be able to call a function exposed by a VSTO addin from a cell in an excel worksheet. More specifically, if I have a VSTO function Foo() that returns "bar" I'd like to be able to write =Foo() in A1 which evaluates to "bar" on calculation.

Is this possible? What are the key steps I'd need to take?

The prospect of being able to leverage managed code and the VS08 IDE for excel development is very appealing. I thought VSTO would allow me to easily do this but I'm no longer sure. Am I misunderstanding the architecture here? The documentation is a little shoddy.

Answer 1

Excel-DNA (which I develop) is an open-source project that allows you to create user-defined worksheet functions (UDFs) for Excel, as you describe.

With Excel-DNA you can also make full-featured Excel add-ins that include ribbon customization, macros, async function and RTD servers. Excel-DNA uses the native Excel XLL interface to integrate with Excel, so you get very good performance too, compared to solutions based on COM integration.

Answer 2

I don't believe you can do so directly, though you can use a VBA wrapper, see for example http://blogs.msdn.com/pstubbs/archive/2004/12/31/344964.aspx.

You can also use a third party product like ManagedXll to create Excel UDFs in managed code.