Param Array support and Excel-DNA.Registration

[llandi]

Hi,

These days, I have searched and read the various posts on the use of params Array in UDF.

Unfortunately, I realized that even today Excel-DNA is not capable of supporting this feature.

It seems to me that you understand that once you have developed a esensione now call "Registration" that among other things would cover my needs.

is this correct?

Unfortunately I have not yet figured out (actually I have not tried it yet) how to use this extension of Excel-DNA, but I wonder what is the level of reliability and usability.

my goal, at the end, is to be able to create a simple UDF that has an operation similar to the function Excel SUM (...) in the management of the parameters.

I understand that it should not be easy to achieve and for this I ask possibly a suggestion to help find the path (link or other material) to study a method to obtain this missing feature at the cost to implement a special extension in C or another language that then I would be happy to share (if and when I had to be able to implement it).

Thanks again for the great and incredible work that continues to do on Excel-DNA,

Kind regards,

Luca Landi (from Italy)

[Govert van Drimmelen]

Hi Luca,

You're right - support for "params" parameters can be found in the ExcelDna.Registration package, as one of the custom registration processing options implemented there.

It's supported in the same way that the rest of Excel-DNA is supported, and general discussions and questions are welcome on the Excel-DNA Google group.

As a first experiment, you might follow these steps:

* Create a new Class Library project, say "ParamsTest"

* Install the ExcelDna.AddIn and ExcelDna.Registration packages. You can use the Package Manager Console:

PM> Install-Package ExcelDna.Addin

...

PM> Install-Package ExcelDna.Registration

* Configure the project for Explicit Registration by editing the ParamsTest-AddIn.dna file to look like this:

<DnaLibrary Name="ParamsTest Add-In"   RuntimeVersion="v4.0" >

  <ExternalLibrary Path="ParamsTest.dll" ExplicitRegistration="true" LoadFromBytes="true" Pack="true" />

</DnaLibrary>

* Add the registration processing code in an AutoOpen() implementation:

using ExcelDna.Integration;

using ExcelDna.Registration;

namespace ParamsTest

{

    public class AddIn : IExcelAddIn

    {

        public void AutoOpen()

        {

            ExcelRegistration

                .GetExcelFunctions()

                .ProcessParamsRegistrations()

                .RegisterFunctions();

        }

        public void AutoClose()

        {

        }

    }

}

* Add a params function:

using System.Linq;

using ExcelDna.Integration;

namespace ParamsTest

{

    public class Functions

    {

        [ExcelFunction]

        public static string MyTest()

        {

            return "Hello from ParamsTest add-in";

        }

        [ExcelFunction]

        public static double MySum(params double[] values)

        {

            return values.Sum();

        }

    }

}

* Press F5 to compile and run.

* Check that your functions work as expected.

-Govert

[Sanket padte]

Hi Govert,

I am trying to build Excel DNA UDF which will have struture similar to FunctionName(Col1 Name, Col1 Value, Col2 Name, Col2 Value.....) .

This Col1 name and Col1 Value are the ranges from excel sheet.

Can I use paramArray for multiple arguements.

Thanks
Sanket

[Govert van Drimmelen]

Hi Sanket,

The C# params feature requires that all the parameters have the same type. In your these would be 'object', so maybe it would work.

If you have only one or a few such functions, it's probably easier to build your function with up to 256 parameter and not use the ExcelDna.Registration approach.

On the other hand, if you're not happy with the way ExcelDna.Registration unrolls the params, you can generate your own function wrappers and register these explicitly (with ExcelIntegration.RegisterDelegates).

-Govert

[Alberto Ghiglia]

Hi Govert,

in the version 4.0.30319 of ExcelDNA.Registration I couln't find the ".ProcessParamsRegistrations()"

So, in the AutoOpen I've written:

     Dim Functions = ExcelRegistration.GetExcelFunctions().ToList()

        Dim Commands = ExcelRegistration.GetExcelCommands().ToList()

        Dim NrF As Integer = Functions.Count

        Dim NrC As Integer = Commands.Count

        ExcelRegistration.RegisterFunctions(functions)

        ExcelRegistration.RegisterCommands(Commands)

And the TEST Function I've written:

        <ExcelFunction(IsVolatile:=True, IsMacroType:=True, Description:="Ritorna il colore dello Sfondo", Name:="SOMMA.XXX")> _

        Public Shared Function TestXXX(<ExcelArgument(Description:="Cella di riferimento")> ParamArray values As Double()) As Double

            Return values.Sum()

        End Function

But when I try to run the formula in excel "=SOMMA.XXX(A1;A2)" I get the #Value ERROR

What is wrong?

Many thanks

Alberto

[Govert van Drimmelen]

Hi Alberto,

 

Your VBA code that uses the ExcelDna.Registration.VisualBasic extension would typically look like the sample add-in here:

https://github.com/Excel-DNA/Registration/blob/master/Source/Samples/Registration.Sample.VisualBasic/ExampleAddIn.vb

where you would call

    PerformDefaultRegistration()

to deal with the typical registration changes.

This should deal with your ParamArray functions.

 

However, to support enums you need to make your own registration conversions, so the code will look more like the C# sample here:

https://github.com/Excel-DNA/Registration/blob/master/Source/Samples/Registration.Sample/ExampleAddIn.cs

which has extra conversion functions for the enum <-> string conversions – see line 96:

 

                // This is a pair of very generic conversions for Enum types

                .AddReturnConversion((Enum value) => value.ToString(), handleSubTypes: true)

                .AddParameterConversion(ParameterConversions.GetEnumStringConversion())

 

and then your registration function gets a lot more complicated.

 

So I don’t have a nice example there which shows how to do custom conversions or enums in VisualBasic.

It’s probably easier to do the enum <-> string conversions inside your functions (so you declare the function with a string parameter and call Enum.Parse inside your own function)

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Alberto Ghiglia]

Thanks!

I will try to figure out the sample in my VB,NET project

Alberto

Il giorno mercoledì 9 settembre 2015 23:53:43 UTC+2, llandi ha scritto:

[Alberto Ghiglia]

Hi Govert

I included 

        ExcelIntegration.RegisterUnhandledExceptionHandler(Function(ex) "!!! ERROR: " + ex.ToString())

        PerformDefaultRegistration()

in the AutoOpen and added ExplicitRegistration="true"

but when I run excel I get the following message

Registration [Error] Method not registered due to unsupported signature: 'Func`2.Invoke' : DnaMarshalException - Unknown Data Type: Microsoft.Office.Interop.Excel.Range

Is it maybe because in the UDF I have as input parameter the variable "Address" that is an Object type that I translate into a Range with this code?

 Dim CellRange = CurrentExcelApplication.Range(XlCall.Excel(XlCall.xlfReftext, Address, True))

If so how can I convert an object type into a range type?

Thanks

Alberto

Il giorno mercoledì 9 settembre 2015 23:53:43 UTC+2, llandi ha scritto:

[Govert van Drimmelen]

The VB sample project has a function that has a Range argument in https://github.com/Excel-DNA/Registration/blob/master/Source/Samples/Registration.Sample.VisualBasic/RangeParameterExamples.vb

 

 

    <ExcelFunction>

    Function dnaVbRangeTest(input As Range) As String

        Return input.Address

    End Function

 

Maybe you can try to get the sample to work, then see how it differs from your code.

 

-Govert

 

 

From: exce...@googlegroups.com [mailto:exce...@googlegroups.com] On Behalf Of Alberto Ghiglia
Sent: 27 September 2017 17:53
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] Re: Param Array support and Excel-DNA.Registration

 

Hi Govert

--