How do I write a function in F# that can be called from Excel and which will take a range as input and output an array into the ssheet?

Question

I understand how to do this in C# and I can create AddIns using Excel DNA. I can't figure out the F# syntax to enable a function, f, which, for sake of simplicity, does something like f(x)= 2D array of dimension [2,2] containing x at all 4 values. So I'd call f from excel taking an input from a cell, and it would return the 2x2 array. (I will be doing more complex things here - but If I can understand that simple case I'll be fine). Help greatly appreciated!

My attempt at creating an F# function looks like this:

let array2D : int [,] = Array2D.zeroCreate 20 20
let g x = array2D

The problem I'm having is that this doesn't appear as a function in the excel dropdown under the xla name.

Working C# Example:

public static double[,] arraytoexcel(int N) 
{ 
    double[,] RetArray = new double[N, N]; 
    for (int i = 0; i < N; i++) { 
        for (int j = 0; j < N; j++) { 
            RetArray[i, j] = N; 
        } 
    } 
    return RetArray; 
}

Answer 1

You probably want something like this:

let calculateCellContents size x y = float (y * size + x) // Whatever math you need

let mkArray size = Array2D.init size size (calculateCellContents size)

Note that calculateCellContents returns a float (the F# name for a C# double), not an int. Your C# example had the function returning an array of double values, and if XLA is looking for functions with that signature (returning an array of floats), then that might be one possible reason it's not showing up.

Now, if that doesn't solve the "this doesn't appear as a function in the excel dropdown" problem, there are two other possibilities I've thought of. First, if you're supposed to set an attribute on the function for the Excel dropdown to be able to find it, then the syntax is [<AttributeName>]. E.g.:

let calculateCellContents size x y = float (y * size + x) // Whatever math you need

[<ExcelFunction(Description="Make a simple array")>]
let mkArray size = Array2D.init size size (calculateCellContents size)

Second, it might be that you have to change the type of the function. F# functions have the type FSharpFunc, and it's entirely possible that XLA is looking for a Func rather than an FSharpFunc. You can convert between them by creating a new System.Func object from the F# function:

let calculateCellContents size x y = float (y * size + x) // Whatever math you need

let mkArray size = Array2D.init size size (calculateCellContents size)

let mkArrayVisibleFromExcel = new System.Func<int,float>(mkArray)

Or possibly:

[<ExcelFunction(Description="Make a simple array")>]
let mkArrayVisibleFromExcel = new System.Func<int,float>(mkArray)

If that doesn't work, try changing mkArray to take a float argument, and changing the call to Array2D.init accordingly:

let calculateCellContents sizeF x y = float y * sizeF + float x // Whatever math you need

let mkArray (sizeF:float) = 
    Array2D.init (int sizeF) (int sizeF) (calculateCellContents sizeF)

[<ExcelFunction(Description="Make a simple array")>]
let mkArrayVisibleFromExcel = new System.Func<float,float>(mkArray)

If none of those work to make your function visible in Excel's XLA dropdown, then I'm out of ideas.