Skip to main content

· 3 min read

Version 0.33 Release Candidate

Excel-DNA 0.33 contains a number of bug fixes and improvements, including a diagnostic logging approach based on the .NET Trace classes. More details can be found in the current ChangeLog.

This version will also be the foundation for a first release of the custom Registration extension and the ongoing work towards on-sheet IntelliSense for user-defined functions.

A release candidate for the new version is available

Please help me test that the new version works correctly in the many different ways, Excel and Windows versions, and languages where Excel-DNA add-ins run.

If you run into any unexpected behavior bugs or regressions, please post to the Google group or contact me directly.

Also, if you are able to confirm that the new version works in a particular setting, please post that too. Details about what functionality you've tested and what operating environment (including .NET and Excel version) you are running with, would help me a lot.

License Change

For the Excel-DNA project, I've changed to the standard MIT license. This has become the most common open-source license aligned with my intention of making Excel-DNA free for all use, including commercial use. UPDATE 2015-09-03: Excel-DNA is (again) licensed under the zlib license. More details here.

If you have any concerns with this change, please let me know.

NuGet Packages

With this version, I am re-aligning the Excel-DNA package names on NuGet with the assembly names and standard naming conventions. The main packages for this release will be:

  • ExcelDna.AddIn - Includes the .xll and creates a complete add-in when installed into a Class Library project. This is update of the "Excel-DNA" package.

  • ExcelDna.Integration - Contains only the integration reference library, suitable for referencing in third-party libraries that are intended to be used in Excel-DNA add-ins. An update of the "Excel-DNA.Lib" package.

The old packages will be updated to refer to the new ones as dependencies, which should allow package updates to work correctly.

GitHub

The Excel-DNA project is (slowly) moving to GitHub.


Thank you for your continued support of Excel-DNA!

· 2 min read

I've posted a final release of Excel-DNA version 0.32 to CodePlex (https://exceldna.codeplex.com/releases/view/119190) and the NuGet package repository (https://www.nuget.org/packages/Excel-DNA).

Excel-DNA 0.32 consolidates a large number of bug fixes and improvements that have accumulated over the last year. In particular, a number of edge cases that affect Excel-DNA add-ins under Excel 2013 have been addressed.

Native asynchronous functions, available under Excel 2010 and later, are now supported. Runtime registration of delegate functions and external retrieval of registration details will allow development of extension features without requiring changes to the Excel-DNA core runtime - see the ExcelDna.CustomRegistration project for examples of the dynamic registration: https://github.com/Excel-DNA/CustomRegistration

Excel-DNA 0.32 introduces one breaking change: integer parameter conversions are modified to be consistent with VBA. Fractional values passed to functions with integer parameters are converted using the round-to-even convention - as is the case for VBA functions. This issue is discussed in more detail at http://excel-dna.net/2014/05/03/excel-dna-0-32-breaking-changes-to-integer-and-boolean-parameter-handling/

See the Distribution\ChangeLog.txt file for a complete list of changes in this version.

As always, I greatly appreciate any feedback on this version, and on Excel-DNA in general. Any comments or questions are welcome on the Google group or by contacting me directly.

To ensure future development of Excel-DNA, please make a donation via PayPal or arrange for a corporate support agreement. See http://excel-dna.net/support/ for details.

· 2 min read

Excel-DNA version 0.32 introduces some changes in the parameter conversions applied to integer and boolean parameters. These changes improve compatibility with VBA, and make it easier to provide a consistent implementation when the conversion needs to be explicitly implemented, as for some generated methods.

In Excel-DNA versions before 0.32, UDF functions taking integer and boolean parameters were registered with the C API using the respective types, and hence the conversions were performed by Excel before calling the UDF. In Excel-DNA 0.32, these conversions are performed by Excel-DNA, with the changes discussed here. Affected functions would previously have behaved consistent with .xll add-ins made with C/C++, where registered with integer or boolean parameter types.

The new behaviour for integer conversions is that double values passed from Excel to integer parameters in UDFs are converted using the "Round-To-Even" midpoint rounding convention. Previously, positive midpoint values (like 2.5) were rounded up (to 3), while negative midpoint values were rounded down (-2.5 to -3), with the exception that -0.5 was rounded to 0. Int64 (long) parameters are now also handled consistently.

One exception to the VBA compatibility guideline is that incoming boolean true values passed to integer parameters are converted to 1, rather than -1 as would be the case with VBA. For this case I consider it more important to be consistent with .NET conventions, whereby boolean true values are represented by 1.

For conversions to boolean parameters, the main change is in how fractional values are converted to booleans. The new version is consistent with VBA - any non-zero value is converted to true.

I hope you will agree that the improved consistency is worth making these breaking changes, and that the decision will not cause any unexpected problems. As always, I appreciate any feedback, either directly or via the Excel-DNA Google group.


The following snapshot gives a good summary of the changes:

Param Conversion Table Changes

The functions used are as follows:

public static object dnaConvertInt32(int value)
{
return value;
}
Function VbaConvertInteger(value As Integer)
VbaConvertInteger = value
End Function
public static object dnaConvertInt64(long value)
{
return value;
}
public static object dnaConvertBoolean(bool value)
{
return value;
}
Function VbaConvertBoolean(value As Boolean)
VbaConvertBoolean = value
End Function

· 2 min read

Functions and macros created in an Excel-DNA add-in can be called directly from Excel VBA by using Application.Run(...). However, .NET also supports creating rich object models that are exported as COM libraries, which can be Tools->Referenced in VBA. Excel-DNA has some advanced support to host COM-exported objects from Excel-DNA add-ins, giving some advantages over the regular .NET "Register for COM interop" hosting approach:

  • COM objects that are created via the Excel-DNA COM server support will be active in the same AppDomain as the rest of the add-in, allowing direct shared access to static variables, internal caches etc.

  • COM registration for classes hosted by Excel-DNA does not require administrative access (even when registered via RegSvr32.exe).

  • Everything needed for the COM server can be packed in a single-file .xll add-in, including the type library used for IntelliSense support in VBA.

Mikael Katajamäki has written some detailed tutorial posts on his Excel in Finance blog that explore this Excel-DNA feature, with detailed explanation, step-by-step instructions, screen shots and further links. See:

Note that these techniques would work equally well with code written in VB.NET, allowing you to port VB/VBA libraries to VB.NET with Excel-DNA and then use these from VBA.

Thank you Mikael for the great write-up!

· 2 min read

I have posted to CodePlex (https://exceldna.codeplex.com/releases/view/119190) and the NuGet package manager (https://www.nuget.org/packages/Excel-DNA/0.32.0-rc1) a release candidate of the next Excel-DNA version.

I hope to make a final release in the next few weeks, once I've had confirmation that this version works well on the various platforms and Excel versions.

Please test, and let me know of any problems or surprises you run into, or confirm what features, platforms and Excel versions work correctly.

The CodePlex download is structured as before, and for the NuGet package manager, you can upgrade to the pre-release version with:

PM> Upgrade-Package Excel-DNA -Pre

Excel-DNA 0.32 consolidates a large number of bug fixes and improvements that have accumulated over the last year. In particular, a number of edge cases that affect Excel-DNA add-ins under Excel 2013 have been addressed.

Native asynchronous functions, available under Excel 2010 and later, are now supported. Runtime registration of delegate functions and external retrieval of registration details will allow development of extension features without requiring changes to the Excel-DNA core runtime.

Excel-DNA 0.32 is compatible with version 0.30, and introduces no notable breaking changes. See the Distribution\ChangeLog.txt file for a complete change list.

As always, I greatly appreciate any feedback on this version, and on Excel-DNA in general. Any comments or questions are welcome on the Google group or by contacting me directly.

To ensure future development of Excel-DNA, please make a donation via PayPal or arrange for a corporate support agreement. See http://excel-dna.net/support/ for details.

Thank you for your continued support, Govert

· One min read

Bram Jochems has written a friendly "Getting Started" post, discussing how to use F# with Excel-DNA in a finance context. His add-in with various F# / Excel-DNA helper utilities, and a bunch of quantitative finance-related UDFs, including option pricing function and volatility interpolation, has been published as a project on GitHub.

It's well worth a look, whether you are using F# and keen to explore Excel-DNA, or just curious about F# and looking for some practical examples.

· One min read

Gert-Jan van der Kamp has posted a very nice end-to-end example on CodeProject, showing how to create a WCF service and Excel-DNA add-in to stream real-time data into Excel.

The example uses to use the Reactive Extensions support in Excel-DNA v. 0.30 to push the data to an Excel UDF (using Excel's RTD mechanism behind the scenes), together with a Duplex WCF service providing the data.

There was also this CodePlex discussion about the Excel ThrottleInterval option, which trades off the real-time update frequency against stability of the Excel calculation.

· 2 min read

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>

· 10 min read

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.

IObservable 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.

F# helpers for async and IObservable-based events

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)

Sample functions

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
  • Async implementation 1: Use Excel-DNA async directly to run downloadString on a ThreadPool thread
let 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
}
  • Async implementation 2: 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))
  • Async implementation 3: Use the helper we defined above. 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, 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)
  • Event implementation: Finally this is the Excel function that will tick away in a cell. Entered into a cell (and formatted as a Time value), the formula =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)

Putting everything together in an Excel add-in

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:

  • Create a new F# library in Visual Studio.
  • Install the Excel-DNA package from NuGet (Install-Package Excel-DNA from the NuGet console).
  • Set up the Debug path:
    1. Select “Start External Program” and browse to find Excel.exe, e.g. for Excel 2010 the path might be: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE.
    2. Enter the full path to the .xll file in the output as the Command line arguments, e.g. C:\MyProjects\TestDnaFs\bin\Debug\TestDnaFs-addin.xll.
  • Place the following code in 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)

Support and feedback

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.