Using Enums in UDF's

[Robert Crouch]

Is it possible to create a UDF with Excel-DNA using an enum as one of the parameters (e.g. DayOfWeekNumber(myEnum.Sunday) vs. DayOfWeekNumber(myEnum.Monday))

[Govert van Drimmelen]

Hi Robert,

Since Excel doesn't know about the enums, your function will probably have a to accept a string, and then you need to parse it and check whether it is valid yourself.

So something like

public static object UseDay(string dayName)

{

    MyEnum theDay;

    if (Enum.TryParse<MyEnum>(dayName, out theDay))

    {

        return ExcelError.ExcelErrorValue; // #VALUE

    }

    // Use theDay here.

    

}

The ExcelDna.Registration project (https://github.com/Excel-DNA/Registration) would be the right place for us to add some automatic generation of such wrappers, so that you can just write your own function as

public static object UseDay(MyEnum theDay)

{

    // Use theDay here

}

so that can save you some work on the wrappers. But it would look the same to the user in Excel - they have to pass a string and you have to decide how to respond to invalid values.

-Govert

[Govert van Drimmelen]

Sorry - that should be:

    if ( ! Enum.TryParse<MyEnum>(dayName, out theDay))

    {

        return ExcelError.ExcelErrorValue; // #VALUE

    }

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Govert van Drimmelen [gov...@icon.co.za]
Sent: 21 May 2015 12:33 AM
To: exce...@googlegroups.com; rob...@crouch.org
Subject: [ExcelDna] Re: Using Enums in UDF's

--
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 http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[ph...@pinnaclemtnhomes.com]

I think what Robert had in mind was the same thing I am thinking of right now.  Consider Excel's VLOOKUP formula.  The last parameter required gives a drop down list of TRUE or FALSE, and based on that the formula calculates differently.  How do you create a pre-defined list in the UDF that will tell the user to input either X, Y, or Z?  Another example of a default formula with a list as a parameter would be ERROR.TYPE which gives about seven error types to choose from.

To post to this group, send email to exc...@googlegroups.com.

[Govert van Drimmelen]

As a pragmatic approach, i would recommend this:

* Allow your function to accept and process string values for that parameter, and internally convert to your enum value or return a #VALUE error.

* Add a nice description of the allowed options in your [ExcelArgument(Description="...")] attribute.

* Load the Excel-DNA IntelliSense extensions with your add-in ( https://github.com/Excel-DNA/IntelliSense  ) to ensure the description is shown to the user when they are entering your function.

If you really want to emulate the built-in functions like the ones you describe, you enter into a bit of a minefield.

There are two issues here:

* Deciding how to register and handle the enum-related parameter in the UDF registration.

* Making a pop-up list for the possible values that a parameter can take.

Excel does not allow us to register a user-defined function with such a special parameter type.

So you'll have to accept string or double as the parameter type for your function, and convert these to your enum type yourself, and deal with invalid values you might receive etc.

If having to pass a string or number in for the enum values seems terrible, you might set up define names for your enum values that correspond to the different string or number values. These will show in the Excel dropdown after you type the first letter, so that might be quite usable, though it won't be obvious for your user what the options are unless they type the right prefix. One downside to making names is that they are linked to the workbook, and not your add-in.

One hackish way to allow your formula to read:

  =MyFunc(MY_ENUM_1) 

instead of 

  =MyFunc("MY_ENUM_1") 

or 

  =MyFunc(1) 

is to create functions with the names of the enums, say

        [ExcelFunction(IsHidden=true)]

public static object MY_ENUM_1() => "";

and the get their RegisterId and store these internally somewhere, say a dictionary _enumRegIds:

var regId1 = XlCall.Excel(XlCall.xlfEvaluate, "MY_ENUM_1");

         _enumRegIds[regId1] = MyEnum.MY_ENUM_1;

These RegisterIds can be kept in a dictionary in your add-in, and used to look up the right enum value when the function is called:

        public static string MyFunc(object input)

        {

            MyEnum inputEnum;

            if (_enumRegIds.TryGetValue(input, out inputEnum))

            {

                return $"Input is an Enum value: {inputEnum}";

            }

            else

            {

                return $"Input is {input}";

            }

        }

This does not address the pop-up issue yet. That would have to be done by improving the Excel-DNA IntelliSense extension to know about special parameters that should be presented with a list of values. I can't think of any blocking technical issues on adding this to the IntelliSense, but it would be a fair amount of work to implement.

-Govert