9 posts tagged with "Excel"

Excel-DNA version 1.6 is now available for testing as pre-release version 1.6.0-preview3 from the NuGet package repository. The extension libraries ExcelDna.Registration and ExcelDna.IntelliSense also have matching releases. ExcelDnaDoc should follow in the coming weeks.

The main focus for version 1.6 is to provide a first preview of the support for add-ins that target .NET 6. As a prerequisite to adding the .NET 6 target, we’ve also added support for new SDK-style project files and NuGet references (also great for projects that target .NET Framework). I’ve also taken the opportunity to tweak the Excel-DNA packing mechanism a bit in an attempt to avoid some of the false positive anti-virus detections we’ve seen recently. I’ll briefly discuss these (in reverse order) below, but first a word about our sponsors.

GitHub sponsors​

Excel-DNA is now registered on GitHub sponsors – see https://github.com/sponsors/Excel-DNA. Thank you very much to everyone who has already signed up – your contributions are directly funding further development.

If you use Excel-DNA and would like to encourage future support and ongoing development, please sign up as a GitHub sponsor for the project. GitHub sponsors will also have access to a private repository of sample projects where I hope to add additional tools and documentation over time.

I had previously considered the .NET Core / .NET 6 support as a good point to switch to a commercial model for Excel-DNA. However, the benefits of being an open-source project with a permissive license have been significant. So, if possible, I hope to continue with the core library as free and open source software, where further development is funded by the sponsors.

For those in a corporate setting using Excel-DNA extensively or in a mission critical role, I also offer an annual corporate support agreement. Please contact me directly if you are interested in more details.

Anti-virus false positives​

Over the last year we’ve seen a number of anti-virus and security programs identify Excel-DNA add-ins as security risks, including false positive detections that delete add-in files by the default Windows Defender service. It seems that these detections were triggered by some malicious Excel add-ins that have been built using Excel-DNA, with the anti-virus heuristics subsequently identifying all Excel-DNA add-ins as problematic. For some security vendors, including Microsoft, we’ve been able to report this false positive detection and they have updated their signatures accordingly. But in many cases the Excel-DNA version 1.5 add-ins are still being flagged.

It seems the main heuristic used to detect Excel .xll add-ins as problematic is the presence of executable assemblies as resources in the .xll library. Excel-DNA uses the packing of assemblies as resources in the .xll to simplify the add-in distribution, in many cases making possible a single file (or two files for 32-bit and 64-bit) add-in distribution.

For this version there are two changes which may help with these false positives.

The first is that packed files are now encoded, so that they are not detected as embedded executable code directly. In my testing that change removed the false positive detections at least in the Microsoft products. However, at least one other user has reported still seeing problems with this version, so it does not seem like bulletproof solution.

Another option has been contributed by user @Phundamentals (thank you!), to add a project property which disables the compression of packed files.

  <ExcelDnaPackCompressResources>false</ExcelDnaPackCompressResources>

Should these mitigations not be enough to reduce most false positives, we could also introduce an option where there are no packed files at all (currently the managed Excel-DNA assemblies are always packed).

There have also been some indications that signing the final add-in library helps reduce false positive detections.

In all cases I do encourage developers to report the false positives to their anti-virus or security vendor. Their software is mistakenly identifying and blocking legitimate add-ins from running.

Developers of malicious add-ins are welcome to contact me for sponsored licenses of an alternative add-in framework.

PackageReference and SDK-style project files​

The main ExcelDna.AddIn NuGet package now supports references in SDK-style project files. For new projects, this means that a .dna file is no longer added to the project automatically, and if not present will be created and output at build time from project properties. Existing projects with customized .dna file(s) will work as they did before.

For a new project, a simple project file might look like this:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net472</TargetFramework>

    <!-- We don't need the extra 'ref' directory and reference assemblies for the Excel add-in -->
    <ProduceReferenceAssembly>false</ProduceReferenceAssembly>

    <!-- We need all dependencies to be copied to the output directory, as-if we are an 'application' and not a 'library'.
         This property also sets the CopyLockFileAssemblies property to true. -->
    <EnableDynamicLoading>true</EnableDynamicLoading>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="ExcelDna.AddIn" Version="1.6.0-preview3" />
  </ItemGroup>

</Project>

Various additional properties can be set in the project file – see the example here https://github.com/Excel-DNA/ExcelDna/blob/master/Source/Tests/ExcelDna.AddIn.Tasks.IntegrationTests.TestTarget/SDKProperties/SDKProperties.csproj

.NET 6 support​

Excel-DNA version 1.6 (finally!) includes support for add-ins that target .NET 6. Most Excel-DNA features seem to work, including the COM-based features like RTD-based functions, ribbon and CTP extensions. However, there has been very limited testing and you should consider the .NET 6 support as an early preview.

Update the TargetFramework tag in an SDK-style project file to the .NET 6 (Windows) target:

    <TargetFramework>net6.0-windows</TargetFramework>

or build for both .NET Framework and .NET 6, which allows you to test both targets:

    <TargetFrameworks>net472;net6.0-windows</TargetFrameworks>

End users of the add-in will need to have the .NET 6 runtime installed. (To include the runtime files as part of the add-in will not be supported, but an installer program might check and install the runtime.)

A hard limitation of the .NET core series of runtimes (.NET 5 / 6 / 7 etc.) is that only one version of a .NET core runtime can be loaded into a specific process. The core runtime can still be loaded concurrently with a .NET Framework runtime (e.g. .NET Framework 4.8), although this is not a configuration officially supported by Microsoft. Excel-DNA will try to support add-ins targeting a .NET Framework running together with .NET 6 add-ins, but there is not planned to be any support for the future .NET 7 runtime. Fortunately .NET 6 is a ‘Long-term Support’ (LTS) release and will be formally supported until the end of 2024, so we can keep targeting .NET 6 for a few years. There is also some work on the horizon that make ahead-of-time compilation of .NET libraries (like the Excel-DNA add-ins) viable, bypassing the concurrent runtime issues. So I do expect a future path beyond .NET 6, though that is not an immediate concern.

I look forward to bug reports, questions and other feedback about the .NET 6 support (including whether it works at all). Support for modern .NET has been a long time coming and ensures that Excel-DNA can take part in the exciting future evolution of .NET.

After 16 years I am still amazed by and deeply appreciate the support and enthusiasm for Excel-DNA. Last but not least I want to thank Sergey Vlasov for his calm and consistent efforts that are now driving the project forward.

-Govert

Excel-DNA version 1.5​

A release candidate for the next version of Excel-DNA is now available for testing. Please try out this update, and let me know if you run into any problems or whether you are able to confirm that your add-in still works correctly. I expect to make a final release in a few weeks.

This update includes a completely new implementation of the core marshaling, replacing code that I initially wrote in 2005. I’ve also done some internal refactoring to prepare for supporting the .NET 5+ generation of runtimes. Finally, a few improvements were made to improve add-in load times when many functions are registered. Improved resilience in hostile anti-virus environments were also implemented since the last release.

Modernising the marshaling code has resulted in two notable changes of the minimum requirements for using Excel-DNA.

• Only .NET Framework versions 4.5.2 to 4.8 are supported, and
• Excel 2007 or newer is required, dropping support for the oldest versions of Excel.

UPDATE NOTE: If you are updating the NuGet package on a project that currently targets an older version of .NET (e.g. .NET Framework 4.0), you should change the target framework for the project to .NET Framework 4.5.2 or later, before updating the NuGet package. Otherwise you may get one of the following problems:

• The package install script may not run when the new package is installed. This leaves the project with a file called “_UNINSTALLED_xxxxx-AddIn.dna”. In this case, uninstall the ExcelDna.AddIn package, update the project target framework, and then reinstall. You might need to restart Visual Studio to complete the package updates.
• Error messages stating “Error CS0246 – The type or namespace name ‘ExcelDna’ could not be found (are you missing a using directive or an assembly reference?)”. This also indicates that the target framework is not supported and should be updated, after which the errors should be resolved.

Apart from the new minimum requirements, the new release is a highly compatible update and you should not expect any issues or significant behaviour changes after updating.

Thank you to @ittegrat and @augustoproiete for their contributions to this version.

The following prerelease packages are available on NuGet:

• ExcelDna.AddIn.1.5.0-rc1
• ExcelDna.Integration.1.5.0-rc1
• ExcelDna.IntelliSense.1.5.0-rc1
• ExcelDna.Registration.1.5.0-rc1
• ExcelDna.Registration.VisualBasic.1.5.0-rc1
• ExcelDna.Registration.FSharp.1.5.0-rc1

GitHub Sponsors​

Excel-DNA is now registered on the GitHub sponsors program. This lets you sponsor future Excel-DNA development, and also gives access to direct support for your Excel-DNA projects. Billing for the monthly sponsorship is through your GitHub account, making it easy to add sponsorship of open-source projects to a corporate GitHub account.

If you are using Excel-DNA and would like to see further development and ongoing support, please sign up as a sponsor. A big thank you to those users who were the first to do so: @PierreYvesR, @terryaney, @mhouldsworth @KoosBusters and one private sponsor.

I’ve set up three levels of monthly sponsorship subscriptions:

• Individual – $14 pm. For power users and developers making add-ins for personal or limited use.
• Team – $170 pm. For smaller development teams in a company, those publishing add-ins externally or using more advanced add-in features.
• Corporate – $850 pm. For larger companies with mission critical applications or multiple development teams.

For the Team and Corporate sponsorship levels I am also available for some monthly direct support sessions to help deal with problems or advise on your add-in projects.

The direct corporate support contracts are available as before, with bespoke agreements and extensive direct support. Thank you very much to the existing corporate partners who have supported Excel-DNA over the past decade and continue to ensure the project’s viability.

Roadmap​

The development work for Excel-DNA will next address two areas:

• Improved support for SDK-style project files and a PackageReference import of the main NuGet package. The newer .NET project templates and build tools are based on the SDK-style project files, so updating to support these even when targeting .NET Framework will be a big step forward. The main hurdle so far has been the install script and build tasks which depend on the older packages.config style NuGet implementation.
• Support for the .NET 5+ generation of runtimes. I’ve made some progress towards the new add-in hosting code required for loading the .NET 5+ runtime, but getting to an implementation that can be supported for the future and work together with existing add-ins still needs a lot of work. The .NET 5+ runtime versions have more limited isolation capabilities, and different versions cannot be loaded side-by-side in the process. This might require us to standardise on a specific version like the .NET 6 Long Term Support release as the single supported .NET 5+ runtime target.

Thank you for your ongoing enthusiasm for Excel-DNA and the super-power combination of .NET and Excel. I look forward to your thoughts and feedback.

-Govert

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:

• Interfacing C# and VBA with Excel-DNA (no intellisense support)
• Interfacing C# and VBA with Excel-DNA (with intellisense support)

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!

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.

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.

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.

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.

Some links:​

• Async functions in C# - has some sample functions in C#.
• Reactive Extensions for Excel (RxExcel) - the RxExcel class is a small wrapper that bridges the IExcelObservable to any implementation of IObservable<T>, allowing the Rx libraries to be used in Excel.

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.

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:

1. Get started with Excel-DNA.
2. Copy the code and xml wrapper into a text file called Resizer.dna (the xml wrapper is at the end of this post).
3. Copy the ExcelDna.xll in the Excel-DNA distribution to Resizer.xll (next to the Resizer.dna).
4. File->Open the 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>

Excel 2010 introduced support for offloading UDF computations to a compute cluster. The Excel blog talks about it http://blogs.msdn.com/b/excel/archive/2010/02/12/offloading-udf-s-to-a-windows-hpc-cluster.aspx, and there are some nice pictures on this TechNet article: http://technet.microsoft.com/en-us/library/ff877825(WS.10).aspx.

Excel-DNA now supports marking functions as cluster-safe, and I have updated the loader to allow add-ins to work under the XllContainer on the HPC nodes. There are some issues to be aware of:

• The add-in does not create its own AppDomain when running on the compute node. One consequence is that no custom .xll.config file is used; configuration entries need to be set in the XllContainer configuration setup.
• There are some limitations on the size of array data that can be passed to and from UDF calls - this limit is probably configurable in the WCF service.
• Only the 32-bit host is currently supported.

To test this you will need an Windows HPC Server 2008 R2 cluster with the HPC Services for Excel installed. On the clients you need Excel 2010 with the HPC cluster connector installed. The latest check-in for Excel-DNA with this support is on GitHub: https://github.com/Excel-DNA/ExcelDna.

In the Microsoft HPC SDK there is a sample called ClusterUDF.xll with a few test functions. I have recreated these in C# in the samples file Distribution\Samples\ClusterSample.dna Basically functions just need to be marked as IsClusterSafe=true to be pushed to the cluster for computation. For example

[ExcelFunction(IsClusterSafe=true)]
public static int DnaCountPrimesC(int nFrom, int nTo)
{
    // ...
}

As usual, any feedback on this feature - questions or reports on whether you use it - will be most appreciated.