Prompt error message with ExcelHandle feature when manually take UDF registration

[jason solarsystem]

hi Govert,

I am trying to use ExcelDNA 1.9.0 with its feature ExcelHandle in UDF.

If the project is allowed to automatically complete the registration of UDF, it runs normally without any error pop-ups. However, now I want to use the manual registration method for UDF and implement it in AutoOpen, and an error will pop up when running:

Registration [Error] Method not registered due to unsupported signature:'<lambda>.MyCreateCalc' : DnaMarshalException - Unknown Data Type: DemoUDFWithHandle.Calc

Registration [Error] Method not registered due to unsupported signature: '<lambda>.MyCalcSum' : DnaMarshalException - Unknown Data Type:DemoUDFWithHandle.Calc

Could you help how to handle this issue? 

Looking forward to your message, thanks.

[jason solarsystem]

using ExcelDna.Integration;
using ExcelDna.Registration;
using ExcelDna.IntelliSense;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Linq.Expressions;

namespace DemoUDFWithHandle
{

    public class Calc // Assume this is a user-defined class
    {
        public double Value1 { get; set; }
        public double Value2 { get; set; }
        public Calc(double v1, double v2) { Value1 = v1; Value2 = v2; }
        public double Sum() { return Value1 + Value2; }
    }

    public static class MyUDF
    {
        [ExcelFunction(Description = "Creates a Calc object and returns it as a handle.")]
        [return: ExcelHandle] // Mark return value as a handle
        public static Calc MyCreateCalc(double d1, double d2)
        {
            return new Calc(d1, d2);
        }

        [ExcelFunction(Description = "Takes a Calc handle and returns its sum.")]
        public static object MyCalcSum([ExcelHandle] Calc c) // Mark parameter as a handle
        {
            if (c == null) return ExcelDna.Integration.ExcelError.ExcelErrorValue;
            return c.Sum();
        }

        [ExcelFunction(Description = "Creates an integer and returns it as a handle.")]
        [return: ExcelHandle]
        public static int MyCreateSquareIntObject(int i)
        {
            return i * i;
        }

        [ExcelFunction(Description = "Takes an integer handle and prints its info.")]
        public static string MyPrintIntObject([ExcelHandle] int i)
        {
            // Note: The value 'i' here is the actual int, resolved from the handle by Excel-DNA
            return $"IntObject (from handle) value = {i}";
        }

    }


    class XllAddIn : IExcelAddIn
    {
        public void AutoClose()
        {

        }

        public void AutoOpen()
        {
            var targetFunctions = ExcelRegistration.GetExcelFunctions();
            targetFunctions.RegisterFunctions();

            IntelliSenseServer.Install();

        }
    }
}

////// .dna ///////

<?xml version="1.0" encoding="utf-8"?>
<DnaLibrary Name="DemoUDFWithHandle Add-In" RuntimeVersion="v4.0" xmlns="http://schemas.excel-dna.net/addin/2020/07/dnalibrary">
  <ExternalLibrary Path="DemoUDFWithHandle.dll" ExplicitExports="false" LoadFromBytes="true" Pack="true" IncludePdb="false"  ExplicitRegistration="true"/>

</DnaLibrary>

[jason solarsystem]

By the way, is it possible to customize the display result when return ExcelHandle? 

For example below, MyCreateCal:1, change to "CAL_OBJECT" something that difference.

[Govert van Drimmelen]

With Excel-DNA version 1.9 we added the 'extended registration' as the default registration mechanism (i.e. if you don't set ExplicitRegistration="true").

This 'extended registration' includes the handle support.

If you want to do the registration yourself (get the functions, then register them) then the same function transformations are not applied.

So, the handle-returning function is not set up with its wrappers (which implement the handle support) in this case.

That's why you get the error.

We'll have to look a bit at how to best approach something like applying all the 'extended registration' changes to a list of functions explicitly.

One solution for your case, which also addresses the question of having a custom handle format) is to implement the handle wrapper yourself.

To help, you can use the  ExcelDna.Integration.ObjectHandles.ObjectHandler class, though it will only work if 
You could try something like:

public static object MyCreateCalc(double d1, double d2)
        {
                 var calc = new Calc(d1, d2);

                 return ObjectHandler.GetHandle("CAL_OBJECT", new object[] { d1,d2}, calc);
        }

Then use with ObjectHandler.TryGetObject.

But I suspect we have to do a bit more work to get this approach working quite right.

I've opened a GitHub issue to remember this: Improve ObjectHandler support from user code · Issue #824 · Excel-DNA/ExcelDna

-Govert

[jason solarsystem]

hi Govert, 

Thanks for your quick response.

I tried with your recommanding code on custom handle format, it still does not work. ObjectHandler.

TryGetObject always return false. Is there any wrong using?

        [ExcelFunction(Description = "Creates a Calc object and returns it as a handle.")]
        [return: ExcelHandle] // Mark return value as a handle

        public static object MyCreateCalc(double d1, double d2)
        {

            //return new Calc(d1, d2);

            var calc = new Calc(d1, d2);
            return ObjectHandler.GetHandle("CAL_OBJECT", new object[] { d1, d2 }, calc);
        }

        [ExcelFunction(Description = "Takes a Calc handle and returns its sum.")]

        public static object MyCalcSum([ExcelHandle] object c) // Mark parameter as a handle

        {
            if (c == null) return ExcelDna.Integration.ExcelError.ExcelErrorValue;

            if (ObjectHandler.TryGetObject("CAL_OBJECT", out c))
            {
                Calc realC = (Calc) c;
                return realC.Sum();
            }
            return null;
        }