UDF with Implicit Intersection Operator

[walterl...@gmail.com]

We've written a UDF with ExcelDNA and it's worked very well. We use a C# component to write a .xlsx that uses the UDF, but now it's getting hung up on the new @ operator being applied to all UDF functions. The C# component seems to be writing Excel2007 layout, and Microsoft is adding the @.

This isn't a problem with ExcelDNA, and I'm trying to see what we can do as far as the component who writes the .xlsx, but I'm wondering if anyone else has dealt with this and whether you found a solution?

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on GitHub Sponsors.

You can add Excel-DNA support with easy billing through a corporate GitHub account.

--------------------------------------------------

The @ is not present in the persisted formula, but is how a Dynamic Arrays-aware (DA) version of Excel will render a UDF formula whenever it is not an array formula.

When writing formulas from a DA Excel, you can use the Range.Formula2 method to write a formula that is not re-interpreted with the @.

See Cell Formulas | Microsoft Docs

However, if you have to make files using an older version of Excel or a third-party component, you might experiment by writing single-cell array (Ctrl+Shift+Enter) formulas (Range.FormulaArray) and seeing how that opens in DA Excel.

Note that this will potentially give you different results in pre-DA Excel than without the array formulas, even though they're only single-cell.

For more on the @ operator, see Implicit intersection operator: @ - Office Support (microsoft.com)

-Govert

[walterl...@gmail.com]

I'm trying to address this on multiple fronts, and I suspect I'll have to start writing the spreadsheet using OpenXml directly instead of 3rd party libraries. But one thing I did was try to write to cells in the spreadsheet from the UDF using ExcelReference.SetValue(). I created a test project (attached), but it doesn't seem to work and I don't know what I'm doing wrong.

Also, I don't see the descriptions from the ExcelFunction or ExcelArgument attributes when in Excel. I may be doing something wrong there too.

[walterl...@gmail.com]

We found a solution https://stackoverflow.com/questions/68987244/can-closedxml-write-range-formulas-that-dont-get-marked-by-excel-with-the-impli