Call methods written in C# in Excel 2007 from cell formulas

Question

I am using Excel 2007. I have C# code written in a separate binary. The code uses static classes and static methods on the classes. I have a reference to the DLL in my VSTO Excel Worksheet project. What do I have to add or modify to get this to work?

My C# code looks like this:

using System;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

namespace FooStatistics
{
    [ComVisible(true)]
    public static class Statistics
    {
        public static int Count(Range range)
        {
            return range.Count;
        }

I want to be able to put a formula into an Excel cell that looks like this:

=FooStatistic.Statistic.Count(A1:A10)

Or whatever.

I've seen this but it appears to be for non-static classes in Excel 2003. I can't believe the integration is not better by now.

I've looked at a lot of StackOverflow questions on this. They don't appear to provide native integration (many say, "Use X open source library") and, ominously, many are not accepted by the OP. I am not looking for, "Make it into a COM object and call it from VBA."

So I'm looking for:

• Excel 2007
• code in C# DLL
• call from Excel cell as UDF
• native integration

So here's another StackOverflow link, in which two responders say:

• As far as I know, you cannot directly create UDFs in VSTO.
• VSTO has no support for creating Excel UDFs. Automation Add-Ins can be created in .Net, and seem to be the Microsoft approved way of doing it.

This is a question from June 2009. Is this true -- in 2009 you have to expose your .NET components as COM servers to get callable UDFs for Excel?

Answer 1

If these are your four requirements -- (1) Excel 2007, (2) code in C# DLL, (3) call from Excel cell as UDF, (4) native integration -- then, yes, this can be done, and pretty easily. One of the best tutorials on how to do this is Eric Carter's article Writing user defined functions for Excel in .NET.

If you additionally want your code be hosted via VSTO, then I am virtually certain that you are required to use a VBA wrapper in this case. See Paul Stubbs' article How to create Excel UDFs in VSTO managed code where he uses a VBA add-in to expose VBA UDFs, which in turn call his Managed UDFs written in VSTO.

To be honest though, for Excel UDFs, I would simply avoid the use of VSTO. VSTO is a great designer for managed COM add-ins, allowing you to easily add Ribbon controls and the like. But it is of no help for UDFs (and in fact, does not even support it). So my advice is to create a managed automation add-in, as per Eric Carter's article, and drop the VSTO requirement.

If you do this, you will have no problems, I promise. :-)

Mike

Answer 2

@ hughdbrown: just follow the example in Erics article. If you do that it will work. No you can't use static class. your instaniating a .net class with a COM wrapper (registering it for com interop).

Answer 3

Hugh,

I understand your desire for a 'native' solution, rather than to "Use X open source library". But even VSTO does not seem very 'native' to Excel.

Your requirement is exactly what lead me to develop ExcelDna (http://exceldna.codeplex.com) after finding the Automation add-ins inadequate. The support for Automation Add-Ins has not improved in recent Excel versions, whereas the .xll add-in API (that ExcelDna uses) has seen updated support in recent versions, now supporting multi-threaded recalculation, and with async calls coming in Excel 2010.

Even though ExcelDna is an extra part to introduce into your solution, you'll be pleased at the result. Sadly there has been no clear direction from Microsoft on managed UDF add-ins, or any sign of support for this in VSTO, but in practice doing it with ExcelDna is easy, light-weight and it works very well.

Govert