I have written several helper functions in F# that enable me to deal with the dynamic nature of Excel over the COM/PIA interface. However when I go to use these functions in an Excel-DNA UDF they do not work as expected as Excel-DNA is pre-processing the values in the array from excel.
e.g. null
is turned into ExcelDna.Integration.ExcelEmpty
This interferes with my own validation code that was anticipating a null
. I am able to work around this by adding an additional case to my pattern matching:
let (|XlEmpty|_|) (x: obj) =
match x with
| null -> Some XlEmpty
| :? ExcelDna.Integration.ExcelEmpty -> Some XlEmpty
| _ -> None
However it feels like a waste to convert and then convert again. Is there a way to tell Excel-DNA not to do additional processing of the range values in a UDF and supply them equivalent to the COM/PIA interface? i.e. Range.Value XlRangeValueDataType.xlRangeValueDefault
EDIT:
I declare my arguments as obj
like this:
[<ExcelFunction(Description = "Validates a Test Table Row")>]
let isTestRow (headings: obj) (row: obj) =
let validator = TestTable.validator
let headingsList = TestTable.testHeadings
validateRow validator headingsList headings row
I have done some more digging and @Jim Foye's suggested question also confirms this. For UDF's, Excel-DNA works over the C API rather than COM and therefore has to do its own marshaling. The possible values are shown in this file:
The reason to use ExcelEmpty
(the user supplied an empty cell) is that for a UDF, the argument can also be ExcelMissing
(the user supplied no argument) which might both be reasonably null
and there is a need to disambiguate.
I will adjust my pattern matching to be compatible with both the COM marshaling and the ExcelDNA marshaling.