f# - Excel DNA UDF obtain unprocessed values as inputs

Question

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

标签： f#excel-dnaexcel-udf

Answer 1

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:

https://github.com/Excel-DNA/ExcelDna/blob/2aa1bd9afaf76084c1d59e2330584edddb888eb1/Distribution/Reference.txt

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.