F# types interactions with Excel

[Keith McCarthy]

Hello, 

I am working to understand the interaction between Excel and F# when using ExcelDNA.  I am using various examples of code from the internet, but I do not understand what is passed between Excel, ExcelDNA, and F#.  When Excel passes an range to F#, what is the type? All the F# collections require a single data type.  So, is it a custom structure?  What type(s?) is required to return to Excel?  Float arrays work, but int arrays fail.  How does one work with a range that contains multiple types?

Why does testFx1 work, but not 2?

        let testFx1 (x : obj[]) = x  //works

        let testFx2 (x : _[]) = x     //fails

I reviewed the documentation, but cannot find the answer. 

[Govert van Drimmelen]

Hi Keith,

 

I think this is the best current list:

Excel-DNA Reference: Data type marshaling | Excel-DNA Docs

 

If the parameter type is Object, the value will be one of:

• Double
• String
• Boolean
• ExcelDna.Integration.ExcelError
• ExcelDna.Integration.ExcelMissing
• ExcelDna.Integration.ExcelEmpty
• Object[,] containing an array with a mixture of the above types
• ExcelDna.Integration.ExcelReference – Only if the parameter is marked with ExcelArgumentAttribute with AllowReference = true.

 

If the parameter type is Object[] or Object[,], you’ll get a mix of the above.

 

A wider range of types are supported – e.g. if the  parameter has type double the function will only get called by Excel if the input value is a double. DateTimes and various sized Integers are supported, even though Excel has no such types for cell values.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/9c4eaa98-621c-4571-94d0-8860cc8c2326n%40googlegroups.com.

[Keith McCarthy]

Hi Govert,

Thank you for the help!

I have been experimenting and can only get Excel to send float values for ExcelDNA.  Are Double or Decimal ever used?  I believe that Decimal > Double > Float in terms of both precision and accuracy.

[Govert van Drimmelen]

--------------------------------------------------
Excel-DNA is now registered on GitHub Sponsors.
Your small monthly contribution will directly fund further development.
--------------------------------------------------

 

Hi Keith,

 

The type called “float” in F# is an alias for the .NET type System.Double and is an IEEE 754 64-bit floating point value.

You can also use “double” as an alias in F# for the same type.

 

The 32-bit floating point type System.Single is called “float32” or “single” in F#.

 

Excel represents all numbers as 64-bit doubles, there are no fixed point ‘decimals’, singles or integer types in Excel.

 

In general, Excel-DNA will pass through any number from Excel as a double, unless the signature of your function sets the parameter type to decimal, single, int or similar, in which case Excel-DNA will try to do a conversion from the double value.

For arrays, one 1D and 2D double arrays are supported as the parameter, in addition to the object[] and object[,] arrays with mixed content (where, again, the numbers will always be doubles).

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/7314769c-a5f3-4a6e-8019-1abe16a94ca0n%40googlegroups.com.