Using UDF with dynamic arrays and working around @

%3CLINGO-SUB%20id%3D%22lingo-sub-2692703%22%20slang%3D%22en-US%22%3EUsing%20UDF%20with%20dynamic%20arrays%20and%20working%20around%20%40%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2692703%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20created%20a%20UDF%20(.xll%20plugin%20using%20ExcelDNA)%20so%20we%20can%20call%20into%20C%23%20code.%20The%20UDF%20returns%20an%20array%20of%20double-precision%20numbers.%20We%20also%20write%20.xlsx%20files%20using%20various%203rd%20party%20components.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20of%20the%20new%20versions%20supporting%20dynamic%20arrays%2C%20the%20formulas%20using%20our%20UDF%20get%20the%20implicit%20intersection%20operator%20applied%20to%20them%2C%20and%20that%20is%20preventing%20them%20from%20filling%20the%20cells%20next%20to%20them.%20Removing%20the%20operator%20manually%20allows%20the%20values%20to%20spill%20into%20the%20adjacent%20cells.%20An%20image%20of%20the%20behavior%20is%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20documentation%20on%20this%20feels%20ambiguous.%20In%20one%20document%2C%20it%20talks%20about%20writing%20to%20the%20Range.Formula2%20property%20of%20cells.%20Given%20we're%20using%203rd%20party%20components%20to%20write%20the%20.xlsx%2C%20I'm%20not%20sure%20this%20is%20available.%20But%20other%20docs%20suggest%20that%20any%20UDF%20will%20get%20flagged%20with%20'%40'%2C%20so%20would%20it%20even%20matter%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20there%20suggestions%20on%20how%20to%20work%20around%20this%20issue%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2692703%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

We have created a UDF (.xll plugin using ExcelDNA) so we can call into C# code. The UDF returns an array of double-precision numbers. We also write .xlsx files using various 3rd party components.

 

Because of the new versions supporting dynamic arrays, the formulas using our UDF get the implicit intersection operator applied to them, and that is preventing them from filling the cells next to them. Removing the operator manually allows the values to spill into the adjacent cells. An image of the behavior is attached.

 

The documentation on this feels ambiguous. In one document, it talks about writing to the Range.Formula2 property of cells. Given we're using 3rd party components to write the .xlsx, I'm not sure this is available. But other docs suggest that any UDF will get flagged with '@', so would it even matter?

 

Are there suggestions on how to work around this issue?

0 Replies