Create an Excel Macro definition in C#

I want to create a Function in my C# code, which can be called from VB as if it were a Macro.

i.e. the VB code should be able to do:

sub vb_method
    csharp_method("some_parameters")
end sub

and have that call my C# method:

public object csharp_method(String parameter) {
    ...
}

I know this can be done in C++:

LPXLOPER retval;
LPXLOPER module;  // = something
LPXLOPER parameters[] = { module,
                         "cpp_function", "parameter_type_info",
                         "MacroName", "text",
                         2,
                         ... };
Excel4v(xlfRegister, retval, parameter_count, parameters);

This registers my cpp_function so that it can be called by the name MacroName. But it's using XLOPER stuff (which is a headache), and it's in C++.

The magic 2 tells Excel to use my function as a macro. Then my c++ code can be called from VB, or using ExecuteExcel4Macro. I can also use this to register User-Defined Functions (UDF) - just use a magic 1 instead. (more details about xlfRegister here: http://msdn.microsoft.com/en-us/library/bb687900.aspx )

C# makes it very easy to create UDFs, but I need a way to register my functions as macros instead o开发者_StackOverflow社区f formulas.

So how can I have a Macro call my C# code?

---

Firstly, you can make .xlls in C# or VB.NET without dealing with XLOPERs or C++ by using Excel-Dna (freely available from http://exceldna.codeplex.com).

So you could make your C# method available as a function in Excel (through a wrapper of the type 0xA3 describes) by just adding an attribute

[ExcelFunction(Name="MacroName")]
public object csharp_method(String parameter) 
{...}

Secondly, you can call both functions and macros from VBA using Application.Run. So you could say

  Dim result As Variant
  result = Application.Run("MacroName", mystring)

---

As far as I know there is no convenience layer available in .NET that allows you to register macros in Excel as it is the case with user-defined functions.

The way to solve your problem would be to create a wrapper DLL in managed C++/CLI that registers the macro and then calls into the managed C# code.

With the means of reflection you should even be able to create a generic wrapper, that loads a C# assembly, enumerates all public methods (or all public methods that have a specific custom attribute), and then dynamically registers these methods.

I haven't checked it, but I assume that the mechanism for registering UDFs works in a very similar way.