Excel-DNA 0.32 – Breaking changes to integer and boolean parameter handling

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 importatnt 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:

Image

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
Advertisements
Posted in Uncategorized | Tagged ,

Tutorial: COM server support for VBA integration

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 works 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!

Posted in Uncategorized | Tagged , , , , , , ,

Excel-DNA 0.32 Release Candidate

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 https://excel-dna.net/support/ for details.

Thank you for your continued support,
Govert

Posted in Uncategorized

Getting started with F# and Excel-DNA in finance

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

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.

Posted in Uncategorized | Tagged ,

Streaming real-time data to Excel

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.

Posted in Uncategorized | Tagged , , ,

F# and R in Excel

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.

Posted in Uncategorized | Tagged ,

Caching and Asynchronous Excel UDFs

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>
Posted in Samples | Tagged , , , ,