This project has moved. For the latest updates, please go here.

Excel Formula as C# method?

Oct 22, 2012 at 10:59 PM

How can I create a C# method that I can use as a formula in Excel?

Eg. formula in A1 is "=HelloWorld()", which returns "Hello" in A1 and "World" in A2.

Thanks, Robert

Oct 23, 2012 at 8:46 AM

Hi Robert,

To create user-defined functions for Excel with .NET, you can use the Excel-DNA library. This complements NetOffice well - Excel-DNA add-ins integrate with Excel via the C API, but you can use NetOffice as a version-independent interop assembly to implement macros that talk to the Excel object model in a single add-in.

Actually having the formula that you enter into A1 as =HelloWorld() also affect A2 is probably a bad idea. You'd rather make an array formula that returns an array, and enter it with Ctrl+Shift+Enter. But there are some workarounds.

in the simplest case you code will be something like this:

public static class MyFunctions
{
    [ExcelFunction(Category="Greetings", Description="Greet an individual")]
    public static string SayHello(string name)
    {
        return "Hello " + name;
    }

    [ExcelFunction(Category="Greetings", Description="Greet the world")]
    public static object[,] HelloWorld()
    {
        return new object[,] {{"Hello"}, {"World"}};
    }
}

-Govert

Oct 23, 2012 at 4:42 PM
Edited Oct 23, 2012 at 4:43 PM

Thanks. So I need to learn Excel-DNA too.
Yet need to find time for that ;-)

Robert