Spilling with Excel DNA

[Steven Lovelock]

I was playing with using dynamic arrays in Excel DNA but encountered a strange issue. I was following the article here https://accessanalytic.com.au/tricking-non-dynamic-array-formulas-into-spilling/# to try and "trick" excel into spilling a function that wouldn't normally spill. This works for most excel functions, but does not work for any Excel DNA functions.

To test I made a basic function:
[ExcelFunction]
public static int AddOne(int x) => x + 1;

In A1 I added =SEQUENCE(10,,,20)

in B1 I added =EOMONTH(A1#,0)

in C1 I added =EOMONTH(+A1#,0)

In D1 I added =AddOne(A1#)

In E1 I added =AddOne(+A1#)

B1 returns #VALUE

C1 tricked excel and spilled the correct EOM value into each cell

D1 returned single value 2

E1 returned single value 2

It seems like if you pass an array of inputs into an Excel DNA parameter that only takes a single value, that it is coerced to a single value by taking the first item in the array. This can be confirmed with debugging as the method is only called one with the first input.

Is this something that we can possibly control and opt out of, or is this a limitation of the Excel XLL interface?

I am running Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20540) 64-bit and Excel DNA 1.7.0-rc3

Steven

[Govert van Drimmelen]

--------------------------------------------------
Excel-DNA is now registered on GitHub Sponsors.
Please sign up for a monthly contribution to encourage support and development.
--------------------------------------------------

Hi Steven,

Most of the built-in Excel functions have been modified to understand array inputs, but this is not the default behaviour.

In particular, Excel will not do the array expansion for UDF functions unless they are explicitly implemented as array-aware.

Basically, you need to make the UDF take an 'object' argument, inspect whether it is a single value or an array, and return results based on the input shape.

I have made a small experiment that provides a function to do the above, to map a function taking a single value into an array-understanding function.

You can find the project (VB.NET) here - the function you are looking for is called ARRAY.MAP (ArrayMapN):  Samples/ArrayMap at master · Excel-DNA/Samples (github.com)

With this add-in installed, you can test this as 

    =ARRAY.MAP(AddOne, A1#)

(Note no quotes around the AddOne here - that gives the RegisterId for the function, which the ARRAY.MAP implementation understands).

In addition, you can pull the array-map functionality into your own function to make it array aware.

Suppose you rename your function to "AddOneImpl" (and possibly mark it as IsHidden=true)

Then you can do the ARRAY.MAP call like this

    static object AddOne(object input) => XlCall.Excel(XlCall.xlUDF, "ARRAY.MAP", "AddOneImpl", input);

The ARRAY.MAP function allows functions to take many arguments, and one of these can be a 2D array, or two of the arguments can be 1D arrays.

So a function taking many parameters can work well in a table with row and column headers, returning s single 2D array as result.

-Govert

[Steven Lovelock]

Hi Govert

This is what I feared. I will take a look at that ARRAY.MAP function.

Thanks for the detailed answer. 

Steven