FCell allows you to expose .NET methods and constructors as Excel User Defined Functions which can be used in Excel formulas.

FCell will automatically load each customization assembly and search for public class members which can be exposed as UDFs.

The following class members are candidates for UDFs:

  • Public static method or F#/VB public module function.

  • Public instance method

  • Public instance constructor

In addition, if a customization assembly contains public static parameterless method marked with [XlTypeFeed] attribute and returning Type[] then this method will be invoked and the returned types will be processed as if defined in a customization assembly.

Note

[XlTypeFeed] methods can return types which define UDFs or Ribbons/CustomPanes.

Note

No special attributes are needed to expose a member as UDF so you can use existing .NET assemblies as customization assemblies without recompilation.

For every UDF candidate its signature will be inspected. Only those meeting all these criteria will be exposed:

  • None of input arguments is optional or passed by ref or out. You can use F# options if you need optional arguments.

  • The member is not marked with [XlInvisible] or [XlConverter] or [XlWorkbookOpen] or [XlWorkbookClose]

  • The member is not a generic definition

  • None of the member arguments are generic

  • The member is not an event

  • The member has max 32 (static members) or 31 (instance members) input arguments

  • The member's ReflectedType does not inherit from System.Windows.Forms.Control

  • The member's ReflectedType does not inherit from System.Windows.DependencyObject

Note

Total number of UDFs from all customization assemblies loaded via single XLL AddIn cannot exceed 10 000. You can package your assemblies into more Custom XLL AddIns if you need to expose more UDFs.

If a member meets all the criteria it will be exposed as Excel UDF with the following Excel name:

  • Static (shared in VB) member will retain its .NET name

  • Constructor from class MyClass will be exposed as MyClass.createMyClass

  • Instance method MyMethod from class MyClass will be exposed as MyClass.MyMethod

Note

You can optionally mark your static .NET member or the class which declares it with [XlQualifiedName(true)] to expose it as MyClass.MyMember

If the resulting name contains invalid characters, it will be transformed:

  • Any non letter and non digit will be replaced with underscore (_)

  • If the first character is not a letter, an underscore will be added as prefix

  • If there are duplicate names within customization, a number will be added as postfix to make them unique, e.g. funName0, funName1... This will allow to expose overloaded methods and constructors.

Excel UDFs are grouped in Categories, e.g. Date & Time. FCell will automatically create categories for all .NET UDFs using the pattern of MyAssemblyName.UDFDeclaringClassName

Note

You can optionally mark your UDF defining member with [XlVolatile] attribute to make it volatile. This has no effect on async and reactive functions.

Use [XlFunctionHelp("My function help")] method attribute if you want Excel to display help on your function.

Use [XlArgHelp("My argument help")] parameter attribute if you want Excel to display help on your function argument.

Using Excel built-in functions

You can call built-in Excel functions in FCell UDFs with XlFunctions.RunXlFun (defined in FCell.ManagedXll)

This method takes 2 arguments: function integer id and an array of XlData which represents the Excel function inputs.

The F# code example below shows how to call Excel LINEST function:

Note

Excel functions which take or return Excel references, e.g. OFFSET, cannot be called this way.

Note

You can find Excel function ids in xlcall.h which is distributed with FCell.

F# Copy imageCopy
open FCell.ManagedXll
open FCell.ManagedXll.ExplicitConversion // defines !! cast operator

module Lin =
    let myLinest (y : float[]) (x : float[]) =
        let x : XlData = !!x // see definition of XlData in the next chapter
        let y : XlData = !!y
        //LINEST function id is 49
        //this is equivalent to Excel array formula {=LINEST(Y,X)}
        XlFunctions.RunXlFun 49 [|y;x|]