2

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 of formulas.

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

Improve this question

2 Answers 2

Reset to default
2

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

But I have some macros I don't want to expose as functions. I.e., I don't want a user to be able to put "=MacroName()" into a cell. That would be bad. Explicitly registering my methods in C++ with XLOPERs gives me the power to register some as UDFs and some as Macros. XLW seems to only let you do UDFs, and I think Excel-Dna is the same way.
In Excel-Dna you can mark the function as hidden: ExcelFunction(Name="MacroName", IsHidden=true)]. This registers the function as pxMacroType=0, so it is not displayed in the function wizard. Would this help?
(Users could still call the function from a worksheet, but they'd have to know the name. You could make the names obscure, adding '?'s etc.)
Or you could register the .Net library for COM interop, and then just Tools->Reference it from your VBA to call the methods.
1

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.

Improve this answer

1 Comment

I was hoping for a more native mechanism, but I'll try this. Having a generic wrapper would definitely be better than a hardcoded wrapper.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.