Dynamic delegate registration with variable amount of parameters

[edugca]

Hi Govert,

Is it possible to replicate the dynamic delegate registration example (here) for a function with variable amount of parameters? Like when using params in C# or paramArray in VBA.

I tried declaring a new delegate type as suggested (here) but had no success.

Best, Eduardo

[govert]

Hi @edugca - that kind of conversion is built into the Excel-DNA Registration extensions: https://github.com/Excel-DNA/Registration and https://github.com/Excel-DNA/Registration/blob/master/Source/ExcelDna.Registration/ParamsRegistration.cs

[edugca]

Thanks for the quick answer. I had already added the following routine to be called from AutoOpen()

public void RegisterFunctions()
        {
            // There are various options for wrapping and transforming your functions
            // See the Source\Samples\Registration.Sample project for a comprehensive example
            // Here we just change the attribute before registering the functions
            ExcelRegistration.GetExcelFunctions()
                             .Select(UpdateHelpTopic)
                             .ProcessParamsRegistrations()
                             .RegisterFunctions();

        }

It does work for my C# pre-compiled functions. What I'm missing here is how to recall it for the dynamically added functions. That is, how can I call .ProcessParamsRegistrations() to the list of delegates (named delegates) in the following line:

ExcelAsyncUtil.QueueAsMacro(() => { ExcelIntegration.RegisterDelegates(delegates, funcAttribs, argAttribsList); } );

[govert]

I don't understand your question yet.

You can only call ExcelIntegration.RegisterDelegates with methods that have Excel-friendly signatures, so no params for this case. The ProcessParamsRegistrations does a conversion that wraps a params method in a new method with the parameters unrolled (so no more variable params), and then this wrapper is registered.

You could do the same kind of conversion and registration at any time.

[maximovicd]

I think I know what you want, @edugca. You want to register a function that can have, say 1 or 2 parameters. ExcelDNA doesn't seem to make assumptions on that at all, so your problem is "of C# nature".

All you have to do is have your DelegateCreator, and create a function that accepts all of your possible parameters to fall back to (for example private static void Foo(params object[] optional)). Then your delegate creator can be made by compiling your own LambdaExpression:

var parameters = new ParameterExpression[2];
parameters[0] = Expression.Parameter(typeof(object), "param1");
parameters[1] = Expression.Parameter(typeof(object), "param2");

var callParameters = new Expression[1];
callParameters[0] = Expression.NewArrayInit(typeof(object), parameters);

Expression body = Expression.Call(typeof(MyClass).GetMethod(nameof(Foo), System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Static), callParameters);

LambdaExpression exp = Expression.Lambda(body, parameters);
return exp.Compile();

Excel will work with these as optional and always call your Foo function. Hope this helps.

[edugca]

Thanks @govert and @maximovicd! I think @maximovicd answered my question. I'll test this code later.

By the way, the context of my question is that I'm developing a tool similar to BERT (Basic Excel R Toolkit). There will be scripts with functions written in R language by the user. My add-in reads the scripts and wrap the functions so they're visible to Excel.