Natallie Baikevich has posted some very pretty examples of using F# with Excel-DNA. One function uses the R type provider to easily expose a feature from an R package as an UDF in Excel. Have a look at the write-up, and the code on GitHub.
This sample shows how the result of an Excel-DNA async UDF call can be cached using the .NET 4 MemoryCache class.
PS: Apparently there is a bug in the memory management of the .NET MemoryCache class. See the StackOverflow discussion and the Connect bug report. The SharpMemoryCache NuGet package might be an alternative, though I've not tried it.
<DnaLibrary Name="CachedAsyncSample" RuntimeVersion="v4.0" Language="C#">
<Reference Name="System.Runtime.Caching" />
<![CDATA[
using System;
using System.Threading;
using System.Runtime.Caching;
using ExcelDna.Integration;
public static class dnaFunctions
{
public static object dnaCachedAsync(string input)
{
// First check the cache, and return immediately
// if we found something.
// (We also need a unique key to identify the cache item)
string key = "dnaCachedAsync:" + input;
ObjectCache cache = MemoryCache.Default;
string cachedItem = cache[key] as string;
if (cachedItem != null)
return cachedItem;
// Not in the cache - make the async call
// to retrieve the item. (The second parameter here should identify
// the function call, so would usually be an array of the input parameters,
// but here we have the identifying key already.)
object asyncResult = ExcelAsyncUtil.Run("dnaCachedAsync", key, () =>
{
// Here we fetch the data from far away....
// This code will run on a ThreadPool thread.
// To simulate a slow calculation or web service call,
// Just sleep for a few seconds...
Thread.Sleep(5000);
// Then return the result
return "The calculation with input "
+ input + " completed at "
+ DateTime.Now.ToString("HH:mm:ss");
});
// Check the asyncResult to see if we're still busy
if (asyncResult.Equals(ExcelError.ExcelErrorNA))
return "!!! Fetching data";
// OK, we actually got the result this time.
// Add to the cache and return
// (keeping the cached entry valid for 1 minute)
// Note that the function won't recalc automatically after
// the cache expires. For this we need to go the
// RxExcel route with an IObservable.
cache.Add(key, asyncResult, DateTime.Now.AddMinutes(1), null);
return asyncResult;
}
public static string dnaTest()
{
return "Hello from CachedAsyncSample";
}
}
]]>
</DnaLibrary>
There have been a some recent posts mentioning the asynchronous and reactive programming features in F#. Since Excel-DNA 0.30 added support for creating async and IObservable-based real-time data functions, I'd like to show how these F# features can be nicely exposed to Excel via Excel-DNA.
Excel-DNA 0.30 allows an add-in to expose IObservable sources to Excel as real-time data functions. (Actually Excel-DNA defines an interface called IExcelObservable that matches the semantics of `IObservable<\object> - this is because we still target .NET 2.0 with the core library.)
Asynchronous function can then be implemented as an IObservable that returns a single value before completing. Cancellation (triggered when the user removes a real-time or async formula) is supported via the standard IDisposable mechanism.
Internally, Excel-DNA implements a thread-safe RTD server and registers the IObservable as an RTD topic. So some aspects of the IObservable support are subject to Excel's RTD feature works, for example the RTD throttle interval (by default 2 seconds) will also apply to IObservable functions.
The following functions in the ExcelDna.Integration.ExcelAsyncUtil helper class are relevant:
ExcelAsyncUtil.Initialize() - this should be called in a macro context before any of the other features are used, typically from the AutoOpen() handler.
ExcelAsyncUtil.Observe(...) - registers an IExcelObservable as a real-time data function with Excel. Subsequent OnNext() calls will raise updates via RTD.
ExcelAsyncUtil.Run(...) - a helper method that runs a function asynchronously on a .NET threadpool thread.
In addition, we'll use
ExcelObservableSource - a delegate type for functions that return an IExcelObservable.IExcelObservable to any implementation of IObservable<T>, allowing the Rx libraries to be used in Excel.To initialize the async support in Excel-DNA, we need some code like the following:
namespace FsAsync
open System
open System.Threading
open System.Net
open Microsoft.FSharp.Control.WebExtensions
open ExcelDna.Integration
/// This class implements the IExcelAddin which allows us to initialize the ExcelAsyncUtil support.
/// It must not be a nested class (e.g. defined as a type inside a module) but a top-level class (inside a namespace)
type FsAsyncAddIn () =
interface IExcelAddIn with
member this.AutoOpen () =
ExcelAsyncUtil.Initialize ()
member this.AutoClose () = ExcelAsyncUtil.Uninitialize ()
// define a regular Excel UDF just to show that the add-in works
[<ExcelFunction(Description="A test function from F#")>]
static member fsaAddThem (x:double) (y:double) = x + y
F# supports an asynchronous programming model via "async computation expressions". The result of an async computation expression is a value of type Async<T>, which we need to convert to an IExcelObservable. We use a standard CancellationTokenSource hooked up to the IDisposable to enable cancellation.
module FsAsyncUtil =
/// A helper to pass an F# Async computation to Excel-DNA
let excelRunAsync functionName parameters async =
let obsSource =
ExcelObservableSource(
fun () ->
{ new IExcelObservable with
member __.Subscribe observer =
// make something like CancellationDisposable
let cts = new CancellationTokenSource ()
let disp = { new IDisposable with member __.Dispose () = cts.Cancel () }
// Start the async computation on this thread
Async.StartWithContinuations
( async,
( fun result ->
observer.OnNext(result)
observer.OnCompleted () ),
( fun ex -> observer.OnError ex ),
( fun ex ->
observer.OnCompleted () ),
cts.Token
)
// return the disposable
disp
})
ExcelAsyncUtil.Observe (functionName, parameters, obsSource)
Another neat feature of F# is that events are first-class types that implement IObservable. This means any F# event can serve as a real-time data source in an Excel formula. To bridge the F# events to the IExcelObservable interface is really easy, we just have the following helper:
/// A helper to pass an F# IObservable to Excel-DNA
let excelObserve functionName parameters observable =
let obsSource =
ExcelObservableSource(
fun () ->
{ new IExcelObservable with
member __.Subscribe observer =
// Subscribe to the F# observable
Observable.subscribe (fun value -> observer.OnNext (value)) observable
})
ExcelAsyncUtil.Observe (functionName, parameters, obsSource)
Given the above helpers, we can now explore a few ways to implement async and real-time streaming functions. As examples:
Here is a plain synchronous function to download a url into a string:
let downloadString url =
try
let uri = new System.Uri(url)
let webClient = new WebClient()
let html = webClient.DownloadString(uri)
html
with
| ex -> "!!!ERROR: " + ex.Message
downloadString on a ThreadPool threadlet downloadStringAsyncRunTP1 url =
ExcelAsyncUtil.Run ("downloadStringAsyncTP1", url, (fun () -> downloadString url :> obj))
Create an F# asynchronous computation for the download (this functions is not exported to Excel)
let downloadStringAsyncImpl url = async {
try
// In here we could check for cancellation using
// let! ct = Async.CancellationToken
// if ct.IsCancellationRequested then ...
let uri = new System.Uri(url)
let webClient = new WebClient()
let! html = webClient.AsyncDownloadString(uri)
return html
with
| ex -> return "!!!ERROR: " + ex.Message
}
ThreadPool thread because that's what ExcelAsyncUtil.Run does. Blocking calls will block a ThreadPool thread, eventually limiting the concurrency of the async callslet downloadStringAsyncTP2 url =
ExcelAsyncUtil.Run ("downloadStringAsyncTP2", url, (fun () -> Async.RunSynchronously (downloadStringAsyncImpl url) :> obj))
ThreadPool threads, and allows cancellationlet downloadStringAsync url =
FsAsyncUtil.excelRunAsync "downloadStringAsync" url (downloadStringAsyncImpl url)
Helper that will create a timer that ticks at timerInterval for timerDuration, and is then done. Also not exported to Excel (incompatible signature). Notice that from F#, the timer.Elapsed event of the BCL Timer class implements IObservable, so can be used directly with the transformations in the F# Observable module.
let createTimer timerInterval timerDuration =
// setup a timer
let timer = new System.Timers.Timer(float timerInterval)
timer.AutoReset <- true
// return an async task for stopping it after the duration
let timerStop = async {
timer.Start()
do! Async.Sleep timerDuration
timer.Stop()
}
Async.Start timerStop
// Make sure that the type we actually observe in the event is supported by Excel
// by converting the events to timestamps
timer.Elapsed |> Observable.map (fun elapsed -> DateTime.Now)
=startTimer(5000, 60000) will show a clock that ticks every 5 seconds for a minute.let startTimer timerInterval timerDuration =
FsAsyncUtil.excelObserve "startTimer" [|float timerInterval; float timerDuration|] (createTimer timerInterval timerDuration)
A complete .dna script file with the above code can be found in the Excel-DNA distribution, under Distribution\Samples\Async\FsAsync.dna.
Alternatively, the following steps would build an add-in in Visual Studio:
Install-Package Excel-DNA from the NuGet console).C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE..xll file in the output as the Command line arguments, e.g. C:\MyProjects\TestDnaFs\bin\Debug\TestDnaFs-addin.xll.Library1.fs, compile and run:namespace FsAsync
open System
open System.Threading
open System.Net
open Microsoft.FSharp.Control.WebExtensions
open ExcelDna.Integration
/// This class implements the IExcelAddin which allows us to initialize the ExcelAsyncUtil support.
/// It must not be a nested class (e.g. defined as a type inside a module) but a top-level class (inside a namespace)
type FsAsyncAddIn () =
interface IExcelAddIn with
member this.AutoOpen () =
ExcelAsyncUtil.Initialize ()
member this.AutoClose () = ExcelAsyncUtil.Uninitialize ()
// a regular Excel UDF just to show that the add-in works
static member fsaAddThem (x:double) (y:double) = x + y
/// Some utility functions for connecting Excel-DNA async with F#
module FsAsyncUtil =
/// A helper to pass an F# Async computation to Excel-DNA
let excelRunAsync functionName parameters async =
let obsSource =
ExcelObservableSource(
fun () ->
{ new IExcelObservable with
member __.Subscribe observer =
// make something like CancellationDisposable
let cts = new CancellationTokenSource ()
let disp = { new IDisposable with member __.Dispose () = cts.Cancel () }
// Start the async computation on this thread
Async.StartWithContinuations
( async,
( fun result ->
observer.OnNext(result)
observer.OnCompleted () ),
( fun ex -> observer.OnError ex ),
( fun ex ->
observer.OnCompleted () ),
cts.Token
)
// return the disposable
disp
})
ExcelAsyncUtil.Observe (functionName, parameters, obsSource)
/// A helper to pass an F# IObservable to Excel-DNA
let excelObserve functionName parameters observable =
let obsSource =
ExcelObservableSource(
fun () ->
{ new IExcelObservable with
member __.Subscribe observer =
// Subscribe to the F# observable
Observable.subscribe (fun value -> observer.OnNext (value)) observable
})
ExcelAsyncUtil.Observe (functionName, parameters, obsSource)
// Some test functions
module TestFunctions =
/// Plain synchronous download function
/// can be called from Excel
let downloadString url =
try
let uri = new System.Uri(url)
let webClient = new WebClient()
let html = webClient.DownloadString(uri)
html
with
| ex -> "!!!ERROR: " + ex.Message
/// Uses Excel-DNA async to run download on a ThreadPool thread
let downloadStringAsyncTP1 url =
ExcelAsyncUtil.Run ("downloadStringAsyncTP1", url, (fun () -> downloadString url :> obj))
/// Create an F# asynchronous computation for the download
/// Not exported to Excel
let downloadStringAsyncImpl url = async {
try
// In here we could check for cancellation using
// let! ct = Async.CancellationToken
// if ct.IsCancellationRequested then ...
let uri = new System.Uri(url)
let webClient = new WebClient()
let! html = webClient.AsyncDownloadString(uri)
return html
with
| ex -> return "!!!ERROR: " + ex.Message
}
/// This function runs the async computation synchronously on a ThreadPool thread
/// because that's what ExcelAsyncUtil.Run does
/// Blocking calls will block a ThreadPool thread, eventually limiting the concurrency of the async calls
let downloadStringAsyncTP2 url =
ExcelAsyncUtil.Run ("downloadStringAsyncTP2", url, (fun () -> Async.RunSynchronously (downloadStringAsyncImpl url) :> obj))
/// This runs the async computation using true F# async
/// Should not block ThreadPool threads, and allows cancellation
let downloadStringAsync url =
FsAsyncUtil.excelRunAsync "downloadStringAsync" url (downloadStringAsyncImpl url)
// Helper that will create a timer that ticks at timerInterval for timerDuration, then stops
// Not exported to Excel (incompatible type)
let createTimer timerInterval timerDuration =
// setup a timer
let timer = new System.Timers.Timer(float timerInterval)
timer.AutoReset Observable.map (fun elapsed -> DateTime.Now)
// Excel function to start the timer - using the fact that F# events implement IObservable
let startTimer timerInterval timerDuration =
FsAsyncUtil.excelObserve "startTimer" [|float timerInterval; float timerDuration|] (createTimer timerInterval timerDuration)
The best place to ask any questions related to Excel-DNA is the Excel-DNA Google group. Any feedback from F# users trying out Excel-DNA or the features discussed here will be very welcome. I can also be contacted directly at govert@dnakode.com.
Excel-DNA is a great library to help ease the path from Excel VBA to VB.NET. Last year another part of the puzzle fell in place: I discovered NetOffice, a version-independent set of Office interop assemblies put together by Sebastian Lange. By referencing the NetOffice assemblies instead of the official Primary Interop Assemblies (PIA) for Office, an Excel-DNA add-in can target various Excel versions with a single add-in, and also ease distribution of the required interop assemblies, even packing them into the .xll add-in itself.
To explore how Excel-DNA and NetOffice can combine to convert a VBA add-in to VB.NET, I picked a small add-in made by Robert del Vicario that does a risk analysis simulation inspired by the Pallisade @RISK add-in. I took Robert's original RiskGen VBA add-in, and created a new Excel-DNA add-in in VB.NET (I used Visual Studio, but the free SharpDevelop IDE should work fine too). I documented the steps along the way of creating the VB.NET project, making an add-in based on Excel-DNA and using NetOffice to help port the VBA code to VB.NET. The resulting document (RiskGen Port Log.docx) outlining exactly how I ported the add-in, with the new VB.NET-based RiskGen.NET is also on Robert's site.
I'm also looking for some more examples of free/open source VBA add-ins to port to Excel-DNA. The best add-ins will contain a mix of user-defined functions and macros which use the Excel object model. Please post to the Google group or mail me directly if you have any suggestions.
And as always, if you need any support porting your Excel VBA add-ins to .NET using Excel-DNA, I'm happy to help on the Excel-DNA Google group.
Update (21 June 2017): The most up-to-date version of the ArrayResizer utility can be found here: https://github.com/Excel-DNA/ExcelDna/blob/master/Distribution/Samples/ArrayResizer.dna
Update: To work correctly under Excel 2000/2002/2003, this sample requires at least version 0.29.0.12 of Excel-DNA.
A common question on the Excel-DNA group is how to automatically resize the results of an array formula. The most well-know appearance of this trick is in the Bloomberg add-in.
WARNING! This is a bad idea. Excel does not allow you to modify the sheet from within a user-defined function. Doing this breaks Excel's calculation model.
Anyway, here is my attempt at an Excel-DNA add-in that implements this trick. My approach is to run a macro on a separate thread that will check and if required will expand the formula to an array formula of the right size. This way nothing ugly gets done if the array size is already correct - future recalculations will not run the formula array resizer if the size is still correct.
The code below will register a function call Resize. You can either call Resize from within your function, or enter something like =Resize(MyFunction(…)) as the cell formula. The code also registers two sample functions, MakeArray and MakeArrayAndResize to play with, each take the number of rows and columns for the size of the returned array.
To test this:
Resizer.dna (the xml wrapper is at the end of this post).ExcelDna.xll in the Excel-DNA distribution to Resizer.xll (next to the Resizer.dna).Resizer.xll in Excel and enter something like =MakeArrayAndResize(5,3) into a cell.
See how it grows.In the current version, the formula expansion is destructive, so anything in the way will be erased. One case I don't know how to deal with is when there is an array that would be partially overwritten by the expended function result. In the current version Excel will display an error that says "You cannot change part of an array.", and I replace the formula with a text version of it. I'd love to know how you think we should handle this case.
Any questions or comments (can if anyone can get it to work, or not?) can be directed to the [Excel-DNA Google group][excel-dna-group] or to me directly via e-mail. I'm pretty sure there are a few cases where it will break - please let me know if you run into any problems.
I'll try to gather the comments and suggestions for an improved implementation that might go into the next version of Excel-DNA.
Also, if you have any questions about how the implementation works, I'd be happy to write a follow up post that explains a bit more of what I'm doing. But first, let's try to get it working.
Here's the Resizer add-in code:
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Threading;
using ExcelDna.Integration;
public static class ResizeTest
{
public static object MakeArray(int rows, int columns)
{
object[,] result = new string[rows, columns];
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
result[i,j] = string.Format("({0},{1})", i, j);
}
}
return result;
}
public static object MakeArrayAndResize(int rows, int columns)
{
object result = MakeArray(rows, columns);
// Call Resize via Excel - so if the Resize add-in is not part of this code, it should still work.
return XlCall.Excel(XlCall.xlUDF, "Resize", result);
}
}
public class Resizer
{
static Queue<ExcelReference> ResizeJobs = new Queue<ExcelReference>();
// This function will run in the UDF context.
// Needs extra protection to allow multithreaded use.
public static object Resize(object[,] array)
{
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
if (caller == null)
return array;
int rows = array.GetLength(0);
int columns = array.GetLength(1);
if ((caller.RowLast - caller.RowFirst + 1 != rows) ||
(caller.ColumnLast - caller.ColumnFirst + 1 != columns))
{
// Size problem: enqueue job, call async update and return #N/A
// TODO: Add guard for ever-changing result?
EnqueueResize(caller, rows, columns);
AsyncRunMacro("DoResizing");
return ExcelError.ExcelErrorNA;
}
// Size is already OK - just return result
return array;
}
static void EnqueueResize(ExcelReference caller, int rows, int columns)
{
ExcelReference target = new ExcelReference(caller.RowFirst, caller.RowFirst + rows - 1, caller.ColumnFirst, caller.ColumnFirst + columns - 1, caller.SheetId);
ResizeJobs.Enqueue(target);
}
public static void DoResizing()
{
while (ResizeJobs.Count > 0)
{
DoResize(ResizeJobs.Dequeue());
}
}
static void DoResize(ExcelReference target)
{
try
{
// Get the current state for reset later
XlCall.Excel(XlCall.xlcEcho, false);
// Get the formula in the first cell of the target
string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, target);
ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);
bool isFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, target);
if (isFormulaArray)
{
object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection);
object oldActiveCell = XlCall.Excel(XlCall.xlfActiveCell);
// Remember old selection and select the first cell of the target
string firstCellSheet = (string)XlCall.Excel(XlCall.xlSheetNm, firstCell);
XlCall.Excel(XlCall.xlcWorkbookSelect, new object[] {firstCellSheet});
object oldSelectionOnArraySheet = XlCall.Excel(XlCall.xlfSelection);
XlCall.Excel(XlCall.xlcFormulaGoto, firstCell);
// Extend the selection to the whole array and clear
XlCall.Excel(XlCall.xlcSelectSpecial, 6);
ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
oldArray.SetValue(ExcelEmpty.Value);
XlCall.Excel(XlCall.xlcSelect, oldSelectionOnArraySheet);
XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet);
}
// Get the formula and convert to R1C1 mode
bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
string formulaR1C1 = formula;
if (!isR1C1Mode)
{
// Set the formula into the whole target
formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
}
// Must be R1C1-style references
object ignoredResult;
XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target);
if (retval != XlCall.XlReturn.XlReturnSuccess)
{
// TODO: Consider what to do now!?
// Might have failed due to array in the way.
firstCell.SetValue("'" + formula);
}
}
finally
{
XlCall.Excel(XlCall.xlcEcho, true);
}
}
// Most of this from the newsgroup: http://groups.google.com/group/exceldna/browse_thread/thread/a72c9b9f49523fc9/4577cd6840c7f195
private static readonly TimeSpan BackoffTime = TimeSpan.FromSeconds(1);
static void AsyncRunMacro(string macroName)
{
// Do this on a new thread....
Thread newThread = new Thread( delegate ()
{
while(true)
{
try
{
RunMacro(macroName);
break;
}
catch(COMException cex)
{
if(IsRetry(cex))
{
Thread.Sleep(BackoffTime);
continue;
}
// TODO: Handle unexpected error
return;
}
catch(Exception ex)
{
// TODO: Handle unexpected error
return;
}
}
});
newThread.Start();
}
static void RunMacro(string macroName)
{
object xlApp;
try
{
object xlApp = ExcelDnaUtil.Application;
xlApp.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, xlApp, new object[] {macroName});
}
catch (TargetInvocationException tie)
{
throw tie.InnerException;
}
finally
{
Marshal.ReleaseComObject(xlApp);
}
}
const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
const uint VBA_E_IGNORE = 0x800AC472;
static bool IsRetry(COMException e)
{
uint errorCode = (uint)e.ErrorCode;
switch(errorCode)
{
case RPC_E_SERVERCALL_RETRYLATER:
case VBA_E_IGNORE:
return true;
default:
return false;
}
}
}
You can easily make a test add-in for this by wrapping the code into a .dna file with this around:
<DnaLibrary Language="CS">
<![CDATA[
<!--// Paste all of the above code here //-->
]]>
</DnaLibrary>